CODEWITHSUNDEEP
excelvbaexcel-formula
John
John

Reputation: 565

Remove Blanks in Excel using Formula

I have the below example where I have 3 columns in "CONDITION"

If I have data in all the 3 columns then the results should be column2 and column3 as shown in fig.

enter image description here

I used the below formula for "Results with my formula"

=IF(AND(NOT(ISBLANK($A2));NOT(ISBLANK( $B2));NOT(ISBLANK( $C2)));CONCATENATE( $B2; $C2)

But, I need the results as in "Expected Results" column

In case if the value in the above row is empty then the current value has to be posted in this cell.

Upvotes: 0

Views: 480

Answers (3)

Brad Lee
Brad Lee

Reputation: 187

Assuming that you start on row 2, this macro should do the trick.

Sub Test()

Dim a as Long
Dim b as Long
Dim lastrow as Long

lastrow = Cells(Rows.Count, 2).End(xlUp).Row

b = 2
For a = 2 To lastrow

If Cells(a, 1) <> "" Then

Cells(b, 7) = Cells(a, 2) & " " & Cells(a, 3)

b = b + 1

End If

Next

End Sub

Upvotes: 2

bosco_yip
bosco_yip

Reputation: 3802

Another formula solution.

Formula in E2, copied down :

=IFERROR(INDEX($B$2:$B$13&" "&$C$2:$C$13,AGGREGATE(15,6,ROW($A$2:$A$13)-ROW($A$1)/($A$2:$A$13<>""),ROW(A1))),"")

Note : please change from "," to ";" in suit with your own country regional settings.

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

NOTE: My local settings use , instead of ; for the formula criteria delimiter. You may need to switch out the , for ;

If one has the dynamic array formula FILTER:

=FILTER(B3:B14&" "&C3:C14,LEN(A3:A14)*LEN(B3:B14)*LEN(C3:C14)>0)

enter image description here

If not then use:

=INDEX(B:B,AGGREGATE(15,7,ROW($B$3:$B$14)/(LEN($A$3:$A$14)*LEN($B$3:$B$14)*LEN($C$3:$C$14)>0),ROW($ZZ1)))&" "&INDEX(C:C,AGGREGATE(15,7,ROW($B$3:$B$14)/(LEN($A$3:$A$14)*LEN($B$3:$B$14)*LEN($C$3:$C$14)>0),ROW($ZZ1)))

put in first cell and copy down.

enter image description here

Upvotes: 2

Related Questions