Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pam
 
Posts: n/a
Default 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   Report Post  
Zack Barresse
 
Posts: n/a
Default 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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Ray A
 
Posts: n/a
Default 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   Report Post  
vezerid
 
Posts: n/a
Default 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   Report Post  
Pam
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert numbers from text format to number format merlin68 Excel Discussion (Misc queries) 7 June 20th 07 07:03 PM
Converting number or text to a Date Format samhain New Users to Excel 2 October 17th 05 02:28 PM
change custom format number to text joey Excel Discussion (Misc queries) 3 September 20th 05 09:35 PM
Format Number to Text Roni Excel Worksheet Functions 2 May 17th 05 03:17 PM
convert a number in Excel from numeric to text, i.e. "1" to "one" buenavisionpaul Excel Worksheet Functions 1 January 27th 05 07:30 PM


All times are GMT +1. The time now is 01:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"