Simple Dollar Value Data cleaning

jhan

Hello

I am a complete novice first time user of Alteryx - and I am having some difficulty cleaning data:

For example, I have a set of dollar values in a variety of format found in a single column:

535,00 $
1 695,00 $
$895.00

700.00 -
$1,049.00

1,230

(800.00)

2100

I would like to transform them all into and put it in a single new column:

535.00

1695.00

895.00

-700.00

1049.00

1230.00

-800.00

2100.00

Any help would be appreciated.

Thank you,

Datasets

Transformation

Accepted answers

estherb47

Ah, we are so close! I forgot that 2 zeros can occur in the number itself.

You can fix 2 of the formulas, and it should now work. changes are in blue

First, change the second formula to IF EndsWith([CleanedNumbers], "-") THEN "-"+REGEX_Replace([CleanedNumbers], "(.+?)(-)","$1") ELSE [CleanedNumbers]
ENDIF

Next, change the third formula to:
REGEX_Replace([CleanedNumbers], "(.+?)((?:\.|\,)\d{2}[^\d*])","$1") This adjusts so it will only remove the numbers that are actually decimals. We're saying a comma, followed by two digits, followed by anything that's not another digit should be removed.

All comments

estherb47

Hi @jhan ,

Try the ReplaceChar function in a formula tool

ReplaceChar([Field],"$)", "")

That should remove all $, and ).

You can use the Replace function to replace ( with -

And finally, the Data Cleansing tool can remove all of the extra spaces.

Cheers!

Esther

WHOOPS! Missed a few of the cleanup steps (moving the ending "-" and removing commas)

jhan

Thanks, @EstherB47

I think the biggest problem that I am having is to build a formula that can distinguish the comma that is ahead of the decimals versus comma that is denoting 1,000's.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317