Help separating addresses text to column not working using delimited

[berrypeachy_mood]

Hello, I am completely new to excel and I've been trying to separate addresses by street address, city, state, and zip code. I was following a youtube tutorial where they used text to column and delimited in order to separate the text. I have been able to get to the part where you first select text to column and the option show up to select delimited and the addresses look fine on that page but once I click next all the addresses disappear and only the names are left and I have no idea what I'm doing wrong T.T

[Sam Capricci]

try this in B1, drag right then down...
=TRIM(MID(SUBSTITUTE(CHAR(10)&$A1,CHAR(10),REPT(" ",125)),125*COLUMNS($A:A),125))

[berrypeachy_mood]

wow Thank you!! and how would i sepreate city state and zip ?

[alansidman]

The answer to your question depends on the version of Excel you are using. florida is not an excel version. Please help us to help you by providing us with your version, ie. 2010, 2016, O365 etc. Update your profile to reflect that version.

[berrypeachy_mood]

i didnt realized that ill update now I'm on 2019 version!

[berrypeachy_mood]

correction 2109

[berrypeachy_mood]

i updated my excel version to see if you could help me separate city and zip as well! thank you again for your help!

[alansidman]

Because your data is not consistent in its presentation, I apologize, but I do not have a solution for you.

[GeoffW283]

Here is an extension of Sam's post #2 formula. It attempts to separates out town/state/zip but due to the numerous inconsistencies in your address data it is successful for only 40 out of your 58 samples or just 69%. There would also be an easy manual cleanup of spurious trailing commas in the "State" column.

I don't know how to do better but if this is of use to you then insert a header row in your sample file and then in B2 copied across and down:

Please Login or Register  to view this content.

[DJunqueira]

I think Power Query did a good job.
Make the addresses a Table, activate PQ, chose Divide Columns, By delimiters, accept the conditions as it is.

[AliGW]

correction 2109

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. 2109 is a release number of the version you have, not the version itself. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.