Column of Formula generated numbers with some " ", need new column without " "

[Maroonlite]

Hello. I've been all over the lovely forums for 2 days trying to solve what intuitively seems like an easy problem to solve, but apparently not.

In short, I need to remove empty cells from a column where the values are dynamic.

I have a column (CY, in this case) of numbers, with " " interspersed, that are generated by an "IF" formula in other cells. I only mention this because it means that the "empty" cells are not actually blank and the position of the cells with value or without value are not static.

I simply want to put a formula next to Column CY that copies the same information from CY but without the " ", so I end up with a column of nothing but numbers with no breaks/""/empty cells.

Worksheet is attached, but for quick example:


Excel forum example.jpg
Column CY is generated by formulas, and column CZ is what I'd like it to automatically generate based on CY data.


The range of cells in question is at CY133:CY146, and the new formula, should I be so lucky as to find a solution, will be placed in CX133 and copied down to CX146, ideally. Open to any solution though.

I've tried making the IF statement generate 0 as a value instead of "". Also tried TRUE/FALSE. ISNUMBER wont work because the empty cells still return FALSE as they are formula generated. LOOKUP with COUNTIF got me close but wasn't working because it only returned UNIQUE values, rather than simply returning every positive number. INDEX/MATCH was close but not good enough because I'm not trying to match anything, rather, simply return the value if it's a positive number at all.

[WideBoyDixon]

Formula in CZ133:

Please Login or Register  to view this content.

User Ctrl+Shift+Enter to create an array formula then copy down to CZ146.

WBD

[wk9128]

TRY CZ133 cell array formula, copy drag down

[Maroonlite]

They BOTH did the trick!!! WideBoyDixon The first one worked best because there were no zeros at the end of the newly generated list. The solution looks quite similar to one I tried except it didnt include the ISNUMBER function. Wow! Im so happy right now.

WK9128 The second solution will also work, it just also generates 0's after the positive values/.

Both great solutions and speedy replies!!!!

thank you!