trim cells with space in betwen numbers in cell

  • Thread starter Thread starter Morten
  • Start date Start date
M

Morten

How can you remove "spaces" between numbers in one cell? (ie 1 000 000) I
need to remove the space between the numbers over a large range of cells with
numbers in them with spaces.

I have tried trimming, cleaning the cells and defining them as numbers but
it seems like the only way to do this is by physically going into each cell
and deleting the space.

A shortcut in doing this would appreciated.

Thanks for all your help.
 
Highlight the column, Ctrl+H, find space replace with nothing. Will
auto-format to number.
 
Assuming its truly the space symbol:

=VALUE(SUBSTITUTE(A2," ",""))

Note that if space character is some other unprintable character such as
CHAR(160), you could use the CODE function to determine which symbol it is
exaclty, and then remove it like so:

=VALUE(SUBSTITUTE(A2,CHAR(160),""))
 
Back
Top