Home

IF(); or IFNONTEXT()l or ??

%3CLINGO-SUB%20id%3D%22lingo-sub-796318%22%20slang%3D%22en-US%22%3EIF()%3B%20or%20IFNONTEXT()l%20or%20%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796318%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20downloading%20bank%20account%20activity%20the%20%22Reference%22%20column%20has%20blanks%20or%20check%20numbers.%20I'm%20trying%20to%20scan%20that%20column%20and%20where%20the%20blank%20cells%20are%20in%20that%20column%20insert%20%22Debit%22.%20And%20if%20the%20cell%20is%20NOT%20EMPTY%20(ie.%20there's%20a%20check%20number)%20do%20nothing%20(leave%20the%20check%20number%20and%20go%20on%20to%20the%20next%20cell%20in%20the%20column.)%20Please%20help%20me%20devise%20a%20formula.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-796318%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796454%22%20slang%3D%22en-US%22%3ERe%3A%20IF()%3B%20or%20IFNONTEXT()l%20or%20%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796454%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389563%22%20target%3D%22_blank%22%3E%40Moonlander%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20below%20formula%20in%20the%20column%20next%20to%20'Reference'%20column.%20Sample%20file%20is%20also%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(ISBLANK(A2)%2C%22Debit%22%2CA2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20will%20help%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797065%22%20slang%3D%22en-US%22%3ERe%3A%20IF()%3B%20or%20IFNONTEXT()l%20or%20%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389563%22%20target%3D%22_blank%22%3E%40Moonlander%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20select%20entire%20range%20for%20that%20column%2C%20Ctrl%2BH%20and%20here%20Replace%20all%20blank%20cells%20on%20Debit%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126369iA3980C57D73D66EC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797564%22%20slang%3D%22en-US%22%3ERe%3A%20IF()%3B%20or%20IFNONTEXT()l%20or%20%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126403i2E0C851E82761295%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%20Here%20is%20a%20screenshot%20of%20data.%20In%20this%20case%20the%20check%20number%20is%20the%20first%20data%20in%20the%20reference%20column.%20I'm%20missing%20something.%20I%20don't%20understand%20taking%20the%20formula%20down%20across%20the%20new%20column.%20Usually%20I%20can%20copy%20a%20formula%2C%20select%20all%20of%20the%20cells%20below%20that%20formula%20populated%20cell%2C%20and%20it%20adjusts%20and%20applies%20to%20all%20selected%20cells.%20But%20not%20in%20this%20case.%20What%20am%20I%20missing%2C%20please%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126404iEFE42D88C89FD5EE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797669%22%20slang%3D%22en-US%22%3ERe%3A%20IF()%3B%20or%20IFNONTEXT()l%20or%20%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389563%22%20target%3D%22_blank%22%3E%40Moonlander%3C%2FA%3E%26nbsp%3BTry%20to%20'Save%20as'%20the%20file%20in%20excel%20format%20(.xlsx)%20as%20suggested%20in%20the%20warning%20message%20and%20then%20copy%20the%20formula%20again.%20If%20you%20also%20look%20at%20cell%20B3%20(in%20your%20screenshot)%20it's%20showing%20the%20same%20chkRef%20as%20appearing%20in%20B2%20and%20it%20may%20be%20because%20of%20the%20format.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20you%20may%20share%20the%20file%20for%20modification%20at%20my%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797687%22%20slang%3D%22en-US%22%3ERe%3A%20IF()%3B%20or%20IFNONTEXT()l%20or%20%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389563%22%20target%3D%22_blank%22%3E%40Moonlander%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20enter%20the%20formula%20in%20B2%2C%20select%20your%20column%20starting%20from%20B2%20and%20Ctrl%2BD%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Moonlander
New Contributor

In downloading bank account activity the "Reference" column has blanks or check numbers. I'm trying to scan that column and where the blank cells are in that column insert "Debit". And if the cell is NOT EMPTY (ie. there's a check number) do nothing (leave the check number and go on to the next cell in the column.) Please help me devise a formula. Thanks!

5 Replies

Hi @Moonlander 

 

Try below formula in the column next to 'Reference' column. Sample file is also attached.

 

=IF(ISBLANK(A2),"Debit",A2)

 

Hope it will help

Tauqeer

@Moonlander 

You may select entire range for that column, Ctrl+H and here Replace all blank cells on Debit

clipboard_image_0.png

 

@tauqeeracma 

clipboard_image_0.png

Thank you so much. Here is a screenshot of data. In this case the check number is the first data in the reference column. I'm missing something. I don't understand taking the formula down across the new column. Usually I can copy a formula, select all of the cells below that formula populated cell, and it adjusts and applies to all selected cells. But not in this case. What am I missing, please?

clipboard_image_1.png

@Moonlander Try to 'Save as' the file in excel format (.xlsx) as suggested in the warning message and then copy the formula again. If you also look at cell B3 (in your screenshot) it's showing the same chkRef as appearing in B2 and it may be because of the format. 

 

Alternatively you may share the file for modification at my end.

 

Tauqeer

@Moonlander 

You may enter the formula in B2, select your column starting from B2 and Ctrl+D

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies