Change number (in text format) to numeric format

P

Pam

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

Zack Barresse

=--A1

Where A1 houses your textual numbers. Also, ASAP Utilities has a good
feature for converting textual numbers into numerical numbers.

HTH
 
P

Peo Sjoblom

Try

=--(TRIM(F2))

if that doesn't work then you have html trailing spaces

=--(TRIM(SUBSTITUTE(F1,CHAR(160),"")))
 
R

Ray A

one way;
In a blank cell enter the number 1. Now copy and highlight the range you
want to convert to numbers. Edit>paste special> multiply
hth
 
V

vezerid

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
 
P

Pam

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