split name surname in separate column

[Anonymous]

Hi, I went to the forum, but I have not found anything that suits my case.

I have a colum like this ( name and surname)

MARCO DI BENEDETTO

MICHELE DE ROBERTO

I'd like to split into 2 separate colum

name	surname
MARCO	DI BENEDETTO
MICHELE	DE ROBERTO

If I split column by delimiters ( space) I obtain something wrong due to surname with 2 parts ( DI BENEDETTO)....

any idea is appreciated !

Thanks

Diego

[mangaus1111]

Otherwise you can use the macro Text before and after Delimiter from here

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Good function but don't separate correctly ;-/

If I use Text before delimiter on

MARCO DI BENEDETTO

I obtain

DI

instead of

DI BENEDETTO 

😕

[mangaus1111]

Hi @Anonymous ,

see my solution in the pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJi0TPmFFEFXXd-Zn1?e=BeuR8g

[mangaus1111]

Ciao Diego,

you can use this in Power Query:

For Surname

= Table.AddColumn(#"Inserted Text Length", "Text After Delimiter", each Text.AfterDelimiter([Name], " "), type text)

For Name

= Table.AddColumn(#"Removed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([Name], " "), type text)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RamiKAL]

Hi, on Power query right click on the column, split by delimiter and then choose "Left-most delimiter". KLL

[Gengar]

Hi @Anonymous ,

This is my data:

You can create two calculated column:

_name = LEFT([Name],FIND(" ",[Name])-1)

surname =

VAR NAME1 = RIGHT([Name],LEN([Name])-FIND(" ",[Name]))

VAR NAME2 = RIGHT(NAME1,LEN(NAME1)-FIND(" ",NAME1))

return NAME2

Hope my answer could help you!

Ganger

[Anonymous]

Ehm, in your solution we lost part of surname (DI or DE), correct surnema is

DI BENEDETTO

DE ROBERTO

...that's my headache! 😉