Deleting blanks
 

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?

Deleting blanks
 

Try TRIM function.

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


"Marianne" wrote in message
...
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?

Deleting blanks
 

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 he
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.)

Marianne wrote:

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?

--

Dave Peterson

Deleting blanks
 

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 EditReplace
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" wrote in message
...
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?

Deleting blanks
 

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

Marianne

"MartinW" wrote:

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 EditReplace
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" wrote in message
...
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?

Deleting blanks
 

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

Marianne

"MartinW" wrote:

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 EditReplace
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" wrote in message
...
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?

Deleting blanks
 

Hi Marianne,

Sorry I should have said select the data first and then go to EditReplace
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


"Marianne" wrote in message
...
Thank you - but nothing happens when I press Replace all... Any
suggestions?

Marianne

"MartinW" wrote:

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 EditReplace
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" wrote in message
...
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?

Deleting blanks
 

Try this one :
suppose u have values in A1 ,

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





On Sep 8, 7:26*am, Marianne
wrote:
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?

Deleting blanks
 

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

Jerry

"muddan madhu" wrote:

Try this one :
suppose u have values in A1 ,

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





On Sep 8, 7:26 am, Marianne
wrote:
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?

Deleting blanks
 

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

"Jerry W. Lewis" wrote in message
...
Removing the spaces will still leave a string. Try
=VALUE(SUBSTITUTE(A1,CHAR(160),""))

Jerry

"muddan madhu" wrote:

Try this one :
suppose u have values in A1 ,

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





On Sep 8, 7:26 am, Marianne
wrote:
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?

Deleting blanks
 

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

"Jerry W. Lewis" wrote:

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

Jerry

"muddan madhu" wrote:

Try this one :
suppose u have values in A1 ,

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





On Sep 8, 7:26 am, Marianne
wrote:
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?