trim cells with space in betwen numbers in cell

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.
 
S

Sean Timmons

Highlight the column, Ctrl+H, find space replace with nothing. Will
auto-format to number.
 
L

Luke M

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),""))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top