| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract number and use in formula from text & numbers in cell | Excel Worksheet Functions | |||
| Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
| Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Display numbers only, no text formula | Excel Worksheet Functions | |||
Linear Mode
