Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've the follwoing names in a column:
Lastname, firstname and in some cases
lastname, firstnameLastname, firstname,...
The following lastname ist joined to the previous firstname without a symbol. There is only a line break.
e.g.:
Miller, James
Smith, Marc
Flesher, JonasRabo, David
Jaris, Martha
Holder, SvenVeno, SusenStark, JulieZery, Claus
How can I separate the names in the script if there are several in one cell?
Great! Thanx!
please mark as helpful so other users can see the case is closed.
Regards
You can try something like below
Data:
LOAD * INLINE [
Names
"Miller,James"
"Smith,Marc"
"Flesher,JonasRabo,David"
"Jaris,Martha"
"Holder,SvenVeno,SusenStark,JulieZery,Claus"
];
Data1:
NoConcatenate Load
Names
Resident
Data
Where SubStringCount(Names,',')=1
;
Concatenate
Load
FindOneOf(Names,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',3)+1) as Names1
SubField(Replace(Names,Mid(Names,FindOneOf(Names,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',3)),' '& Mid(Names,FindOneOf(Names,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',3))),' ') as Names
Resident
Data
Where SubStringCount(Names,',')=2