Parsing single field into existing column

[mmagliar]

Hello,

I am having a problem trying to parse two values in a column into an existing column.  I originally parsed based on the delimiter ":" but since these two fields do not have the same delimiter I am ending up with them in different columns.  Also, the scenario repeats on multiple lines.  I tried RegEx but no luck.  Any ideas would be greatly appreciated.

Attached is my data.  

[danilang]

Hi @mmagliar 

Try using this technique to preprocess the data to add the required ":" where they're missing.  Add a formula tool configured like this before your parse tool

Giving you this result

Of course, you'll have to clean up the extra spaces in the results

Dan

[mmagliar]

Dan,

Wow this is great.  MLS worked perfect.  For list price I have a $ prior to the number and this part is not working.  I can (should) strip all the $ from the entire file so any help here would be great.

Thanks again.

Mike

[danilang · Accepted solution]

Hi @mmagliar 

If you look at the records coming out of the input tool, you'll see that all the prices are pulled into Alteryx as numbers without the $ or the comma.  The $ that you see in Excel is part of the formatting applied to the cells for display.   I've modified the original workflow to add a $ into F2 if it's a price(see attached)

Dan

[mmagliar]

Thanks so much.  I really need to spend some more time with RegEx as it seems it can fix many types of data issues.

Mike