Deleting blanks

[Marianne]

When I download statistical information from a specific source, there are
spaces in the cells (either first or in between the numbers), and the numbers
are not interpreted as numbers. How can I easily delete all spaces instead of
entering each cell and doing it manually?

 

[Gaurav]

Try TRIM function.

for example you have some value in A1. In B1 enter =TRIM(A1)

 

[Dave Peterson]

Select the range to fix
Edit|Replace
what: (spacebar)
with: (leave blank)
replace all

You may want to look at this...

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[MartinW]

Hi Marianne,

As well as the other responses there is also the non-breaking space
problem that is often inherited with info copied from a website.

Use Edit>Replace
Replace what: Alt+0160
Replace with: Leave this blank
Click Replace All

Alt+0160 is done by holding down the Alt key, type 0160
on the number pad(only), then release the Alt key.

HTH
Martin

 

[Marianne]

Thank you - but nothing happens when i press Replace all... Any suggestions?

Marianne

 

[Marianne]

Thank you - but nothing happens when I press Replace all... Any suggestions?

Marianne

 

[MartinW]

Hi Marianne,

Sorry I should have said select the data first and then go to Edit>Replace
etc.

Also watch for the trap of the NB space being left in the replace what box
when
you try the second time. Best to click in the box then hit backspace two or
three
times then hit delete two or three times, or maybe ten times on each <g>

HTH
Martin

 

[muddan madhu]

Try this one :
suppose u have values in A1 ,

use this formula =SUBSTITUTE(A1,CHAR(160),"")

 

[Jerry W. Lewis]

Removing the spaces will still leave a string. Try
=VALUE(SUBSTITUTE(A1,CHAR(160),""))

Jerry

 

[David Biddulph]

Or just =--SUBSTITUTE(A1,CHAR(160),"")

 

[Marianne]

Thank you - now the blanks disappear, BUT I still cannot make graphs based on
the data (the whole point...!). Excel obviously does not interpret them as
numbers although I format them as numbers afterwards...

Marianne