Split the string using Regex Tool

[RichardAlt]

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 1	Column 2	Column 3	Column 4
INVESTMENT HOLDINGS
Equity
Sample Investment Asia-Pacific Ex-Japan 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.40	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%

Thank you!

[paulfound]

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

[RichardAlt]

@paulfound Not working for me

[paulfound]

Hi @RichardAlt 

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

[RichardAlt]

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]

Hi @RichardAlt 

I'm not seeing that.

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

[RichardAlt]

@paulfound , Yup, There is spaces in front.

[paulfound · Accepted solution]

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