Change Address Text from multiple lines to a single line ?

[Maverick27]

Attn Excel Gurus

Anyone care to share a Macro or Excel Function to change Address text layout from multiple lines to a single line ?

1. Remove top blank space(s) shown by red box
2. Insert comma & single space after each line of text


Original format:

After Modification:

The Address is displayed on Column U of the Sheet. Row # : 194

Thanks in Advance.

 

[Kerryx]

Use Text to Columns in DATA tab to move to cells , use a line break (CTRL +J) as your delimiter.

315 Chinese & Thai Cuisine

Off the road

Upstairs

Downstairs

Around the Bend

Up in the Air

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	315 Chinese & Thai Cuisine Off the road Upstairs Downstairs Around the Bend Up in the Air	315 Chinese & Thai Cuisine	Off the road	Upstairs	Downstairs	Around the Bend	Up in the Air	315 Chinese & Thai Cuisine,Off the road,Upstairs,Downstairs,Around the Bend,Up in the Air,
Sheet1

Cell Formulas
Range	Formula
I2	I2	=CONCAT(C2:H2&",")

Then Concat formula to put into a single line

 

[ouvay]

Hey

Give this a go

Private Sub reformat_VBA()

Dim lr As Long
With Sheet1
lr = .Cells(.Rows.Count, 21).End(3).Row
.Range("U2:U" & lr).Replace what:=Chr(10), Replacement:=", ", MatchCase:=False
.Range("U2:U" & lr).WrapText = False
For i = 2 To lr
    Debug.Print Left(.Range("U" & i), 2) = ", "
    If Left(.Range("U" & i), 2) = ", " Then .Range("U" & i).Value = Right(.Range("U" & i), Len(.Range("U" & i)) - 2)
Next i
End With
End Sub

 

[ouvay]

Hey

Give this a go

Private Sub reformat_VBA()

Dim lr As Long
With Sheet1
lr = .Cells(.Rows.Count, 21).End(3).Row
.Range("U2:U" & lr).Replace what:=Chr(10), Replacement:=", ", MatchCase:=False
.Range("U2:U" & lr).WrapText = False
For i = 2 To lr
    Debug.Print Left(.Range("U" & i), 2) = ", "
    If Left(.Range("U" & i), 2) = ", " Then .Range("U" & i).Value = Right(.Range("U" & i), Len(.Range("U" & i)) - 2)
Next i
End With
End Sub

This assumes that the data you wish corrected is in column U (as per your screenshots)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Perhaps this?

22 09 02.xlsm
	U	V
1	315 Chinese & Thai Cuisine Some text Other text City etc	315 Chinese & Thai Cuisine, Some text, Other text, City, etc
Reformat address

Cell Formulas
Range	Formula
V1	V1	=SUBSTITUTE(MID(U1,2,LEN(U1)),CHAR(10),", ")

 

[Maverick27]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Perhaps this?

22 09 02.xlsm
	U	V
1	315 Chinese & Thai Cuisine Some text Other text City etc	315 Chinese & Thai Cuisine, Some text, Other text, City, etc
Reformat address

Cell Formulas
Range	Formula
V1	V1	=SUBSTITUTE(MID(U1,2,LEN(U1)),CHAR(10),", ")

Thanks everyone for your reply.

I decided to use this Function.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

Please still update your details as requested above, as different Excel versions have different functions available to them.

 

[Maverick27]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Perhaps this?

22 09 02.xlsm
	U	V
1	315 Chinese & Thai Cuisine Some text Other text City etc	315 Chinese & Thai Cuisine, Some text, Other text, City, etc
Reformat address

Cell Formulas
Range	Formula
V1	V1	=SUBSTITUTE(MID(U1,2,LEN(U1)),CHAR(10),", ")

Hello Again Peter

Can you pls fine tune the Excel Function:

=SUBSTITUTE(MID(U1,2,LEN(U1)),CHAR(10),", ")

The Data String is displayed as below in the Excel Cell:

When I apply the Excel Function to the cell, the result is as below:

The final output should look like:

Note: the semi colon which separates the Text instead of comma.

Rgds.

 

[Peter_SSs]

I cannot access the data or expected results in a format to test and/or check the results. Can you provide it in a way that I can? (XL2BB would be best)

 

[Maverick27]

MASTER.xlsx V3.xlsm
	A
1	Email: admin@ankole.co.ke Email: x Email: x Facebook: www.facebook.com/ankolegrill Instagram: www.instagram.com/ankolegrill Linkedin www.linkedin.com/company/ankole-grill Twitter: www.twitter.com/ankolegrill Web: www.ankole.co.ke
Sheet1