A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I put a blank value in a cell not using "" in a formula



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 01:09 AM posted to microsoft.public.excel.worksheet.functions
Gunnatrada
external usenet poster
 
Posts: 1
Default 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  
Old June 2nd, 2010, 01:19 AM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old June 2nd, 2010, 03:25 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.