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

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

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

On Wed, 15 Mar 2006 04:29:27 -0800, 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

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

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

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

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

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

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

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

On Wed, 15 Mar 2006 07:49:05 -0800, 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?

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

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

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