community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

RegEx / Parse - Field with numeric and alpha

Atom

I have the data below in one field.  I am trying to return the data after the first hyphen.  

 

In the case of the record with 6320020.410 - I/S DFIT - N/C ASSET, I want to end up with I/S DFIT - N/C ASSET.  My attempts at using RegEx are not producing the desired results.  Any help is greatly appreciaed.  Thanks.

 

6311010 - SIT EXPENSE
6320020.410 - I/S DFIT - N/C ASSET
6320020.411 - I/S DFIT - N/C ASSET
6320040.410 - I/S DFIT - N/C LIAB PLANT
6320040.411 - I/S DFIT - N/C LIAB PLANT

Alteryx Certified Partner
Alteryx Certified Partner

Hi @wg 

 

This RegEX expression can be useful.

 

 Trim(REGEX_Replace([Field1], ".*?-(.*)", "$1"))

 

Cheers,

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@wg 

 

Or this string function:

 

Trim(Right([Field1], Length([Field1]) - FindString([Field1], "-") - 1) )

 

Cheers,

Meteoroid
In general a very simple way to accomplish what you want - get rid of stuff before a certain symbol is encountered in the string - is to use the Text To Columns tool and use the symbol in question as the delimiter. In this case, if you insert a Text To Columns tool, use "-" as a delimiter (don't include the citation marks in the tool, just the hyphen), split into 2 columns, leave extra characters in last column, then you should be good to go; the 2nd column generated should then include the information you want, and only that (however you may also need to add a data cleansing tool after the Text To Columns tool to remove any potential leading spaces in the column, as well as a select tool to remove the unnecessary first column). A derived benefit from using the Text To Column tool this way is that you get to keep track of the information you remove from the string - it's easy to find out what was 'lost', if it turns out later on that you actually need that part of the string as well.
Alteryx Certified Partner

Hi,

 


A suggestion for using the regex tool.
I did the text separation in 2 steps.

Attached is the example workflow.

Atom

Great idea.  Thanks.

Labels