Remove Character - RegEx

[kpurcell]

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

[tom_montpool · Accepted solution]

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]

That is much easier and worked perfectly.  Thanks for the assistance.

Kevin

[dest_ker · Accepted solution]

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.



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.





Hope you will find this as helpful.
Good Luck

Dest

[Divi]

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.