Null String Problems

%3CLINGO-SUB%20id%3D%22lingo-sub-2289586%22%20slang%3D%22en-US%22%3ENull%20String%20Problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2289586%22%20slang%3D%22en-US%22%3EI%20have%20one%20worksheet%20in%20a%20workbook%20that%20contains%20several%20cells%20with%20drop-down%20lists%2C%20separated%20by%20rows%20and%20columns.%20On%20a%20separate%20worksheet%20I%20have%20created%20a%20column%20equal%20to%20the%20values%20contained%20in%20the%20drop-down%20cells%20mentioned%20above.%20If%20the%20drop-down%20cell%20contains%20a%20blank%2C%20it%20gets%20copied%20as%20a%20Zero%20in%20the%20column%20I've%20made.%20I%20don't%20want%20Zeroes%2C%20so%20I%20have%20used%20a%20few%20different%20formulas%20to%20remove%20the%20Zero%2C%20all%20involving%20the%20null%20string%20%22%22.%20This%20makes%20my%20column%20have%20several%20cells%20appearing%20to%20be%20empty.%20Then%20in%20the%20column%20next%20to%20the%20copied%20data%2C%20with%20the%20null%20strings%2C%20I've%20used%20another%20array%20formula%20to%20eliminate%20the%20blank%20cells%20using%20iferror%2C%20index%20match%2C%20and%20istext.%20This%20is%20not%20working%20because%20the%20cells%20with%20the%20null%20string%20is%20not%20actually%20blank%20when%20it%20contains%20the%20null%20string%20%22%22.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20someone%20tell%20me%20how%20to%20take%20a%20column%20of%20cells%20that%20contains%20null%20strings%20and%20make%20a%20new%20column%20that%20eliminates%20the%20null%20strings%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%20I%20hope%20I%20am%20making%20sense%20here.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2289586%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-2291053%22%20slang%3D%22en-US%22%3ERe%3A%20Null%20String%20Problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036491%22%20target%3D%22_blank%22%3E%40powerwindow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormulas%20in%20Excel%20don't%20return%20blank%20as%20a%20value%2C%20they%20return%20zero%20instead.%20You%20may%20return%20empty%20string%20instead%20as%20you%20do%20and%20ignore%20in%20other%20formulas%20cells%20which%20contains%20empty%20string%20%22%22.%20Another%20option%20is%20to%20return%200%20and%20apply%20custom%20format%20to%20resulting%20cells%20show%20them%20as%20empty%20cells.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20463px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275544iFBB9C53712D55904%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor
I have one worksheet in a workbook that contains several cells with drop-down lists, separated by rows and columns. On a separate worksheet I have created a column equal to the values contained in the drop-down cells mentioned above. If the drop-down cell contains a blank, it gets copied as a Zero in the column I've made. I don't want Zeroes, so I have used a few different formulas to remove the Zero, all involving the null string "". This makes my column have several cells appearing to be empty. Then in the column next to the copied data, with the null strings, I've used another array formula to eliminate the blank cells using iferror, index match, and istext. This is not working because the cells with the null string is not actually blank when it contains the null string "".

Can someone tell me how to take a column of cells that contains null strings and make a new column that eliminates the null strings?

Thanks, I hope I am making sense here.
1 Reply

@powerwindow 

Formulas in Excel don't return blank as a value, they return zero instead. You may return empty string instead as you do and ignore in other formulas cells which contains empty string "". Another option is to return 0 and apply custom format to resulting cells show them as empty cells.

image.png