Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Remove Character - RegEx

kpurcell
6 - Meteoroid
Apologizes if this is a simple solution but I need to remove a charcter from the data in the column to be able to convert form sting to a double format.  Would RegEx assist with this?

I have a rate table with data from UPS with zone, weight and gross rate.  I have some records (record id 10 below) that have a $ included and when I covert this field from a string to double, the fields with a $ in the data turn to null.  Is there a way to remove the dollar sign so I can utilize the rates as a number versus a string?

RecordID    Weight    Zone    Gross Rate
1    1 Lbs.    2    6.24
2    1 Lbs.    3    6.68
3    1 Lbs.    4    6.87
4    1 Lbs.    5    7.17
5    1 Lbs.    6    7.49
6    1 Lbs.    7    7.59
7    1 Lbs.    8    7.71
8    1 Lbs.    44    23.88
9    1 Lbs.    45    23.96
10    1 Lbs.    46    $31.47
11    2    2    6.84
12    2    3    7.3
13    2    4    7.88
14    2    5    8.05
15    2    6    8.47
16    2    7    8.58
17    2    8    8.89
18    2    44    26.56
19    2    45    26.57
20    2    46    34.14
4 REPLIES 4
tom_montpool
12 - Quasar
You could use RegEx, but the simpler option is to use the Replace function in a Formula Tool. In the Formula tool properties, set the Output field to your Gross Rate field and then use the following expression: REPLACE([Gross Rate],"$","")
kpurcell
6 - Meteoroid
That is much easier and worked perfectly.  Thanks for the assistance.

Kevin
dest_ker
5 - Atom

Hi Kevin,

I see that Tom gave you an easy solution and I agree that using REPLACE in this case is easier. 
I have made an example by using REGEX tool to answer in your question.

rtaImage.jpg

At the REGEX tool chose in which field you want to replace the character, in this case "Gross Rate", at Regular Expression set $ - use back slash in front of dollar $ because if you leave just $ then the Regex will not replace $ because it's the special Characters which Regex is using. At the Output Method chose Replace and in the Replacement Text you can replace to whatever you want but in your case you want to replace $ with nothing so leave it empty.

rtaImage (1).jpg

rtaImage (2).jpg

Hope you will find this as helpful.
Good Luck

Dest

Divi
6 - Meteoroid

For this, you can simply use the formula tool with this expression :

 

Trim([GROSS RATE],"$") - This removes "$" from beginning and end of the string.

 

> First, trim the field and the convert the string to double. This results in what you are expecting.

 

> If you convert to double and then trim the field - You will get null or 0  for the values with "$".

 

I hope you will find this information useful.

 

 

Labels