Parsing single field into existing column

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.

example data.png

parse example.xlsx

Parse

Preparation

Accepted answers

danilang

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

Parse v2.yxmd

All comments

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

Parse.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703