Remove Blank Cells From a Column Range

[ststern45]

Hi everyone,

I have random letters from a through z in cell range DB12 through DS12.
Some of these cells contain blanks and no repeat letters
I'm looking for a formula that will place the letters from DB12 through DS12 starting in cell DW12.

Thank you in advance!!

 

[Joe4]

I'm looking for a formula that will place the letters from DB12 through DS12 starting in cell DW12.

That is a very incomplete description.
- Starting in DW12 and then going in which direction?
- Do you want the blank spaces removed?
- Do you want them sorted in any specific order?

All these questions can usually be answered pretty quickly with a visual example of your data and expected results.

 

[ststern45]

That is a very incomplete description.
- Starting in DW12 and then going in which direction? DW12 through EN12
- Do you want the blank spaces removed? Yes
- Do you want them sorted in any specific order? No order

All these questions can usually be answered pretty quickly with a visual example of your data and expected results.

Thank you!!

 

[ststern45]

Since all the cells contain text is there a workaround similar to this formula below?
If I'm not mistaken the aggregate only will calculate values not text.
=IFERROR(AGGREGATE(15,6,$DB12:$DS12/($DB12:$DS12<>"")/(ISNA(MATCH($DB12:$DS12,DV12:$DV12,0))),1),"")
Thanks!!

 

[ststern45]

Found the formula here at MrExcel:

=IFERROR(INDEX($DB12:$DS12, SMALL(IF($DB12:$DS12<>"", COLUMN($DB12:$DS12)-COLUMN($DB12)+1, ""), COLUMNS($DW12:DW12))), "")
Ctrl + Shift + Enter

 

[Peter_SSs]

If you are interested, you could also use this one that does not require the Ctrl+Shift+Enter confirmation (can be useful in case formulas ever need to be amended where the C+S+E might be forgotten)

=IFERROR(INDEX($DB12:$DS12,AGGREGATE(15,6,(COLUMN($DB12:$DS12)-COLUMN($DB12)+1)/($DB12:$DS12<>""),COLUMNS($DW12:DW12))),"")

 

[ststern45]

If you are interested, you could also use this one that does not require the Ctrl+Shift+Enter confirmation (can be useful in case formulas ever need to be amended where the C+S+E might be forgotten)

=IFERROR(INDEX($DB12:$DS12,AGGREGATE(15,6,(COLUMN($DB12:$DS12)-COLUMN($DB12)+1)/($DB12:$DS12<>""),COLUMNS($DW12:DW12))),"")

Thank you Peter.
I have not checked your formla.
Will this work with text/ alpha characters also?

 

[Peter_SSs]

Will this work with text/ alpha characters also?

Yes.