If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I put a blank value in a cell not using "" in a formula
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? |
#2
|
|||
|
|||
How do I put a blank value in a cell not using "" in a formula
You can have a 0 or a "" but not both.
Why not use Conditional Formatting to color the font white if value = 0 Gord Dibben MS Excel MVP On Tue, 1 Jun 2010 17:09:01 -0700, Gunnatrada wrote: 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? |
#3
|
|||
|
|||
How do I put a blank value in a cell not using "" in a formula
Another option is to use a custom number format that suppresses the 0
display. Select the cell(s) in question Goto the menu FormatCellsCustom In the Type box enter: General;General; OK out The cell will still contain the numeric 0 you just won't see it. -- Biff Microsoft Excel MVP "Gunnatrada" wrote in message ... 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 | |
Display Modes | |
|
|