concatenation for four or more cells

[grayphantom]

I need help I have created a spreadsheet for address, but now i need to put all the data into one cell. So it would appear like a normal address. Can I concatenate more than two cell and have the text wrap around.

[jkpd2000]

try this ...
=concatenate(a1," ",a2," ",a3)

first time posting a solution

[ This Message was edited by: jkpd2000 on 2002-05-21 08:52 ]

[Anne Troy]

If you've got Word, you can easily try mailmerging to labels.

http://www.thewordexpert.com/word.htm#MailMerge

[klb]

Can you export data to Access? Mailing label wizard can do this for you without blinking an eye.

[cornbread]

I deal with address stuff like that every day. Put this in a module and run it as a macro.

Sub concatenate1()

Range("a1").Select 

Do while Not IsEmpty(ActiveCell.offset(0,-4))

ActiveCell = Activecell.Offset(0,-4) & " " & Activecell.Offset(0,-3) & " " & Activecell.Offset(0,-2) & " " &  Activecell.Offset(0,-1)  

ActiveCell.Offset(1,0).Select

Loop

end sub

"a1" fill in "a1" in the sub with the first cell to the right of the first four separate address cells I.E. "E1" if your first row has the first address parts on it. I have some cool ones for stripping zip codes out of longer strings of numbers if you ever need them too.



[ This Message was edited by: cornbread on 2002-05-16 13:18 ]

[cornbread]

There it's all on one line now. Thanks to NateO

[ This Message was edited by: cornbread on 2002-05-16 13:16 ]

[klb]

But how do you get the text to wrap so it looks like a mailing address like Grayphantom asked?

[gareth]

Or

You could even try this one

=A1 & A2 & A3 & A4

[Jay Petrulis]

On 2002-05-17 04:56, klb wrote:
But how do you get the text to wrap so it looks like a mailing address like Grayphantom asked?

Hi,

Following on Cornbread response, you can use Chr(10) to insert a carriage return in the cell.

Sub test()
Dim x, msg

With ActiveCell
    For x = -4 To -2
        msg = msg & .Offset(0, x) & Chr(10)
    Next x
    msg = msg & .Offset(0, -1)
    .Value = msg
End With
End Sub

This puts Cornbread's routine in a loop. As Cornbread noted, if A1:D1 contain the address data, activate cell E1 and run the routine.

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-17 05:25 ]

[klb]

Thank you Jay

From a satisfied cat>^;^<