removing spaces

[Guest]

i have copied data from a website and would like to apply certain formulas to
it, but the data has 2 blank spaces after the last digit and therefore
formulas are not working.

I've tried trim, clean and neither are working.

Can anyone help?
Thanks

 

[Guest]

Try running this macro:

Sub CleanData()
Cells.Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

these cell probably contain the non breaking space character (char 160).

 

[Guest]

Very nice Tom.........I snagged your code for my own evil purposes elsewhere
<g>
Thanks,

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

i have copied data from a website and would like to apply certain formulas to
it, but the data has 2 blank spaces after the last digit and therefore
formulas are not working.

I've tried trim, clean and neither are working.

Can anyone help?
Thanks

There is most likely a no-break space in the string (CHAR(160)).

So try:

=TRIM(SUBSTITUTE(A1,CHAR(160),""))

You may need to precede this with a double unary if this is numeric data, as
the formula returns text.

=--TRIM(SUBSTITUTE(A5,CHAR(160),""))
--ron

 

[Guest]

thanks, but still not working.

simply function like =sum() is returning 0...after applying trim/substitute

 

[Guest]

I would use Chip Pearsons fine Add-in called CellView, to actually see what
characters are in the cell......then you can deal with them.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

thanks.
Results: the cell contains 250.00
charc - 2 5 0 . 0 . 0 space space
dec - 050 053 048 046 048 048 160 160.

How do I get rid of the 160?

Thanks
Claus
characters

 

[Guest]

Did you try Tom's code?..........his stuff usually works pretty good.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

After checking the contents of the cell I got rid of the 160 using the,
=TRIM(SUBSTITUTE(A1,CHAR(160),"")), formula.

Still the sum function adds the cells up to 0.00.

Comments?

 

[Guest]

It sounds like your "numbers" are not really numbers, but rather they are
"TEXT" that just look like numbers and must be re-formatted to be real
numbers.

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

After checking the contents of the cell I got rid of the 160 using the,
=TRIM(SUBSTITUTE(A1,CHAR(160),"")), formula.

Still the sum function adds the cells up to 0.00.

Comments?

You ignored part of my post (repeated below), so did not convert the text
result to a number:


--ron

 

[David McRitchie]

Hi Chuck,

You might find that the TrimALL macro serves a more general purpose
in fixing up such data, and may help with some types of reentry problems
where you change the cell format before running the macro -- the TrimALL
macro will only work on text cells (a cell with 160 non-breaking space code is text)..
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

In any case, I prefer macros to work on a selection (selection.) instead
of on all cells (cells.) as being more generic as it is simple to select all
cells before running a macro, so you can use the same macro for both.

 

[CLR]

Thanks David......at first glance the TRIMall macro looks great, but my
tired old eyes are about ready to shut for tonight. I'll give it a study
tomorrow at work.....that's where I have to do that sort of
conversion.....getting garbage downloads from the Man-man system and trying
to make heads or tails out of them......I'm on MIS's s**t list and every
time they send me something, it's in a different format.....Im getting
pretty good at unscrambling them, but every little goodie-tidbit makes the
job easier.

Thanks again,
Vaya con Dios,
Chuck, CABGx3