Splitting a single string in one column

[Pranav_KomandurPK (Pranav Komandur)]

Hi Experts

As you can see in the screenshot and the worksheet I attached, I have a column with one string with 2 numbers on each row. I want to split to where the second number will move into a new column right next to the column it is on.

Original

What I want to result to look like

SplitTest - Copy.xlsx (9.5 KB)

Please use Sheet2 for the final result

[prasath17 (Guru)]

@Pranav_KomandurPK - instead we can fix the regex and get the first 6 digits in the column A and the rest in column B…

For ex (\d{6})\s([\d,.-]+) - group1 to columnA and group2 to ColumnB

If you are you text We can help build your regex

[Pranav_KomandurPK (Pranav Komandur)]

Thank you for this response prasath17. What activities and in what order would I do this.

[vinithareddyk123 (Vinitha)]

Hi @Pranav_KomandurPK

You can try this way aswell

yourVar=250101 22,579

Col1_value = System.Text.RegularExpressions.Regex.Split(YourVar,“\s+”)(0)
Col2_value= System.Text.RegularExpressions.Regex.Split(YourVar,“\s+”)(1)

you can use add data row to add these values to a datatable and then write range activity to write datatable to excel.

Thanks

[Pranav_KomandurPK (Pranav Komandur)]

Hi, these values will be changing every month. How would I do this then?

[prasath17 (Guru)]

@Pranav_KomandurPK - look like you should have already written the regex result to columnA right? I want the Regex’s input string(not the regex patrern)so that I can give you result as mentioned above.

[Pranav_KomandurPK (Pranav Komandur)]

I have 2 regex statements because some of these numbers every month would come in positive or negative

[Prem_Ponnai]

Sequence_1.xaml (9.3 KB)
Hi Pranav,

Please try this workflow

[prasath17 (Guru)]

@Pranav_KomandurPK

I have 2 regex statements because some of these numbers every month would come in positive or negative

I guess, you don’t need 2 Regex statements…if you share some sample strings…I can take a look and try to build one Regex…can you please share some samples and also give your expected output…

[Pranav_KomandurPK (Pranav Komandur)]

I have attached above in my post the sample numbers and the expected output

[prasath17 (Guru)]

Got it…here you go…Is this your expected output…?If yes, all you have to do it…write the Group1 to ColumnA and Group2 to column B…

Once you confirmed, I can give one line LINQ code to write it in a datatable or if you would like to go with for Each also we can do…

[Pranav_KomandurPK (Pranav Komandur)]

Yes, that looks great! Please send that over thank you

[prasath17 (Guru)]

@Pranav_KomandurPK - Here you go…Please try to mimic the workflow given below(just for your learning purpose, don’t mistake)

LINQ Code

   (From m In IEnRegex.Cast(Of Match)
   Select Dt.Rows.Add(m.Groups(1).toString,m.Groups(2).toString)).CopyToDataTable

My Output

Hope this helps…

[Pranav_KomandurPK (Pranav Komandur)]

Thank you!

[Pranav_KomandurPK (Pranav Komandur)]

Thank you for this!

[prasath17 (Guru)]

@Pranav_KomandurPK

Thank you!

You Welcome…Once you done with your testing and if everything works…please mark my post as solution, so that other will be benefited…Thanks…

[vinithareddyk123 (Vinitha)]

Hi

There are many ways you can do this
You can also try this
Sequence3.xaml (10.9 KB)

Regards,

[Pranav_KomandurPK (Pranav Komandur)]

It does not include that first account number 250101, can you check your code again?

[prasath17 (Guru)]

@Pranav_KomandurPK - your issue hasn’t solved yet?