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)))

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)))

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)))

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