Help separating addresses text to column not working using delimited

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

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.

Originally Posted by berrypeachy_mood

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.

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

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:

Formula:

=TRIM(MID(SUBSTITUTE(CHAR(10)&   SUBSTITUTE(LEFT($A2, LEN($A2)-6) & CHAR(10) & RIGHT($A2,5), ", ", CHAR(10)),CHAR(10),REPT(" ",125)),125*COLUMNS($A:A),125))