Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Text to columns by the last and second to last hyphen

Highlighted
6 - Meteoroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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,

6 - Meteoroid

 

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

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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.*)

 

middlenamesslash.PNG

 

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,

Highlighted
12 - Quasar

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

 

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

 

Capture.PNG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
12 - Quasar

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.

Highlighted
8 - Asteroid

Hi @madslawton3 

 

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

 

Good luck!

Highlighted
6 - Meteoroid

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

Labels