Split fullname to LastName, FirstName, and MI

[Anonymous]

Hi,

We have a FullName field with LastName,FirstName Middlename format. I need to split lastname, firstname, and MI to separate columns. Could someone please help? I need to do it through the expression and not the script itself. Right now, I was able to get LastName using SubField(Name,',',1)

Current FullName format:

FullName Format
Smith,James R
Smith,James Randy
Smith,James Randy JR

I need it to separate it like below:

LastName	FirstName	MI
Smith	James	R
Smith	James	R
Smith	James	R

Thank you.

[Peter_Cammaert · Accepted solution]

For FirstName you can use

Subfield(Subfield(Name, ',', 2), ' ', 1)

And for MI (which I assume contains only the initial character):

Left(Subfield(Subfield(Name, ',', 2), ' ', 2), 1)

The operations are self-explaining. If not sure about what is happening here, ask away.

[Edit] Right, it's MiddleInitial not MiddleName.

[Anonymous]

Thank you so much. That worked like a charm.

[MarcoWedel]

Hi,

depending on how reliable spaces occur in your data, one solution might be also:

LOAD [FullName Format],
    SubField([FullName Format],',',1) as LastName,
    TextBetween([FullName Format],',',' ') as FirstName,
    Mid([FullName Format],Index([FullName Format],' ')+1,1) as MI
FROM [https://community.qlik.com/thread/272390] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[Anonymous]

Wow! Thank you.