Parsing unstructured data

[carterfleming]

I'm trying to extract data from a long string of numbers and spaces. One of the columns I'm trying to extract/create is a dollar amount (or at least I need it to be). In the example output below, I want the the first number shown to convert to $1,000.00, the second should be $30.75, the next $342.96 and the last one $265.00. Any idea on how to convert this data? ($ signs and commas not required, just an accounting/financial format i.e. 1000.00)

 

[BS_THE_ANALYST · Accepted solution]

@carterfleming Regex can help here :-).
Before:

After:

[Luke_C]

Hi @carterfleming 

Assuming the column only contains blanks or numbers, you could do something like this:

Select Tool to change the type

Divide by 100 to capture the cents