Text To Columns with multiple spaces

Hello everyone,

I have a data set that looks as below.

I am interested only in the "PO Number" values. If it has a value next to it, I would like to output it as a separate column that says PO Number or else leave a blank value. I know if the column has single space separator, I can use Text to Columns tool and separate using \s. Please help me in this scenario. Thank you amazing people!!!!

Parse

Accepted answers

T_Willins

i @murthybhs,

I would use a RegEx Parse as it allows for easier identification of the data you are looking for.

The formula .*?PO Number\s+(.*?)\s+Zip.* does the following:

.*?PO Numbers\s+ From the start of the string look at everything until if finds the text PO Numbers followed by one or more spaces

(.*?)\s+Zip.* Once that is found return everything until the next one or more spaces that is followed by the text Zip (and everything else)

Text to Columns.png

Text to Columns.yxmd

All comments

T_Willins

i @murthybhs,

I would use a RegEx Parse as it allows for easier identification of the data you are looking for.

The formula .*?PO Number\s+(.*?)\s+Zip.* does the following:

.*?PO Numbers\s+ From the start of the string look at everything until if finds the text PO Numbers followed by one or more spaces

(.*?)\s+Zip.* Once that is found return everything until the next one or more spaces that is followed by the text Zip (and everything else)

Text to Columns.png

Text to Columns.yxmd

Maskell_Rascal

Hi @murthybhs

Are you looking for something like the below?

If so, you can use the RegEx tool to parse the data you want.

Regular Expression:

(PO Number)\s{1,}(\w{2}\d{1,})

RegEx tool configuration:

Attached is a sample workflow.

Cheers!

Phil

RegEx Parse PO Number.yxmd

murthybhs

Thank you for the solution @T_Willins . It looks like RegEx expressions solve most of my other applications as well. Is there is a book or source where I can learn more about RegEx expressions?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703