Text to columns by the last and second to last hyphen

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

Datasets

Regex

Output

Parse

Accepted answers

Thableaus

@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,

All comments

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!

Capture.PNG

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.

20190621-NameRegEx.yxmd

ChrisTX

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

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

IJReed

Hi @madslawton3

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

Good luck!

Text to columns example.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703