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

=--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" <[email protected]> wrote in message
news:[email protected]...
>I have numbers in a column. But they aren't 'real' numbers (to Excel), they
> just look like numbers to the ***** 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?
>

Try

=--(TRIM(F2))

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

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

--

Regards,

Peo Sjoblom


"Pam" <[email protected]> wrote in message
news:[email protected]...
> I have numbers in a column. But they aren't 'real' numbers (to Excel),
they
> just look like numbers to the ***** 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?
>

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


"Pam" wrote:

> I have numbers in a column. But they aren't 'real' numbers (to Excel), they
> just look like numbers to the ***** 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?
>

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

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