Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blanks non blanks in filter | Excel Discussion (Misc queries) | |||
Blanks non blanks in filter | Excel Discussion (Misc queries) | |||
Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |