Removing blank cells with a formula

An example would be:

Cell A1 = Text 1

Cell A3 = Text 2

Cell A5 = Text 3

Cell A7 = Text 4

 

I would like to insert a formula in cell B1 and down that would result in:

Cell B1 = Text 1

Cell B2 = Text 2

Cell B3 = Text 3

Cell B4 = Text 4

 

Basically it removes any blank cells in column A

 

Thanks

Answer
Answer

Thanks, it works on the example, but when I apply it to my actual range it doesn't work.  The formula is actually in J2:J256.  The range is in I2:I256.

 

Thanks a lot


I can't tell who you're replying to (MS browser issue).

If you're replying to me then enter this array formula in J2 and copy down as needed (until you get blanks):

=IF(ROWS(J$2:J2)>COUNTA(I:I),"",INDEX(I:I,SMALL(IF(I$2:I$256<>"",ROW(I$2:I$256)),ROWS(J$2:J2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 --
Biff
Microsoft Excel MVP

Biff
Microsoft Excel MVP

KISS - Keep It Simple Stupid

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated January 10, 2021 Views 34,251 Applies to: