Split a cell value of an excel by a digit and concatenate it with some value

Help Studio
, ,
1

Hi All,

I want to split cell ID value of an excel by 4, concatenate it with digit ‘0’. Store it in col 3 in LINQ.

Thanks in advance.

image

2

Hi @Smreti_Gupta

Where you are spliting your excel value ??

3

Hi @Robinnavinraj_S I am splitting column ID value by 4 places. The value could be larger than 4 places

Below is the excel formula that I use -
=CONCATENATE(“0”,RIGHT(A2,9))

4

Hi @Smreti_Gupta

Your steps would be as follows below

Read Range the Input Excel

For Each row in ReadDt

Currentrow(“Column3”) =“0”+CurrentRow(“ID”).toString

Out Side the Body of the Loop

Write Range the Excel with same ReadRangePath and Dt

Regards

1 Like
5

Can you let me know if there is any linq query for this as it works faster.

Also, column(“ID”) could have more than 4 digits. So i need to take only 4 from the left.

6

@Smreti_Gupta
have a look on following blog
How to Update Data Column Values of a Data Table | Community Blog

in this blog the different options are discussed. Your case has more a match to get it done with a for each or datacolumn expression instead of a LINQ implementing an ItemArray reconstruction or mirroring a for each within an invoke code activitiy

1 Like
7

Hi @Smreti_Gupta

Check with the linq expression below!

Use Read Range

Assign NewDt = ReadDt.clone

(From d In readDt.AsEnumerable**
Let u =If(d(0).toString.length>4, "0"+d(0).tostring.Substring(0,4), "0"+d(0).ToString)
Select NewDt.Rows.Add(New Object(){d(0),d(1),u})).CopyToDataTable

Finally write range the NewDt

Regards

1 Like
8

Hi @Smreti_Gupta

Check with the reference xaml below for implementation!

test.xaml (9.4 KB)

Regards

9

Hi @Smreti_Gupta,

To get the required value you can use Col3=“0”+CurrentRow(“ID”).ToString(0)+CurrentRow(“ID”).ToString(1)+CurrentRow(“ID”).ToString(2)+CurrentRow(“ID”).ToString(3)

Then use the “write cell” to put the Col3 value back in excel.

This is not the best approach but will work.

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.