Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
I enter numbers and they are stored as text | Excel Discussion (Misc queries) |