Forum Discussion

Davis_CS's avatar
Davis_CS
Copper Contributor
Sep 17, 2026

Remove a comma from a number treated as text

Greetings! I'm new to this forum, and I don't know if this is the right place to ask. But, here it goes: I have a column of currency values which is a mess. I managed to clean it as much as possib...
excel
Formulas and Functions
training
IlirU's avatar
IlirU
Iron Contributor
Sep 19, 2026

Hi Davis_CS​,

Since you mentioned that you used functions like SUBSTITUTE or TRIM, I think it would have been helpful to place a double unary operator (--) right before them. This ensures that the result is returned as a number rather than text. Therefore, your formula should look something like this:

=--SUBSTITUTE(TRIM(.......))

Since I don't know the exact formula you used to get the text results you mentioned, I can't provide a precise solution tailored to your exact case.

However, if you want to convert the text values from your post into actual numbers, you can use the following formula (assuming your data ranges from cell A1 to A5).

=SUBSTITUTE(A1:A5, ",", "") / 100

If the list separator for your regional settings is a semicolon instead of a comma, then use the following formula:

=SUBSTITUTE(A1:A5; ","; "") / 100

Note: This formula assumes that every text string representing a number in your data always has two digits after the decimal separator.

I hope this formula helps! If not, please feel free to explain your issue in more detail. It would also be very helpful if you could share a screenshot and the exact formula you used to get those text results.

Regards,

IlirU