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.

 

[Peter_SSs]

Thanks. If this isn't what you want, then please also provide the expected result (manually entered) with XL2BB as well.

Maverick27.xlsm
	A	B
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	Email:admin@ankole.co.ke ; Email:x ; Email:x ; Facebook:www.facebook.com/ankolegrill ; Instagram:www.instagram.com/ankolegrill ; Linkedinwww.linkedin.com/company/ankole-grill ; Twitter:www.twitter.com/ankolegrill ; Web:www.ankole.co.ke
Sheet2

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)&CHAR(10)," ; "),CHAR(10),"")

 

[Maverick27]

Thanks. If this isn't what you want, then please also provide the expected result (manually entered) with XL2BB as well.

Maverick27.xlsm
	A	B
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	Email:admin@ankole.co.ke ; Email:x ; Email:x ; Facebook:www.facebook.com/ankolegrill ; Instagram:www.instagram.com/ankolegrill ; Linkedinwww.linkedin.com/company/ankole-grill ; Twitter:www.twitter.com/ankolegrill ; Web:www.ankole.co.ke
Sheet2

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)&CHAR(10)," ; "),CHAR(10),"")

Thanks- looks Good !!

 

[Peter_SSs]

You're welcome.