Rows C-F have the address. I created a new column and need the address in one column,row J

[BaAnalyst]

Rows C-F have the address of the users. I created a new column, and I need the address in just one column, (Row J)

In this format: Street address, city, state ZIP. I believe this would be a script/ formulaAddress in one Column.JPG

I have created a screen capture.


Thank you.

[nigelog]

Please Login or Register  to view this content.

[BaAnalyst]

Hello. and thanks for your help. I went to run that script and received an error. See screen capture.script error.JPG

[nigelog]

No problems here add to a module not the sheet module

Please Login or Register  to view this content.

[BaAnalyst]

That didn't work. Are you calling my sheet, Sheets (1) ? Because I have named the sheet. Where do I enter my sheet name in the script?Mod.JPG
I have named my column (J) "Full Address" in cell J1

[nigelog]

so might not be sheet 1

Please Login or Register  to view this content.

[BaAnalyst]

I'm not 100% clear on your reply. Let me enter the name in the script to see if it's correct. I have named my sheet Example. See below

Sub address()
Dim lrow As Long
Dim i As Long
lrow = Example.Range("a" & Rows.Count).End(xlUp).Row
With Example
For i = 2 To lrow
.Range("j2:j" & lrow).Formula = "=c2&"" - ""&d2&"" - ""&e2&"" - ""&f2"
.Columns(10).AutoFit
Next
End With
End Sub

[nigelog]

Please Login or Register  to view this content.

[BaAnalyst]

That worked. Except the script added a hyphen in - between - each - address - block. screen shot dashes.JPG

[BaAnalyst]

would it be hard to create a script and remove the hyphen and add a comma? in this format: 1 National Ave, Cleveland, OH 85623
So a comma after the street, and city

[nigelog]

Please Login or Register  to view this content.

change or omit dashes in red

as in

Please Login or Register  to view this content.

[BaAnalyst]

I received an error. error.JPG

[JeteMc]

Guessing that "1 E address" is in cell C2 (assuming that the top row is filled with column headers) paste the following into cell J2 then copy down: =C2&" "&D2&" "&E2&" "&F2
Let us know if you have any questions.