Split First name Last name in excel

Help Studio
,
1

Hi Team,

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

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

2

@Vishwanth_Raya

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

3 Likes
3

Thank you, any sample bot please?

In my scenario, we dont have any middle names.

4

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

6

Please find attached sample file.

Thank you.Sample File.xlsx (347.8 KB)

7

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

2 Likes
8

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

9

@Vishwanth_Raya

  • 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
10

Thank you somuch.

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

11

I Got it. Thank you.

12

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

13

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

14

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
15

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