Removing blank characters in a cell

  • Thread starter Thread starter goonly
  • Start date Start date
G

goonly

Hey,

I was wondering how to remove blank characters in a cell that are typed
in after the number? For example:

I copied some financial spreadsheets from a web page into excel and 80%
of the numbers have the format ##,###__ (where # is an actual number
and _ is a spacebar key). Thus, excel recognizes this as text and not
as a number. It does not work to change the cell format to "number"
either as these blank characters associated with the number are
screwing it up. I would use the text to column fixed width, but not
every cell has these blank characters at the end of it. Is there an
excel formula that would allow me to delete blank characters in a group
of cells?

Thanks,
Goonly
 
Hi Goonly

Try
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(12),""),CHAR(160),"")*1

to remove both spaces and / or the non-breaking space CHAR(160) which
sometimes occurs on web pages
 
Try edit>replace, in the find what box type 0160 on the numpad while holding
down the alt key,
replace with nothing
 
No idea why that worked, but both of your methods worked perfectly!
Thanks for speedy reply and saving me many hours. In the process of
trying to figure this out on my own i learned about (trim, replace,
substitute, clean, replace, and many others that did not work). Thanks
again!
 
Roger Govier said:
Try
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(12),""),CHAR(160),"")*1

to remove both spaces and / or the non-breaking space CHAR(160) which
sometimes occurs on web pages
Click to expand...

CHAR(12) is form feed. CHAR(32) is line feed. However, leading and trailing
ASCII spaces are irrelevant. The formula =" 2 "+" 2 " returns 4.
 
Hi

It seems you have solved your problem, but for future:
enter 1 into some free cell (or select a cell with NUMBER 1 in it) and copy
it.
Select the range with your must-be numbers, change the format to General or
Numeric, and then PasteSpecial.Multiply. All values are now numbers, and
there aren't any spaces left.
 
Hi Harlan

You are quite right, I had meant to type CHAR(32) not (12), but I think you
meant CHAR(32) is Space, not Line Feed.
However, leading and trailing
ASCII spaces are irrelevant. The formula =" 2 "+" 2 " returns 4.
Click to expand...

I'm not sure about this. On my system, (XP and XL2002), summing values with
trailing spaces gives 0.
 
Hi Arvi

Whilst I agree that this method works with spaces, I have never been able to
get it to work if the trailing space is the non line breaking space,
CHAR(160).
Often when copying from the web, it is the CHAR(160) that is the problem.

--
Regards
Roger Govier
Arvi Laanemets said:
Hi

It seems you have solved your problem, but for future:
enter 1 into some free cell (or select a cell with NUMBER 1 in it) and copy
it.
Select the range with your must-be numbers, change the format to General or
Numeric, and then PasteSpecial.Multiply. All values are now numbers, and
there aren't any spaces left.
Click to expand...
 
Hi


Roger Govier said:
Hi Arvi

Whilst I agree that this method works with spaces, I have never been able to
get it to work if the trailing space is the non line breaking space,
CHAR(160).
Often when copying from the web, it is the CHAR(160) that is the problem.
Click to expand...

Maybe you are right, I don't often copy data from web. But those couple of
times I had (Win98SE: IE6.0, Excel2000), I used PasteSPecial.Text and then
Data.TextToColumns with space as delimiter - all data were right format
after that usually, and during this session with any further PasteSpecial
the TextToColumns was made automatically.
 
You are quite right, I had meant to type CHAR(32) not (12), but I think you
meant CHAR(32) is Space, not Line Feed.
Oops!


I'm not sure about this. On my system, (XP and XL2002), summing values with
trailing spaces gives 0.
Click to expand...

What does =VALUE(" 2 ") return on your system? On both the ones I use frequently
(work running XL97 SR-2, home running XL2K SP-3, both using standard US locale
settings), this function call returns 2.
 
Hi Harlan
What does =VALUE(" 2 ") return on your system? On both the ones I use frequently
(work running XL97 SR-2, home running XL2K SP-3, both using standard US locale
settings), this function call returns 2.
Click to expand...

This does indeed return 2 on my system as well

But, If I enter "' 2 " in a series of cells to force a text entry with space
either side of the value, and then sum those cells, I get a result of 0
 
