Parse data with strings and numeric data into 2 columns

[OEvangelista]

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!

[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]

Thanks for the quick response!

[OllieClarke · Accepted solution]

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

[OEvangelista]

Thanks so much...it works!