asked on 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?
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?
VBAMicrosoft ExcelMicrosoft OfficePower BI
Last Comment
Martin Liss
ASKER
Actually its one problem. sorry
Temp.xlsx
Temp.xlsx
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
EE.xlsx
ASKER
Hey Martin, that works well, but the Suite# disappears. Would it be able to stay in the field if it was Apt or Suite?
ASKER
Tom, How do I do the power query once its open?
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you both.
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
You forgot to upload the attachment. If you switch to the Beta you won't have that problem again.