remove blanks using formula

  • Thread starter Thread starter Gotroots
  • Start date Start date
G

Gotroots

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou
 
Assume values in ColA are actual valuesIn cell B1 enter the below formula and
copy down as required; Please note that this is an array formula. An array
formula can perform multiple calculations and then return either a single
result or multiple results. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=IF(COUNTIF($A$1:$A$1000,"?*")<ROW(A1),"",INDEX(A$1:A$1000,
SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$1000)),ROW(A1))))
 
Select the A column cell and press Cntrl+G click on Special select Blanks
Option Button and give ok. Now the blank cells of A Column will be selected.
Now do right click and select Delete and select Entire Row Option button.

In keyboard
Place the cursor in A Column Cell and press the following keyboard buttons
F5>>Alt+S>>Alt+K>Enter>Right Click>D>Alt+R

Remember to Click Yes, if this post helps!
 
Jacob

Your solution worked wonderfully. Just interested to know what you meant by;
Assume values in ColA are actual values
Click to expand...

"A" are values returned by a formula and this had not any influence on your
formula working.

Thanks again
 
Oops.. Initially I created a solution whch works on actual values (not
formulas); later I changed it to consider formulas too...but forgot to retype
the text...

I used COUNTA() which was later changed to COUNTIF()
 
No worries


Jacob Skaria said:
Oops.. Initially I created a solution whch works on actual values (not
formulas); later I changed it to consider formulas too...but forgot to retype
the text...

I used COUNTA() which was later changed to COUNTIF()
Click to expand...
 
Back
Top