Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default identify numbers and text differently in formula

I have a column of imported data. If units are whole, the number shows as
number, if it is whole and partial, it shows as text. I can work with the
text to divide it back up, but but if my formula looks at the cell and finds
a number, I need it to just multiply by the number, not give an error.
Example: if the formula finds "0/ 14.195379", it returns 14.195379
but
Example: if this formula finds a number in F16, it returns #VALUE instead
of the number itself. I need it to give the number it finds

=IF(F16=0,0,F16*LEFT(E16,FIND("/",E16)-1)+RIGHT(E16,LEN(E16)-FIND("/",E16)))

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default identify numbers and text differently in formula

Try:
=IF(ISNUMBER(F16),F16,IF(F16=0,0,F16*LEFT(E16,FIND ("/",E16)-1)+RIGHT(E16,LEN(E16)-FIND("/",E16))))

Dave

--
Brevity is the soul of wit.


"widman" wrote:

I have a column of imported data. If units are whole, the number shows as
number, if it is whole and partial, it shows as text. I can work with the
text to divide it back up, but but if my formula looks at the cell and finds
a number, I need it to just multiply by the number, not give an error.
Example: if the formula finds "0/ 14.195379", it returns 14.195379
but
Example: if this formula finds a number in F16, it returns #VALUE instead
of the number itself. I need it to give the number it finds

=IF(F16=0,0,F16*LEFT(E16,FIND("/",E16)-1)+RIGHT(E16,LEN(E16)-FIND("/",E16)))

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default identify numbers and text differently in formula

thanks. I kept playing with something like that, but was probably one )
short or something.

"Dave F" wrote:

Try:
=IF(ISNUMBER(F16),F16,IF(F16=0,0,F16*LEFT(E16,FIND ("/",E16)-1)+RIGHT(E16,LEN(E16)-FIND("/",E16))))

Dave

--
Brevity is the soul of wit.


"widman" wrote:

I have a column of imported data. If units are whole, the number shows as
number, if it is whole and partial, it shows as text. I can work with the
text to divide it back up, but but if my formula looks at the cell and finds
a number, I need it to just multiply by the number, not give an error.
Example: if the formula finds "0/ 14.195379", it returns 14.195379
but
Example: if this formula finds a number in F16, it returns #VALUE instead
of the number itself. I need it to give the number it finds

=IF(F16=0,0,F16*LEFT(E16,FIND("/",E16)-1)+RIGHT(E16,LEN(E16)-FIND("/",E16)))

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default identify numbers and text differently in formula

On Tue, 14 Nov 2006 07:27:02 -0800, widman
wrote:

I have a column of imported data. If units are whole, the number shows as
number, if it is whole and partial, it shows as text. I can work with the
text to divide it back up, but but if my formula looks at the cell and finds
a number, I need it to just multiply by the number, not give an error.
Example: if the formula finds "0/ 14.195379", it returns 14.195379
but
Example: if this formula finds a number in F16, it returns #VALUE instead
of the number itself. I need it to give the number it finds

=IF(F16=0,0,F16*LEFT(E16,FIND("/",E16)-1)+RIGHT(E16,LEN(E16)-FIND("/",E16)))


Perhaps:

=IF(ISERR(FIND("/",E16)),E16,MID(E16,FIND("/",E16)+1,255))*F16


--ron
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
extract number and use in formula from text & numbers in cell ivory_kitten Excel Worksheet Functions 3 July 14th 06 05:38 AM
Why are 1/2 my numbers imported as text and the rest as numbers? KBear Excel Discussion (Misc queries) 2 April 21st 06 01:40 PM
Numbers stored as text causes problem with VLOOKUP bpeltzer Excel Worksheet Functions 0 February 4th 06 09:07 PM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Display numbers only, no text formula Mira Excel Worksheet Functions 2 November 11th 04 02:54 AM


All times are GMT +1. The time now is 12:05 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"