Forum Discussion
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 possible using the functions SUBSTITUTE and TRIM. These functions transformed the unformated numbers into text, and now it looks something like this:
| 6,764,68 |
| 1,319 |
| 1,948 |
| 301,40 |
| 2848,08 |
The former is a sample from 165 entries. Is it possible to remove the first comma of the string, so the values look like this instead?
| 6764,68 |
| 1319,00 |
| 1948,00 |
| 301,40 |
| 2848,08 |
What I would like to do is to remove the second comma from right to left in each value, while keeping the first which marks the cents of the transaction. Can this be done?
Thanks all in advance :D
3 Replies
The Excel function VALUE will convert text that may be regarded as a number back to the number.
= VALUE(text)Cut and paste will overwrite the original text if required.
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, ",", "") / 100If the list separator for your regional settings is a semicolon instead of a comma, then use the following formula:
=SUBSTITUTE(A1:A5; ","; "") / 100Note: 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
Try
=LET(n,TEXT(A1:A5,"#,00"),d,REGEXEXTRACTI(n,",\d{2}$"),BYROW(n&"§"&d,LAMBDA(r,SUBSTITUTE(SUBSTITUTE(TEXTBEFORE(r,"§"),TEXTAFTER(r,"§"),""),",","")&TEXTAFTER(r,"§"))))
Bye
