4 Replies Latest reply on Mar 31, 2017 6:54 AM by jack.glavosek

# How to break a string based on position of special character?

I am working with a string of names that are pulled from a free text field.  I need to identify the first name only.

My definition of first name is "All characters from left to right until the end of the string or until we reach a special character."

I've created a formula that looks for the location of the special character.  The problem I've found is when the string contains more than one special character.

The attached workbook shows my 'Real Name' and the 'Goal Name.'  The 'New Name' field is my attempt at converting the real name into the goal name.

There must be a better way for me to identify the position of a non-alpha character in my string.

• ###### 1. Re: How to break a string based on position of special character?

Hi Jack,

This may not be ideal (or the most efficient way), but I tried it and it works. Try using this for your Position of Special Character formula:

min(min(min(if (find([Real Name]," ")>0) then (FIND([Real Name]," ")-1) else len([Real Name]) END,

if (find([Real Name],"/")>0) then (FIND([Real Name],"/")-1) else len([Real Name]) END),

if (find([Real Name],",")>0) then (find([Real Name],",")-1 )else len([Real Name]) END),

if (find([Real Name],"-")>0) then (find([Real Name],"-")-1) else len([Real Name]) END)

Basically, it finds the minimum (i.e., first) value for a special character. If there are none, it returns the length of the real name. Again, this may not be the best way, but it should work!

Hope it helps.

Michael

• ###### 2. Re: How to break a string based on position of special character?

Jack,

In addition to Michael's solution above, you could also use the SPLIT function in Tableau like this:

Hope that helps.

Kaz.

• ###### 3. Re: How to break a string based on position of special character?

best way to do this is with regular expressions. I was able to do this in 10.1 but it appears you are using an earlier version. I am not sure when the regular expression functions were added. RegExr: Learn, Build, & Test RegEx  is a great resource for learning and testing regular expressions.

Hope this helps!

• ###### 4. Re: How to break a string based on position of special character?

This solution seems to be the best because it handles all special characters.  Thanks for help.