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