Remove Leading Spaces in Excel Address List

Question

I have numerous files where the address field is in a single line of text, for the most part separated by a comma. My first step is using 'Replace' function in Excel to replace comma's with a carriage return. This is to turn an address from a single line into multiple lines.

The issue I'm looking to get assistance with, is when I complete the steps above, a leading space is often remaining in all rows from the second row onwards. I would like to know the best way to remove the leading spaces in these rows and keep the format of multi-line addresses.

I have tried using TRIM however these returns the address back to a single line

To show the pre and post transformed data I've added an image below as I can't seem to get the format to show correctly here on this post. Due to my profile being new I also can't imbed the image so there is a link below showing the pre and post transformed data, and the leading space issue I'm seeking help with

Pre and Post Example

Thanks,

Steve

You may want to replace “[comma][space]” instead of just “[comma]” with [carriage return] — Anonymous
– Anonymous, Commented Jul 17, 2022 at 22:14
So simple but you're spot on, that works. Thanks for your comment! — Syzmik
– Syzmik, Commented Jul 18, 2022 at 0:10
No worries. To cover both cases you can also try to nest it in one formula: first replace with [space] then wrap around a second replacement without [space] — Anonymous
– Anonymous, Commented Jul 18, 2022 at 0:16

Harun24hr · Accepted Answer · 2022-07-18 01:57:18Z

0

As @Anonymous mention in comment, replace both comma and space at a time by SUBSTITUTE() formula and use WRAP TEXT format of resulting cell.

=SUBSTITUTE(A2,", ",CHAR(10))

answered Jul 18, 2022 at 1:57

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove Leading Spaces in Excel Address List

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related