A customer sent me a spreadsheet containing data that had been exported from another system asked me to convert the “values” in column A (which were text) to numeric values.
The first thing that I noticed was that there was at least one leading space in each cell, in other words, each cell entry begins with one or more space characters. To remove spaces at the beginning (and end) of a cell entry, use the TRIM function.
B2 contains the function =TRIM(A2)
The result is that B2 contains the same as A2 but with the spaces removed:
I had to ask my customer for clarification – should the value in A2 be one-hundred-and-twenty or one-hundred-and-twenty-million or one-hundred-and-twenty-thousand with 3 decimal places, or something else?
The answer came back that A2 should contain one-hundred-and-twenty-thousand with 3 decimal places. As my customer is UK-based, the comma should be used to separate the thousands and the full-stop/period should be used to separate the decimal – i.e. 120,000.000
The next step was to replace the full-stop with a comma and the comma with a full-stop. I used the SUBSTITUTE function but did it in 2 steps.
C2 contains the function =SUBSTITUTE(B2,”.”,”,”)
This replaces the full stop in B2 with a comma
Now I need to replace the comma in C2 with a full stop, but with the original comma still there and an extra comma now added (thanks to the previous step), C2 contains 2 commas. I only want to replace the 2nd occurrence. Again I used the SUBSTITUTE function but with an extra argument:
D2 contains the function =SUBSTITUTE(C2,”,”,”.”,2)
This replaces the SECOND occurrence of the comma in C2 with a full stop
Finally, in E2, I need to convert the textual value into a numeric value. There are 2 ways to do this:
The VALUE function converts a text-based number into a real number. To do this, I would enter =VALUE(D2) into E2. However, I would have to manually format the cell to display commas and 3 decimal places.
I chose instead to use the FIXED function which displays a numeric value with a fixed number of decimal places, optionally adds commas and, if appropriate, converts a text-based number into a real number
E2 contains the function =FIXED(D2,3,FALSE)
This instructs Excel to display the value of D2 (which it converts to a number) with 3 decimal places and to include commas (TRUE means prevent commas, FALSE means do not prevent commas)