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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Text to columns split to rows

Asteroid

Hi All,

 

I have bunch text strings want to use text to column and split into 2 columns. Any good ideas on tackle this? Thanks.

 

Sample Text String:

Argentina-$225Colombia-$300Greece-$7000Italy-$3750Qatar-$3250Russia-$360SaudiArabia-$1350SouthAfrica-$225Spain-$510Sweden-$5000UAEMainland-$45UnitedStates$200

 

Ideal output:

 

CountryPricing
Argentina225
Colombia300
Greece7000
Italy3750
Qatar3250
Russia360
SaudiArabia1350
SouthAfrica225
Spain510
Sweden5000
UAEMainland45
UnitedStates200

 

Alteryx
Alteryx

Hey @BosKev 

 

I used some quick RegEx to split the data into rows in place of the Text to Columns tool.  The RegEx piece is basically a way to match the string you want to put into each row.  So my RegEx string is 

\w+-\$\d+

Those symbols stand for: match a bunch of letters, followed by a hyphen, followed by a $ sign and then any number of digits. I recommend https://regex101.com/ as a good resource for testing and understanding RegEx along with some of our live trainings like this one: https://community.alteryx.com/t5/Videos/Parsing-with-Regular-Expressions/td-p/48576

 

Let me know if this helps!

 

-Chris

Asteroid

Thanks Chris, I will look into regex more since 60% of my time is spending on cleaning up messy data.

Labels