...
...
But, If I enter "' 2 " in a series of cells to force a text entry with space
either side of the value, and then sum those cells, I get a result of 0
Click to expand...

Is this a typo? Do you really have a single quote inside the double-quoted
string?

If I have the following in A1:A3 (numeric constants on either side of a formula
returning text),

1
=" 2 "
3

then =A1+A2+A3 returns 6 and =SUM(A1:A3) returns 4 on my system.


Anyone else want to test this and report results??
 
Harlan Grove wrote:
of 0
Is this a typo? Do you really have a single quote inside the
double-quoted string?

If I have the following in A1:A3 (numeric constants on either side of
a formula returning text),

1
=" 2 "
3

then =A1+A2+A3 returns 6 and =SUM(A1:A3) returns 4 on my system.

Anyone else want to test this and report results??
Click to expand...

Hi Harlan
same result on my system (Windows 2000 SP 3, Excel 2003, German
version)
that is really strange.
Frank
 
Hi Harlan

Not a Typo. I was using the double quotes merely to highlight what I had
entered to the cells.
I used the single quote followed by a space followed by a 2 followed by a
space to get the entries into the cells.
I get the same result if I do the same as yourself with cells A1:A3, and use
=" 2 " to get the spaces intot the cell.
i.e. 4 as the answer to the SUM formula and 6 as the answer to =A1+A2+A3

My Transition formulas and Transition entry are both unchecked.
 
Not a Typo. I was using the double quotes merely to highlight what I had
entered to the cells.
Click to expand...
...

Note that ' when the very first thing entered in a cell forces text entry, but
that apostrophe/single quote isn't included in either the cell's value or
formula. Putting it inside the double quotes makes ="' 2 " fundamentally
different than entering

' 2

into a cell directly. The presence of the single quote in the cell's value is
what prevents it from being converted to a number.
I get the same result if I do the same as yourself with cells A1:A3, and use
=" 2 " to get the spaces intot the cell.
i.e. 4 as the answer to the SUM formula and 6 as the answer to =A1+A2+A3

My Transition formulas and Transition entry are both unchecked.
Click to expand...
...

Then even on your system spaces are ignored by operators. With =" 2 " in A2,
what's returned by =VALUE(A2)?
 
Then even on your system spaces are ignored by operators. With =" 2 " in
A2,
what's returned by =VALUE(A2)?
Click to expand...

2
Which is what I said a few post ago.

But, SUM(A1:A3) will only give 4.
Spaces, whether entered with the single quote or the double quote will not
calculate as a number.
The array formula
{=SUM(VALUE(A1:A3))} does of course evaluate to 6 as one is forcing the
evaluation, the same as your VALUE(A2)
but without that evaluation, spaces always do prevent the correct
calculation.
 
...
...
But, SUM(A1:A3) will only give 4.
Click to expand...

As it should. A2 is text, and is correctly discarded by SUM.
Spaces, whether entered with the single quote or the double quote will not
calculate as a number.
Click to expand...

In functions that ignore text.
The array formula
{=SUM(VALUE(A1:A3))} does of course evaluate to 6 as one is forcing the
evaluation, the same as your VALUE(A2)
but without that evaluation, spaces always do prevent the correct
calculation.
Click to expand...

Again, as they should.

Since VALUE() returns errors when fed nonnumeric text, there's never any point
to using it instead of double unary minuses. Since this creates an array, use
SUMPRODUCT to sum it rather than SUM.

=SUMPRODUCT(--A1:A3)

will always evaluate as expected whenever possible.

But let's go back to the top.

CHAR(12) is form feed. CHAR(32) is line feed. However, leading and trailing
ASCII spaces are irrelevant. The formula =" 2 "+" 2 " returns 4.
Click to expand...

If your original formula were just

=SUBSTITUTE(A1,CHAR(160),"")*1

you'd find that it produces a number unless there are spaces *between* numerals.
However, my response was limited to leading and trailing spaces. Also, using
functions over a range, in particular SUM, will give different results than
using operators, in particular +. In other words, it's intended functionality
that

=SUM(A1:A3)

doesn't always return the same thing as

=A1+A2+A3
 
Back
Top