How do I put a blank value in a cell not using "" in a formula
in [Functions]
|
Prev: To protect all worksheet simultaneously in a excel workbook
Next: Case sensitivity of functions in Excel
From: Gunnatrada on 1 Jun 2010 20:09 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?
From: Gord Dibben on 1 Jun 2010 20:19 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 <Gunnatrada(a)discussions.microsoft.com> 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?
From: T. Valko on 1 Jun 2010 22:25
Another option is to use a custom number format that suppresses the 0 display. Select the cell(s) in question Goto the menu Format>Cells>Custom 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" <Gunnatrada(a)discussions.microsoft.com> wrote in message news:FD29BD2D-70A5-47CD-9538-6B485C7A069D(a)microsoft.com... > 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? |