SOLVED
Home

IF cell is empty

%3CLINGO-SUB%20id%3D%22lingo-sub-354167%22%20slang%3D%22en-US%22%3EIF%20cell%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354167%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20upload%20a%20spreadsheet%20and%20all%20cells%20must%20have%20something%20in%20them.%20So%20I%20want%20to%20fill%20in%20all%20blank%20cells%20in%20column%20A%20with%20%22A%22%2C%20column%20B%20with%20%22B%22%2C%20etc.%20but%20if%20a%20cell%20has%20data%20I%20want%20to%20leave%20it%20alone.%3C%2FP%3E%3CP%3EI%20am%20using%20this%20formula%2C%20but%20it's%20returning%20zero%20in%20all%20cells.%3A%3CBR%20%2F%3E%3DIF(ISBLANK(A1)%2C%22A%22%2CA1)%3C%2FP%3E%3CP%3EI%20have%20allowed%20circular%20iteration%20so%20I%20am%20not%20getting%20an%20error%2C%20just%20a%200%20all%20the%20time.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20i%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354167%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354718%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20cell%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354718%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20wasn't%20calculating%20because%20at%20some%20point%20I%20had%20changed%20these%20to%20text%20format%20cells.%20(I%20did%20that%20because%20some%20of%20the%20cells%20have%20dates%20and%20the%20calculation%20was%20messing%20up%20the%20dates.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20think%20I%20was%20getting%20zeros%20because%20it%20would%20only%20return%20a%20letter%20('A'%20for%20example)%20if%20the%20cell%20was%20empty%2C%20but%20the%20cell%20had%20a%20formula%20in%20it%20so%20it%20wasn't%20empty%3F%20I%20suppose%20you%20would%20need%20to%20have%20another%20blank%20column%20%22B%22%20that%20performs%20the%20calculation%20on%20%22A%22%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20F5%20trick%20works%20like%20a%20charm.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354715%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20cell%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354715%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20works%20great!%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354299%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20cell%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354299%22%20slang%3D%22en-US%22%3E%3CP%3EInstead%20of%20a%20formula%2C%20use%20the%20Goto%2Fspecial%20function%20to%20select%20the%20blanks%20and%20then%20type%20the%20A%2C%20B%2C%20C%2C%20etc.%20Select%20Column%20A%2C%20then%20hit%20F5%2C%20then%20click%20the%20'Special...'%20button%2C%20choose%20the%20%22Blanks%22%20option%2C%20then%20'OK'.%20This%20highlights%20the%20blank%20cells%20only%20and%20all%20you%20have%20to%20do%20is%20type%20A%20and%20Ctrl%2BEnter.%20Repeat%20for%20the%20next%20columns.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354281%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20cell%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354281%22%20slang%3D%22en-US%22%3E%3CP%3EWell%20I%20don't%20know%20what%20I've%20done%20now.%20It's%20not%20calculating%20any%20formulas%20at%20all.%20It%20just%20shows%20the%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20this%20in%20the%20cell%20B3%3A%26nbsp%3B%3DIF(ISBLANK(A3)%2C%22A%22%2CA3)%20and%20that%20is%20what%20it%20says%2C%20no%20calculation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354227%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20cell%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354227%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20unsure%20about%20what%20you%20are%20doing%20wrong.%20Your%20formula%20works%20for%20me.%20If%20having%20the%20column%20letter%20replacing%20the%20cells%20is%20not%20important%20but%20just%20having%20something%20in%20each%20cell%20you%20can%20try%20to%20simply%20use%20the%20replace%20function.%20To%20do%20this%20simply%20keep%20the%20%22Find%20what%22%20box%20empty%20and%20insert%20whatever%20you%20want%20in%20the%20%22Replace%20with%22%20box.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
JerryTDS
New Contributor

I have to upload a spreadsheet and all cells must have something in them. So I want to fill in all blank cells in column A with "A", column B with "B", etc. but if a cell has data I want to leave it alone.

I am using this formula, but it's returning zero in all cells.:
=IF(ISBLANK(A1),"A",A1)

I have allowed circular iteration so I am not getting an error, just a 0 all the time. 

What am i doing wrong?

5 Replies

I am unsure about what you are doing wrong. Your formula works for me. If having the column letter replacing the cells is not important but just having something in each cell you can try to simply use the replace function. To do this simply keep the "Find what" box empty and insert whatever you want in the "Replace with" box. 

Well I don't know what I've done now. It's not calculating any formulas at all. It just shows the formula. 

I've got this in the cell B3: =IF(ISBLANK(A3),"A",A3) and that is what it says, no calculation. 

 

 

 

Solution

Instead of a formula, use the Goto/special function to select the blanks and then type the A, B, C, etc. Select Column A, then hit F5, then click the 'Special...' button, choose the "Blanks" option, then 'OK'. This highlights the blank cells only and all you have to do is type A and Ctrl+Enter. Repeat for the next columns. 

That works great! Thank you!

 

It wasn't calculating because at some point I had changed these to text format cells. (I did that because some of the cells have dates and the calculation was messing up the dates.)

 

Also, I think I was getting zeros because it would only return a letter ('A' for example) if the cell was empty, but the cell had a formula in it so it wasn't empty? I suppose you would need to have another blank column "B" that performs the calculation on "A" column. 

 

But the F5 trick works like a charm.

 

 

Related Conversations
Dont see any Contact in Teams
nicb in Microsoft Teams on
2 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
not able to type number and text in same cell?
Deleted in Excel on
2 Replies
Intranet blank white right space
slavenqx in SharePoint on
11 Replies
Excel If Functions
Mfouad2255 in Excel on
10 Replies