• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Parsing out a row cell with an address of varying lengths

W

William Matthews

New Member
I have, for example the following information, one line per row. I just need to get the City, State, and Zip (for demographic information). I have tried to use Text-to-Column with spaces, with commas, the formula =LEFT(A2,FIND(" ",A2)-1) all to no avail. Thank you.
1059921 DDDC TRANSSHIP BRANCH 233 ENTERPRISE DR / SHIPPING DOOR 85 NEWPORT NEWS VA 23603 USA
506 LARCHER BLVD BLDG 2306 KEESLER AFB MS 39534 USA
2725 LEMAY BLVD BLDG 4020 ELLSWORTH AFB SD 57706 USA
700 EAST ROTH RD BLDG 550A FRENCH CAMP CA 95231 USA
700 EAST ROTH RD BLDG 550A FRENCH CAMP CA 95231 USA
700 EAST ROTH RD BLDG 550A FRENCH CAMP CA 95231 USA
982 MACON ST 1031003 ROBINS MAIN STORE ROBINS AFB GA 31098 USA
1010835 SAM AMCSS 2831 HENRY T ALLEN BLDG 4188 FT SAM HOUSTON TX 78234 USA
761ST TANK BATTALION AVE BLDG 135 FT HOOD TX 76544 USA
6050 GORGAS RD BLDG 2303 FT BELVOIR VA 22060 USA
700 EAST ROTH RD BLDG 550A FRENCH CAMP CA 95231 USA
7200 PERIMETER RD 1068412 NEW MALMSTROM MAIN STOR BLDG 1340 MAFB MT 594020000 USA
2503 FUNSTON RD BLDG 2420 FT SAM HOUSTON TX 78234 USA
1059921 DDDC TRANSSHIP BRANCH 233 ENTERPRISE DR / SHIPPING DOOR 85 NEWPORT NEWS VA 23603 USA
195 CHAPPIE JAMES BLVD BLDG 4514 WASHINGTON DC 20032 USA
WASHINGTON BLVD BLDG 1386 FT EUSTIS VA 23604 USA
ACTON DRIVE BLDG 3220 REDSTONE ARSENAL AL 35898 USA
3360 N AVE BLDG 685 TINKER AFB OK 73145 USA
 
Hi William Mathews,

Welcome to the Forum!

Can you upload a sample file. As Text to Column Should work fine for this case.

Regards,
 
Hi, William Matthews!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, despite of the file that will help I think I'm bad news since there's no problem to get the State and Zip code, but the City turns things uncertain as you don't know how many words it contains. But with the State and Zip code you can access to any downloadable list of cities and get the name from there, can't you? I don't know any of your country but surely you do or Google may help you.

State: =EXTRAE($A1;LARGO($A1)-11;2) -----> in english: =MID($A1,LEN($A1)-11,2)
Zip: =VALOR(EXTRAE($A1;LARGO($A1)-8;5)) -----> in english: =VALUE(MID($A1,LEN($A1)-8,5))

Regards!
 
You must log in or register to reply here.
Back
Top