Formatting Excel Column

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

Hi,

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?

Thanks!
Excel_Preprocessed.xlsx (17.9 KB)

[prasath17 (Guru)]

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

Output

Hope this helps…

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

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

[prasath17 (Guru)]

@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,"\/","_"))

[Yudhisteer_Chintaram1 (Yudhisteer Chintaram)]

Okay thanks. Let me try it.

[balupad14 (Balamurugan)]

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.
or
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.

UiPath Go ! BalaReva EasyExcel Activities

Hi, A situation to find and replace the text inside the Excel. For that purpose, I have developed some activities below. Find - Find the text inside the sheet. It will return the cell info as an array string and row indexes. FindReplace - Find and replace the text inside the Excel sheet. [image] [image] Regards Balamurugan.S

Check this video.

Uipath Excel Find | Search | Find and Replace (BalaReva)

Thank you
Balamurugan.S

[prasath17 (Guru)]

@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…

Parameters

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