How to split string text and numbers into different columns

[dunkindonut7777]

Hi I have a data here that shows the combination of string and numbers. I want to separate or split the two different characters into different column:

Input

122220 Property Equipment

122330 Office- Equipment

Expected Output1	Expected Output2
122220	Property Equipment
122330	Office- Equipment

Can you help me resolve this one pls? Thank you

[DenisZ]

Please see attached workflow

[ShankerV]

Hi @dunkindonut7777 

1. Use the RegEX tool 

2. In the Column to Parse, select the Input column

3. In the regular expression (\d)\s(.*)

4. Use the Output Method as Parse

In the Output Columns, modify the Column name from RegExOut1 to Expected Output1 and RegExOut2 to Expected Output2 or any other desired column names

Shanker V

[dunkindonut7777]

hello than you for this, how about this one?

Input

5100130    AR Penalty/Interest credit clearing

5100140    AR CWT clearing

5100140    AR CWT clearing

5100160    Cinema cards payable

I find it hard to separate these characters using regex tool. Thank you

[ShankerV]

Hi @dunkindonut7777 

For your second input shared also, my output will work.

1. Use the RegEX tool 

2. In the Column to Parse, select the Input column

3. In the regular expression (\d)\s(.*)

4. Use the Output Method as Parse

In the Output Columns, modify the Column name from RegExOut1 to Expected Output1 and RegExOut2 to Expected Output2 or any other desired column names

In the line number 3, I am splitting digits and other characters followed by space.

Shanker V

[ShankerV]

Hi @dunkindonut7777 

Apologies I missed the *

Use this corrected one, this will work 

(\d*)\s(.*)

[dunkindonut7777]

Hello I tried your workflow but this is the result:

The digits will become "0"

[ShankerV]

After you used this Regex and split into 2 columns

For the second column it might contain the Space in front, use the data cleansing tool to remove the blanks in front.

Hope this helps.

Shanker V

[ShankerV]

Hi @dunkindonut7777 

Use this below.

(\d*)\s(.*)

For the second column it might contain the Space in front, use the data cleansing tool to remove the blanks in front.

If you are ok with the space, then not an issue. You don't need to remove the space created in front of the column 2.

Hope this helps.

Shanker V

[ShankerV]

Hi @dunkindonut7777 

Hope the previous solution worked.

1. One way is the old one, where you parse and use the Data cleansing tool to remove the whitespaces.

2. Way 2, try this

(\d*)\s\s\s\s(.*)

As there is 4 spaces between the digits and the letters you have shared in the sample, this will generate the accurate output which you are looking for.

The () used to create new columns.

\d* - helps to read the digits

\s helps to skip the spaces where we don't want to consider

.* helps to read the remaining characters after the space

Many thanks

Shanker V