Text to columns by the last and second to last hyphen

[madslawton3]

Hi All!

Is there a way to text to columns a string by ONLY the last hyphen in the string and the second to last hyphen?

For Example, I am trying to isolate users' names as generated by an application. So, some appear like this:

EUR-OPER/ANL-Madison Lawton

BUT some have additional hyphens and a user could have a hyphen in their name as such:

EUR-OPER/ANL-MGR-Madison Kaln-Lawton

Thus, I am looking for a solution so that I can isolate the string from either the second to last and last hyphens so that I just have the users' name within the new columns.

Please advise your thoughts! Thank you

[Thableaus]

Hi @madslawton3 

Depending on how your data pattern looks like, you could use a REGEX tool to do that.

The only challenge is to identify patterns for the user's names. 

Ex: I can see the only portion of your string that contains titlecased words is the user's name. So we could create a REGEX to identify these patterns.

If you could show us more examples of how your data looks like, maybe we can come up with a useful REGEX expression that will come in handy.

Cheers,

[madslawton3]

Thank you for your response! I think that's kind of my issue. There really is no set 'pattern'. Which is why i wanted to see if there was maybe a syntax for "second to last' and 'last'.

I've included a sample below. Notice for some users it ends after the last hyphen, but for the one user herein, there is a hyphen for the name. So that one would have to be the second to last hyphen. Let me know what you think!

[CharlieS]

It is possible to find the string position of the last hyphen by using the ReverseString( function with the FindString( function. This method could be nested to find the last 2,3,whatever. However, I went about this the same way @Thableaus suggested and relied on the pattern of upper and lowercase strings (title case) to indicate the beginning of the name portion.

The attached example shows how I used A RegEx tool to identify the pattern of hyphen, upper, then lower case character using the expression "\-\u\l+". This process identified the location where the text to column split needed to occur so I replaced that hyphen with another character to achieve this.

[Thableaus · Accepted solution]

@madslawton3 

Assuming there are no middle names with hyphens and users must have at least a First Name and Last Name, this expression might fit:

Parse Method: .*-(.*\s.*)

This basically means look for any character until you find the last hyphen that is followed by any sequence of characters followed by at least a space (\s).

I know this sounds confusing, but with this data works.

Cheers,

[ChrisTX]

If the string you're trying to capture is always mixed-case, try this in a RegEx tool:

^([^a-z]*)([A-Z].*)

[CharlieS]

@Thableaus has another good suggestion there with the space. Otherwise you might need to compile a list of department/role strings so they can be identified at the beginning of each string. 

[ChrisTX]

Looking at your sample data.....Try using the Text to Columns tool with a dash as a separator.

Posting your sample data as text or a table would help.

[IJReed]

Hi @madslawton3 

If there is always a space between the first and last name, you could try something like the attached. 

Good luck!

[madslawton3]

No that makes complete sense! Thank you so much, I appreciate it.