Change string format of name: "SMITH, JOHN" to "JOHN SMITH"

[wonka1234]

Hi all,

I am trying to rearrange a string from SMITH, JOHN to JOHN SMITH.

Does anyone know how to do this?

[RolandSchubert · Accepted solution]

Hi @wonka1234 ,

you can use a Formula tool to do the job. The result is the right part of the string (starting after the ","), a blank and the left part of the string (to the ","), so the formula could be:

TRIM(RIGHT([Original], FindString([Original], ','))) + ' ' + LEFT([Original], FindString([Original], ','))

FindString identifies the position of the ",", the "LEFT" and "RIGHT" functions take the respective part.

Let me know if it works for you.

Best,

Roland

[AngelosPachis · Accepted solution]

Hi @wonka1234 ,

Here's one way you can do it (EDIT: similar to what @RolandSchubert described above) by using the Find String function to locate the comma and then find the name/surname

I've broken it down to multiple formula tools so you can see what each tool does, hopefully it's easier to pick it apart

[wonka1234]

@AngelosPachis thanks this worked nicely.

However how do I drop middle names now lol

ie JOHN P SMITH and go to JOHN SMITH.

I dont need to redo the above workflow, just a new formula to possible parse out middle name initial.

[AngelosPachis]

@wonka1234  although you can use a similar approach than the one described below, it might get a bit messy so I think using RegEx is easier here.

Assuming that your Last/Middle/First Names are all separated by a whitespace (\s in Regex language) you can create three groups that contain each one of those elements as shown below

 .+ means one or more characters (uppercase or lowercase letters, numbers, special characters etc. So you have split your name in three groups and then you only want to keep the third one (First Name) and the first one (Last Name). That's $3 to keep the third group separated by a whitespace and then you need the first group $1

Hope that answers your question

[AngelosPachis]

@wonka1234if you don't mind please do accept Roland's reply as a solution as well, since the idea behind my response and his is essentially the same.

[wonka1234]

@AngelosPachis thanks for this regex!

However it seems to reversing to first and last name again!

not john smith lol.

[AngelosPachis]

@wonka1234 reading your post above I thought the requirement was to go from

JOHN P SMITH

to

JOHN SMITH

Didn't the post above answer your question?

[wonka1234]

Sorry my fault.

For example after your fullname formula I have values like :

ALEJANDRO J GOMEZ   and would like it to be ALEJANDRO GOMEZ

instead I am getting GOMEZ ALEJANDRO

[AngelosPachis · Accepted solution]

@wonka1234  I guess then you would have to rearrange the order that the dollar signs appear (so $1 $3). Is it clear how those work from my previous post above?