text and numbers
 

hi your reply, =--TRIM(SUBSTITUTE(A1,CHAR(160),"")) was very useful. But how
can that be used to convert a range of cells?

"Ron Rosenfeld" wrote:

On Thu, 28 Oct 2004 06:35:08 -0700, "Sanford Lefkowitz"
wrote:

I was given a spreadsheet having a bunch of entries that appear to be
numbers, but when I try to do arithemetic with them, I get a #VALUE error.
I tried using the VALUE function to convert them to numbers, but the result
of this function is also a #VALUE error. Tracing the error shows it is trying
to evaluate
VALUE(" 1,291").
I tried using the TRIM function to remove leading blanks, but that did not
work.
I used the CODE function to find out what the characters were and find the
leading characters have a CODE value of 160 (a space I think should be 32).

How do I convert these characters to numbers?

Thanks
Sanford

That is a spacing character frequently used in HTML web pages.

This formula will convert it to a real number:

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




--ron

got the answer, just select the required cells and do a fill.

"aswin" wrote:

hi your reply, =--TRIM(SUBSTITUTE(A1,CHAR(160),"")) was very useful. But how
can that be used to convert a range of cells?

"Ron Rosenfeld" wrote:

On Thu, 28 Oct 2004 06:35:08 -0700, "Sanford Lefkowitz"
wrote:

I was given a spreadsheet having a bunch of entries that appear to be
numbers, but when I try to do arithemetic with them, I get a #VALUE error.
I tried using the VALUE function to convert them to numbers, but the result
of this function is also a #VALUE error. Tracing the error shows it is trying
to evaluate
VALUE(" 1,291").
I tried using the TRIM function to remove leading blanks, but that did not
work.
I used the CODE function to find out what the characters were and find the
leading characters have a CODE value of 160 (a space I think should be 32).

How do I convert these characters to numbers?

Thanks
Sanford

That is a spacing character frequently used in HTML web pages.

This formula will convert it to a real number:

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




--ron

Hi!

Select the range of cells in question.

Goto EditReplace
In the Find What box enter this code:

While holding down the ALT key, use the *numeric keypad* and type 0160

Using the QWERTY number keys won't work, you MUST use the numeric keypad

In the Replace With box: leave this empty, don't enter anything

Click Replace All

Biff

"aswin" wrote in message
...
hi your reply, =--TRIM(SUBSTITUTE(A1,CHAR(160),"")) was very useful. But
how
can that be used to convert a range of cells?

"Ron Rosenfeld" wrote:

On Thu, 28 Oct 2004 06:35:08 -0700, "Sanford Lefkowitz"
wrote:

I was given a spreadsheet having a bunch of entries that appear to be
numbers, but when I try to do arithemetic with them, I get a #VALUE
error.
I tried using the VALUE function to convert them to numbers, but the
result
of this function is also a #VALUE error. Tracing the error shows it is
trying
to evaluate
VALUE(" 1,291").
I tried using the TRIM function to remove leading blanks, but that did
not
work.
I used the CODE function to find out what the characters were and find
the
leading characters have a CODE value of 160 (a space I think should be
32).

How do I convert these characters to numbers?

Thanks
Sanford

That is a spacing character frequently used in HTML web pages.

This formula will convert it to a real number:

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




--ron