Find:removing spaces

[Claus Massmann]

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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Claus Massmann" wrote:

> 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

[CLR]

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

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

> 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).
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Claus Massmann" wrote:
>
> > 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

[Ron Rosenfeld]

On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
<[email protected]> wrote:

>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

[Claus Massmann]

thanks, but still not working.

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

"Ron Rosenfeld" wrote:

> On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
> <[email protected]> wrote:
>
> >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
>

[CLR]

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



"Claus Massmann" wrote:

> thanks, but still not working.
>
> simply function like =sum() is returning 0...after applying trim/substitute
>
> "Ron Rosenfeld" wrote:
>
> > On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
> > <[email protected]> wrote:
> >
> > >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
> >

[Claus Massmann]

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

"CLR" wrote:

> 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
>
>
>
> "Claus Massmann" wrote:
>
> > thanks, but still not working.
> >
> > simply function like =sum() is returning 0...after applying trim/substitute
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
> > > <[email protected]> wrote:
> > >
> > > >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
> > >

[CLR]

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

Vaya con Dios,
Chuck, CABGx3



"Claus Massmann" wrote:

> 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
>
> "CLR" wrote:
>
> > 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
> >
> >
> >
> > "Claus Massmann" wrote:
> >
> > > thanks, but still not working.
> > >
> > > simply function like =sum() is returning 0...after applying trim/substitute
> > >
> > > "Ron Rosenfeld" wrote:
> > >
> > > > On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
> > > > <[email protected]> wrote:
> > > >
> > > > >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
> > > >

[Claus Massmann]

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?

"CLR" wrote:

> 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
>
>
>
> "Claus Massmann" wrote:
>
> > thanks, but still not working.
> >
> > simply function like =sum() is returning 0...after applying trim/substitute
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
> > > <[email protected]> wrote:
> > >
> > > >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
> > >

[CLR]

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





"Claus Massmann" wrote:

> 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?
>
> "CLR" wrote:
>
> > 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
> >
> >
> >
> > "Claus Massmann" wrote:
> >
> > > thanks, but still not working.
> > >
> > > simply function like =sum() is returning 0...after applying trim/substitute
> > >
> > > "Ron Rosenfeld" wrote:
> > >
> > > > On Wed, 15 Mar 2006 04:29:27 -0800, Claus Massmann
> > > > <[email protected]> wrote:
> > > >
> > > > >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
> > > >

[Ron Rosenfeld]

On Wed, 15 Mar 2006 07:49:05 -0800, Claus Massmann
<[email protected]> wrote:

>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:

>> > > 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

[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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"CLR" <[email protected]> wrote ...
> Very nice Tom.........I snagged your code for my own evil purposes elsewhere

> "Tom Ogilvy" wrote:
>
> > 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).
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Claus Massmann" wrote:
> >
> > > 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

[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


"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "CLR" <[email protected]> wrote ...
> > Very nice Tom.........I snagged your code for my own evil purposes
elsewhere
>
> > "Tom Ogilvy" wrote:
> >
> > > 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).
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Claus Massmann" wrote:
> > >
> > > > 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
>
>