Splitting a single string in one column

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.


What I want to result to look like

SplitTest - Copy.xlsx (9.5 KB)

Please use Sheet2 for the final result

@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

1 Like

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

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.


1 Like

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

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

1 Like

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

Sequence_1.xaml (9.3 KB)
Hi Pranav,

Please try this workflow

1 Like

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…

1 Like

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

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…

1 Like

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

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


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

1 Like

Thank you!

Thank you for this!

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…

1 Like


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


1 Like


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

@Pranav_KomandurPK - your issue hasn’t solved yet?

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