Parse data with strings and numeric data into 2 columns

I have this 1 column with the following records and I want to parse and split into 2 columns and just use the description and the last number of each record:

DISPLACED BESP AXE RVRSL 9 35.513DB 19.23

NET COMMITTED CHARGES 715,381.24 46,722.45 237,310.54CR

TOTAL CHARGES 1.34 3,213.79 2,457.34DB

Similar to the following:

DISPLACED BESP FIN RVRSL 19.23

NET COMMITTED CHARGES 237310.54

TOTAL CHARGES 2457.34

What would be the best way to accomplish this? Appreciate any guidance

Thanks!

Parse

Accepted answers

OllieClarke

Hey @OEvangelista

you can use the following RegEx to parse your two columns

(\D+).* ([\d\.\,]+)

This will take everything before the first number into your description column, and the final number after a space into a final value column. You can then clean the description and final value column

Hope that helps,

Ollie

parse.yxmd

All comments

TheOC

hey @OEvangelista

You can use 2 functions of the formula tool together to achieve this:

Getword(string, n) - gives you a specific word (defined by spaces) from a string

Countwords(string) - gives you a numeric for how many words are in a string.

Given that we want the last 'word' in those strings, we can use countwords (-1) to get the index of the last word, then supply this to the getword command.

I've attached a workflow that shows this, please find attached.

Cheers,
TheOC

oevangelista.yxmd

OEvangelista

Thanks for the quick response!

OllieClarke

Hey @OEvangelista

you can use the following RegEx to parse your two columns

(\D+).* ([\d\.\,]+)

This will take everything before the first number into your description column, and the final number after a space into a final value column. You can then clean the description and final value column

Hope that helps,

Ollie

parse.yxmd

OEvangelista

Thanks so much...it works!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703