How to remove white space from a number
I believe your imported data includes non-breaking spaces instead of standard spaces. Hence, the formula =SUBSTITUTE(A1," ","")
does not work. Instead of using the space bar to type the " "
in the SUBSTITUTE formula, try a non-breaking space. You can select this in the Character Map or use keystroke Alt+0160.
EDIT:
Since substituting regular spaces successfully removed the leading and trailing spaces, you should use nested Substitute formulas: one to sub the regular spaces and one to remove the no-break spaces.
=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")
You can copy this formula to try it in your workbook.
Just do a search and replace over a group of cells you have selected, select your column and go to Home
and Find and Select
. Search for a space, and replace it with nothing.
Alternately if you want to retain the originally formatted number you can use the substitute function to provide a revised version of the string in another cell:
=SUBSTITUTE(A2," ","")