Separate address into separate lines WITHIN a cell

[asabur6]

Hi,
I have a bunch of addresses in rows of single lines in one cell. I need A1 to become B1 without having to manually do it by hitting ALT + ENTER to go to the next line within each cell.

-see attachment-

Thanks for your help!

[AliGW]

One example is never going to be enough.

There are instructions at the top of the page explaining how to attach your sample workbook.

[ChemistB]

Assuming that the town name is always a single word (big assumption)

Please Login or Register  to view this content.

The cell with the formula must be formatted to wrap text.
If you are going to have town names with spaces, you need to come up with some logical way for Excel to be able to know where to place the line break.

[asabur6]

Hi Ali,
I have reposted with the spreadsheet attached instead of an image.
However, I must admit that Iam confused. In "Manage attachments" there are plenty of image extensions listed as acceptable. But under "HOW TO ATTACH YOUR SAMPLE WORKBOOK:" it says "Post a small Excel sheet (not a picture)" How do I know when/or if an image is acceptable or not? I guess when you all would need to "play around" with our data to come up with solution?

Thanks

[asabur6]

Thanks ChemistB,
That got me mostly where I need to be. It worked for alot of addresses. However, a space between a city name is not necessarily the only problem. I need 2 breaks (after the street AND after the State). I need help with that and dont even know where to begin to "come up with some logical way for Excel to be able to know where to place the line break."

[ChemistB]

Is it possible to have a list of all the towns (in a single column) somewhere (on sheet2, i.e. )? Otherwise, no way for Excel to know where the street name ends and the town/city begins.

Here is the original formula updated to move the zip code to a new line (fyi, your example has it on the same line as the state and city)

Please Login or Register  to view this content.

[asabur6]

Thanks, this one worked best! Very much appreciated!

[ChemistB]

With a list of all the towns/cities in sheet2!A1:A17, this formula takes into account spaces in town names

Please Login or Register  to view this content.

I used it in column D

[asabur6]

Thanks again!