How do I condense, combine and condense info that is in one cell?

So I have a column that is the ID and then I have an address column that has all of the addresses listed in it. It is separated in the cell by a line break. Some of the addresses are basically the same minus a state or zip code. How do I condense it into one? I appreciate your response! You are making my work life alot better!

So I attached a dummy file- you can see that column D is the address field and that multiple are listed. How do I condense or combine the ones that are basically the same? Any help would be the best!!!
example-of-address-thing.xlsx
Sarah KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomfarrarCommented:
First thing you want to do (assuming there are commas separating the items needing parsing like in your example) is to use Excel Text To Column, select delimited by comma, then see what the data looks like and how easy it may be to work with.

Since you only provided a small amount of data, it is kind of hard to project this solution across all the data.
1
Sarah KAuthor Commented:
So I separated the data using the excel text to column... Now what? Also the one cell that is now separated was tied to an ID too... I don't know if that is relevant at this point.
0
tomfarrarCommented:
If you can't share the entire spreadsheet, then can you show me what the answer you want for the original example provided?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Sarah KAuthor Commented:
So I wrote what I am trying to do in column E and so I hope it makes sense! Thank you so much!!!
example-of-address-thing.xlsx
0
tomfarrarCommented:
So, now I am getting what you are doing.  Try this solution posted by another EE expert:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28642390.html
0
Sarah KAuthor Commented:
I don't understand this option... Is there a way to create a formula to do it?
0
tomfarrarCommented:
Make a copy of your tab that has the data you want to parse.  Use the "Substitute" function in the solution on the first cell that has multiple rows (in the cell) and copy down to the end of your data.  Once the ";" has been substituted for the line break that is in the cell, then you can follow his/her solution to parse the data using Text to Columns, then follow the rest of the solution (paste special....).  You should begin to see how the data will come together.  There may be some manual work on your part to get to the final result, but it should be minimal.  Use your imagination.
1
tomfarrarCommented:
Please see the attached spreadsheet for moving your question down the road, but not finishing.  My suggestion is you repost the question after completing the steps in the spreadsheet so as to simplify what might be a final answer.
example-of-address-thing.xlsx
0
tomfarrarCommented:
Hi Sarah - Here is another expert solution.  Think this very close to what you need after getting the data into multiple rows using the above solution.

http://www.experts-exchange.com/questions/28702175/Combining-Excel-Rows-Into-One-Row.html
0
Ejgil HedegaardCommented:
Here is a possible solution with a macro function CondenceText.
Accept macros to run.
Use it as a regular Excel function, see D4.

The function starts with the first line of text, and then finds all texts in the remaining between commas, and adds if not already there.
But not exactly, since the text "School of Biological & Chemical Sciences" does not match the first "School of Biological Sciences", and similar "Mile End Road" does not match "Mile End Rd".
It looks bad if added, so instead of adding all that is not there to the initial text (line 1) the function only looks if the first word exist already (School, Mile), and if not the entire text (between commas) is added.

See if it does most of what you need.
example-of-address-thing.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.