CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Rus Marcoderin
Rus Marcoderin

Reputation: 67

How to remove blank rows in Google sheets

I was wondering if there was a way to quickly remove blank rows using a formula like COUNTA?

I have something like this with blanks:

Chocolates
Bounty CHOC001
Twix CHOC002


Snickers CHOC003

Mars CHOC004

Kinder Bueno CHOC005

Dairymilk CHOC006

Wispa CHOC007

Now I want it to be like this with blanks removed:

Chocolates
Bounty CHOC001
Twix CHOC002
Snickers CHOC003
Mars CHOC004
Kinder Bueno CHOC005
Dairymilk CHOC006
Wispa CHOC007

Thanks

Upvotes: 0

Views: 66

Answers (1)

JPV
JPV

Reputation: 27292

Assuming the list is in column A, you can try

=filter(A2:A10, len(A2:A10))

Example

If you want to incorporate this in the formula I provided earlier, you can try

={"Chocolates"; query(ArrayFormula(regexreplace(proper(A2:A10), "(?i)ch(o|0)c", "CHOC")), "where Col1 <>''",0)}

enter image description here

Upvotes: 1

Related Questions