Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Thanks in advance
I have a row that that is sometimes blank or contains a number followed by a space and two letters eg. In cell A1 I have the value 456 CR In cell B1 I am using the formula =IF( ISBLANK(A1),0,(LEFT(A1, LEN(A1)-3)*1)) to strip the space and two letters from the cell. I multiply (LEFT(A1, LEN(A1)-3) by 1 to convert to a number from text. I am almost happy with the formula. I want the first part of the IF statement to evaluate to 0 if the field is blank but do not want to see the 0. If I use "" instead of 0 it is a text value again which causes number calculations to fail that use this cell. How can I put a blank number value in the IF statement above? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Can you use a formula to make a truly "blank" cell | Excel Worksheet Functions | |||
excel formula to enter "0" if cell blank | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
I need a formula: " If this cell is blank, then 0" | Excel Worksheet Functions |