3 Replies Latest reply on Feb 7, 2019 9:03 AM by Nick Parsons

# Split First and Last Name

Hello,

For my worksheet I have several sales reps displayed in the following.

JohnSmith

John Smith

Jonathan Smith

MikeLee

Mike Lee

Michael Lee

Because of the space and Michael and Joanthan, its showing Mike Lee and John Smith as three separate sales rep when they are the same person. Anyway to combine them into one name or put a space between the first and last name?

I've tried a replace calculation but its not working.

Thanks

• ###### 1. Re: Split First and Last Name

If you knew all the values you'd like to conform to a single value, then I'd suggest creating a separate data set that will define how you wish to conform them. For example, say this is your original data set:

 Name JohnSmith John Smith Jonathan Smith MikeLee Mike Lee Michael Lee John Doe Jane Doe Mike Chao

Create a new data set like this:

 Name Conformed Name JohnSmith John Smith John Smith John Smith Jonathan Smith John Smith MikeLee Mike Lee Mike Lee Mike Lee Michael Lee Mike Lee

Then, in use a left join:

Finally, use a calculated field to give you either the confirmed name or the original name (if there is no conformed name):

Final Name

// Use the conformed name if it exists.

IF ISNULL([Conformed Name]) THEN

[Name]

ELSE

[Conformed Name]

END

1 of 1 people found this helpful
• ###### 2. Re: Split First and Last Name

I would look to clean up this data outside Tableau.

There are so many permutations of possibilities in data cleanup like this.  For instance, How would you know that John and Jonathan are the same guy?  Even more, take a name like Richard.  You could have Richard, Rick, Ricky, ****, Dickey, and they could all be the same guy.  Or different guys.

And when the data entry did not put in a space between first and last names, finding how to make that break might be difficult.  I guess there could be some calc that looks at a character, and if it is uppercase and the prior character is not, then that would signal a break between first and last name, but that approach would choke on a last name like McCarthy.  (You would need to concoct some sort of iterative loop as well, to look character-by-character, and Tableau calc syntax doesn't have the concept of looping.)

There are better tools than Tableau to do this sort of data cleanup.

1 of 1 people found this helpful
• ###### 3. Re: Split First and Last Name

Agree with clean up ideas. But if you don't clean up, a regex could give you spacing for a quick fix.

First Name =

REGEXP_EXTRACT([Name],'([A-Z][a-z]*)')

Last Name  =

REGEXP_EXTRACT([Name],'.+([A-Z][a-z]*)')

or Full Name =

REGEXP_EXTRACT([Name],'([A-Z][a-z]*)') + " " + REGEXP_EXTRACT([Name],'.+([A-Z][a-z]*)')

1 of 1 people found this helpful