We help IT Professionals succeed at work.

Text to column address in excel but maintain apt #

I have two problems on this sample attachment of an excel table.

In the address column, I want to separate the city, state and zip.  However, when I use the "Text to columns" feature, it also separates the Apt # into an additional column creating a problem.  Is there a way to separate the city, state and zip while leave the apt or suite attached to the address?
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:

I have two problems on this sample attachment of an excel table.


You forgot to upload the attachment. If you switch to the Beta you won't have that problem again.

Author

Commented:
Actually its one problem.  sorry
Temp.xlsx
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:

Try this.


29170441.xlsm

Commented:
Another option.  Used Power Query (Get and Transform) to do this.  You can add more data to the Original Data tab, and then right click and refresh the Result tab and it should give you all the information including the new lines you added.
EE.xlsx

Author

Commented:

Hey Martin, that works well, but the Suite# disappears.  Would it be able to stay in the field if it was Apt or Suite?

Author

Commented:

Tom, How do I do the power query once its open?

Commented:
Not sure what you mean "once its open".  Right now you can just add information to the Original Data and right-click the Result and say refresh and all should be good.  Your columns on the Original Data need to remain the same.

Now as far as how to use Power Query (Get and Transform in Excel 2016), there are many videos on how to use it.  In the case for you, I used the parse fields on columns starting with the data on the right (zip, state, city, etc) and worked toward the left.  It is a good tool and does far more than just parsing data.  It is basically a tool for cleaning up data before putting it into a well-formed table(s) which are ultimately used in Power Pivot or Power BI.
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:

Corrected.29170441a.xlsm

Author

Commented:

Thank you both.  

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:

You’re welcome and I’m glad I was able to help.


If you expand the “Full Biography" section of [url="http://www.experts-exchange.com/M_258447.html"]my profile[/url] you’ll find links to some articles I’ve written that may interest you.


Marty - Microsoft MVP 2009 to 2017

        Experts Exchange Most Valuable Expert (MVE) 2015, 2017

        Experts Exchange Top Expert Visual Basic Classic 2012 to 2019

        Experts Exchange Top Expert VBA 2018, 2019

        Experts Exchange Distinguished Expert in Excel 2018