Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Change number (in text format) to numeric format
I have numbers in a column. But they aren't 'real' numbers (to Excel), they
just look like numbers to the naked eye. So, of course, I can't do any numeric functions on these 'numbers'. I went to help and I see the =VALUE function which looks like that would be my answer. But, when I type: =VALUE(F2) I get #VALUE! instead of the number I want-- 0.13 Any ideas? |
#2
|
|||
|
|||
Change number (in text format) to numeric format
=--A1
Where A1 houses your textual numbers. Also, ASAP Utilities has a good feature for converting textual numbers into numerical numbers. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Pam" wrote in message ... I have numbers in a column. But they aren't 'real' numbers (to Excel), they just look like numbers to the naked eye. So, of course, I can't do any numeric functions on these 'numbers'. I went to help and I see the =VALUE function which looks like that would be my answer. But, when I type: =VALUE(F2) I get #VALUE! instead of the number I want-- 0.13 Any ideas? |
#3
|
|||
|
|||
Change number (in text format) to numeric format
Try
=--(TRIM(F2)) if that doesn't work then you have html trailing spaces =--(TRIM(SUBSTITUTE(F1,CHAR(160),""))) -- Regards, Peo Sjoblom "Pam" wrote in message ... I have numbers in a column. But they aren't 'real' numbers (to Excel), they just look like numbers to the naked eye. So, of course, I can't do any numeric functions on these 'numbers'. I went to help and I see the =VALUE function which looks like that would be my answer. But, when I type: =VALUE(F2) I get #VALUE! instead of the number I want-- 0.13 Any ideas? |
#4
|
|||
|
|||
Change number (in text format) to numeric format
one way;
In a blank cell enter the number 1. Now copy and highlight the range you want to convert to numbers. Editpaste special multiply hth "Pam" wrote: I have numbers in a column. But they aren't 'real' numbers (to Excel), they just look like numbers to the naked eye. So, of course, I can't do any numeric functions on these 'numbers'. I went to help and I see the =VALUE function which looks like that would be my answer. But, when I type: =VALUE(F2) I get #VALUE! instead of the number I want-- 0.13 Any ideas? |
#5
|
|||
|
|||
Change number (in text format) to numeric format
Pam,
it seems that you got your numbers from some import and there exist non-printable characters in the cell. You will have to get rid of them before you apply the VALUE() technique. First, you must understand the pattern using text functions. Use =LEN(F2), to see if the cell contains more characters than appear at first. Most likely there will always be the same characters before or after the number. If there always seem to be the same number of invisible characters then you have to find if these numbers are placed before or after the number. Use something like =MID(F2, 2, 1) to see whether the 2nd character is the one you see. Now, once you determine where the extra characters are and how many they are, you can leave the number part only. If the extra characters are before the number use =MID(F2, N+1, LEN(F2)) to extract. If they are after the number use =LEFT(F2, LEN(F2)-N), in both cases N is the number of extra characters. Use VALUE() on the extracted portions. Write back if (unlikely), you have variable number of extra characters. HTH Kostis Vezerides |
#6
|
|||
|
|||
Change number (in text format) to numeric format
Thanks for all your suggestions, but none of them helped in this particular
file. :( However, I was able to do a VLOOKUP and that worked. (The VLOOKUP table had the 'number' as text in the first column and then the 'real' number was in the 2nd column.) That worked. "Pam" wrote: I have numbers in a column. But they aren't 'real' numbers (to Excel), they just look like numbers to the naked eye. So, of course, I can't do any numeric functions on these 'numbers'. I went to help and I see the =VALUE function which looks like that would be my answer. But, when I type: =VALUE(F2) I get #VALUE! instead of the number I want-- 0.13 Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
Converting number or text to a Date Format | New Users to Excel | |||
change custom format number to text | Excel Discussion (Misc queries) | |||
Format Number to Text | Excel Worksheet Functions | |||
convert a number in Excel from numeric to text, i.e. "1" to "one" | Excel Worksheet Functions |