We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Split the string using Regex Tool

RichardAlt
8 - Asteroid

Hi Guys,

 

How can I split the string using Regex Tool.

This is my sample data:

INVESTMENT HOLDINGS
Equity
Sample Asia Pacific Income and Growth Fund            18,598.12     17,508.68        4.82%
Sample Investment Asia-Pacific Ex-Japan Fund          85,039.16     77,517.74       21.36% 
Sample Investment Progress Fund                       15,741.42     11,796.75        3.25%
Sample Investment Regular Savings Fund                89,924.28     66,125.98       18.22%
Fixed Income
Sample Bond Plus Fund                                 32,480.68     34,222.10        9.43%
Mixed Asset
Sample Flexi Growth & Income Fund                   142,216.38     108,590.49       29.92%
TOTAL INVESTMENT HOLDINGS                             384,000.04     315,761.74       86.99%
CASH AND DEPOSITS
Cash at Bank                                                          47,225.57       13.01%
Fixed Deposit (Include Money Market, Repo & Call Deposit)                    -         0.00%

 

This is my desired output:

Column 1Column 2Column 3Column 4
INVESTMENT HOLDINGS   
Equity   
Sample Investment Asia-Pacific Ex-Japan Fund 18,598.1217,508.684.82%
Sample Investment Asia-Pacific Ex-Japan Fund85,039.1677,517.7421.36%
Sample Investment Progress Fund  15,741.4011,796.753.25%
Sample Investment Regular Savings Fund  89,924.2866,125.9818.22%
Fixed Income   
Sample Bond Plus Fund 32,480.6834,222.109.43%
Mixed Asset   
Sample Flexi Growth & Income Fund 142,216.38108,590.4929.92%
TOTAL INVESTMENT HOLDINGS 384,000.04315,761.7486.99%
CASH AND DEPOSITS   
Cash at Bank    47,225.5713.01% 
Fixed Deposit (Include Money Market, Repo & Call Deposit)   - 0.00%

 

Thank you!

7 REPLIES 7
paulfound
11 - Bolide

PaulFound_0-1598436163626.png

This RegEx will replace any white space of 2 or more and replaces with a bar "|" then split by column.

PaulFound_1-1598436238339.png

 

RichardAlt
8 - Asteroid

@paulfound Not working for me

paulfound
11 - Bolide

Hi @RichardAlt 

 

I have attached a quick example see if you can see whats different.

RichardAlt
8 - Asteroid
INVESTMENT HOLDINGS
MAMSB - DIBP_R_2018
Equity
Sample Asia Pacific Income and Growth Fund            18,598.12     17,508.68        4.82%
Sample Investment Asia-Pacific Ex-Japan Fund          85,039.16     77,517.74       21.36% 
Sample Investment Progress Fund                       15,741.42     11,796.75        3.25%
Sample Investment Regular Savings Fund                89,924.28     66,125.98       18.22%
Fixed Income
Sample Bond Plus Fund                                 32,480.68     34,222.10        9.43%
Mixed Asset
Sample Flexi Growth & Income Fund                   142,216.38     108,590.49       29.92%
TOTAL INVESTMENT HOLDINGS                             384,000.04     315,761.74       86.99%
CASH AND DEPOSITS
Cash at Bank                                                          47,225.57       13.01%
Fixed Deposit (Include Money Market, Repo & Call Deposit)                    -         0.00%

 

What about something like this?

I have a row contains this data "MAMSB - DIBP_R_2018"

When I tried your solution It move to column 2 instead to column 1 only.

paulfound
11 - Bolide

Hi @RichardAlt 

I'm not seeing that.

 

PaulFound_0-1598448501351.png

Not sure what to say, is there spaces at the front in the original?

RichardAlt
8 - Asteroid

@paulfound , Yup, There is spaces in front.

RichardAlt_0-1598449669285.png

 

paulfound
11 - Bolide

Drop in a formula tool before the regex. and trim off the white space.

PaulFound_0-1598449893447.png

 

 

Labels
Top Solution Authors