column format

[noob]

I have a list of addresses A1 contains Name A2 address and street A3 CityA4 state and zip. Starts at A1 and ends at A1218. Is there a automated process that will combine the 4 cells in to B1 for all 1218 cells ?

A
1 Bob A
2 111 central ave
3 chicago
4 IL 60616

 

[jae113]

Yes. Here is one example:

=A1 & " " & A2 & " " & A3 & " " & A4

The & is telling Excel, use this cell

Having the " " in the formula creates a space between all of the data

Hope that helps!

 

[noob]

I've used that but the only problem i have with that is that B2 will display A2:A4 i would need B2:B4 to be blank.

 

[Desu Nota from Columbus]

A
1 Bob A
2 111 central ave
3 chicago
4 IL 60616

Returns this: Bob A 111 Central Ave Chicago IL 60616

Sub Address()
Dim lrow As Long
Dim r As Long

lrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lrow Step 4

Range("B" & i).FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",R[1]C[-1],"" "",R[2]C[-1],"" "",R[3]C[-1])"

Next i

For i = lrow To 1 Step -1
    If Range("B" & i).Value = "" Then
        Range("B" & i).Delete Shift:=xlUp
    End If
Next i

End Sub

let me know if you need anything else

 

[VoG]

Try

Sub ATest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR - 3 Step 4
    Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Join(Application.Transpose(Range("A" & i).Resize(4)), Chr(10))
Next i
End Sub