Formatting Excel Column


I have this excel column where data is in the form “846325/00259269”.

There are two process which I need to do:

  1. I want to remove the “/” in between and replace it with a “_” as such: “846325_00259269”. So creating a new column with the modification.

  2. Extract only the first values before the “/” as such: “846325”.
    Again a new column should be created.

The Excel does not have the column arlready. The bot should create the columns and paste the modifications in it.

Can anyone help please?

Excel_Preprocessed.xlsx (17.9 KB)

Hi @…You can try as shown below…

Add Data Column

Invoke Code

dt.AsEnumerable().ToList().ForEach(Sub(row) row("Split")= system.text.RegularExpressions.Regex.match(row("Modified Yarn TC").ToString,"\d+(?=\/)"))

Edit Arguments


Hope this helps…

1 Like

Do you have one where it replaces the / with _ ?

@Yudhisteer_Chintaram1 - Add Datacolumn activity …call the column as Say Replace

Use the invoke code again

dt.AsEnumerable().ToList().ForEach(Sub(row) row("Replace")= system.text.RegularExpressions.Regex.replace(row("Modified Yarn TC").ToString,"\/","_"))
1 Like

Okay thanks. Let me try it.

1 Like

Hi @Yudhisteer_Chintaram1 ,

Case 1 : you can use find and replace using “Find and Replace activity”
Case 2 : Using find activity you can get the cell info. after that you can read the exact cell and extract it.
Read the excel into the datatable and use the linq to find only the text is having the “/”. after that do for loop . Extract it.

Check this video.

Thank you

@Yudhisteer_Chintaram1 - Sure…

If you want to move the newly created ‘Split’ Column right after “Modified Yarn TC” column then please use the below invoke method…


Note: Modified Yarn TC column position is 28(Index starts at 0, So I gave 29)

1 Like

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