In my last tutorial I showed you how to convert text-based numbers to numeric values. Not only were the numbers stored as text, they also had the comma and decimal point reversed (at least they did for my UK customer).
If you missed the tutorial or want a reminder, you can click here to read it.
Anyway, a follow-up question was posed…
“All the numbers in the example contain a single comma. What if there were multiple commas and can the formula be flexible to account for any number of commas?”
**Download the sample spreadsheet here**
As you can see from the screenshot below, the function in column D replaces ONLY THE SECOND comma (in column C) with a full stop/period. The function in E13 generates an error because it tries to convert what is in D13 to a number but what is in D13 isn’t a number.
I found a solution to this problem as follows:
Up to Column C, the formulas are the same as those in the original spreadsheet:
Ultimately, I need to replace the LAST comma with a decimal point. The function in column D counts the number of commas in the corresponding cell in column C.
=LEN(C2)-LEN(SUBSTITUTE(C2,”,”,””))
The LEN function counts the number of characters in a cell (general note – it DOES include spaces – I’m always being asked that one)
The SUBSTITUTE function substitutes one character with another – in this example it replaces a comma with nothing – in other words, it’s actually removing the commas…
So 120,000,000,000 becomes 120000000000
LEN(SUBSTITUTE(C2,”,”,””)) counts the number of characters in the resulting string
So using some logical thinking, the difference between LEN(C2) and LEN(SUBSTITUTE(C2,”,”,””)) is the number of commas in the string.
The function in E2 has been amended. Originally it was
=SUBSTITUTE(C2,”,”,”.”,2)
The final argument (2), indicated that I wanted to replace the SECOND occurrence of a comma with a decimal point
The function now refers to D2 to determine which comma to replace
=SUBSTITUTE(C2,”,”,”.”,D2)
So this function counts the number of characters in C2 and subtracts the number of commas.