Remove special character ";" from text format column and convert the column to number.

[Anonymous]

Hi

I have a text format column with values like:
120;
34;
150; etc.

I need to get rid of the ";" and then divide the number by 100. So at the end the numbers should look like this:

1,20
0,34
1,50

I made a new column with SUBSTITUTE(TABLE, ";", "") and now I have only numbers but this is still text format, can't change it to whole number or decimal number without the whole new column going #ERROR.

I can't divide a text format with 100. 

[themistoklis]

@Anonymous 

You need to double check all the values in the new column so as to make sure that there is no text somewhere (e.g. a letter or a symbol).

Also make sure that there no leading and trailing space so i suggest you use the TRIM function as well.

e.g. TRIM(SUBSTITUTE(TABLE, ";", ""))

[mahoneypat]

That's an odd error to see for a Replace Values step.  Can you provide some example/mock data (with a link or as a table for copy/paste) so a specific solution can be suggested?

Pat

[Anonymous]

Yeah it was weird, I couldn't even delete columns or do anything, so I just saved the file and reopened it and now it works :O. Thank you for the help

[mahoneypat · Accepted solution]

You can do this more easily in the query editor.  Use Replace Values and put ; in the top box and leave the bottom box empty.  Then change it to a number type, and use the button on the Transform tab to divide by 100.

Pat

[Anonymous]

Thank you for the answer. Actually I tried that too but for some reason it gives me error:
Expression.Error: The key didn't match any rows in the table.
I don't understand why, i right clicked on the correct column name and chose Replace Values...
I even copied a value to notepad and then copied the ; symbol from there but still same result.