Split First name Last name in excel

Hi Team,

I have an excel cell with LastName, FirstName.
I need to get it in FirstName LastName as shown below.

Using keystrokes, i have achieved this, Is there any other possible way to achieve it.
PS:I have around 10k rows.


maybe a LINQ helps you

  • assign: dtFormated = YourDataTableVar.Clone (I assume 1 Column Names)
  • second assign
    left Side: dtFormated
    right side

(From d In YourDataTableVar.AsEnumerable
Let nn = String.Join(" “, d(“Names”).toString.Trim.Split(”,"c).Reverse)
Select dtFormated.Rows.Add({nn})).CopyToDataTable

Sure we can do some finetunings if it is needed. Have a critical chec on middle names e.g
Paul Van Dyck will result to Dyck Van Paul but the Family Name is Van Dyck


Thank you, any sample bot please?

In my scenario, we dont have any middle names.

share a excel with some sample names with me and i can work out a demo bot

Please find attached sample file.

Thank you.Sample File.xlsx (347.8 KB)

find demo xaml here:
Vishwanth_Raya.xaml (7.9 KB)


Thank you, Could you please explain me what it is doing.
(From d In dtData.AsEnumerable
Let nn = String.Join(" “, d(“Names”).toString.Trim.Split(”,"c).Reverse)
Let ra = {d(0),d(1),nn}
Select dtResult.Rows.Add(ra)).CopyToDataTable
how to add the formated result in the next column, instead of replacing? it


  • iterate over each row and can reference the row of iteration with d
  • split the string on comma, receive a string array and reverse its order - reference it with nn
  • construnt a row array of type Object and init it with col1, col2, nn - reference it with ra
  • add a row to the target Datatable with using ra for the row content
  • copy all added rows to the target datatable
1 Like

Thank you somuch.

how to add the formated result in the next column, instead of replacing it?

I Got it. Thank you.

find updated demo here:
Vishwanth_Raya_v2.xaml (8.6 KB)

i’m getting space in first for the filtered name.

give a try on trim and replace the Let nn line with below
Let nn = String.Join(" “, d(“Names”).toString.Trim.Split(”,"c).Reverse.Select(Function (x) x.Trim).ToArray)

1 Like

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