+ Reply to Thread
Results 1 to 15 of 15

Looking for help with slightly advanced delimiting & text to columns.

  1. #1
    Registered User
    Join Date
    12-17-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Question Looking for help with slightly advanced delimiting & text to columns.

    This is in Excel 2016 (I think that's the version)

    I think this might have to be done with VBA but if I can avoid it I'd like to, only because I don't have a lot of VBA experience.

    I'm trying to figure out how to split addresses into a few specific columns, and I'm having some trouble figuring out exactly how to do that.

    The addresses are almost always like this:

    100 Street Name BLVD City, STATE 00000 (state is two letter abbreviation, CA/AZ/NV etc)

    the columns I need are "100 Street Name BLVD" "City" "STATE" and "0000". If i set it to delimit by space and comma then it almost works except that splits the whole street address. In theory I think I'd want it to delimit after a series of capital letters (that should handle BLVD and any smaller permutations of it like ST/RD/CIR, without hitting capital letters in street names). There's also the added problem of City names that have a space in them, so I might need to somehow make an exception or make it not start a new column until it hits ", "

    I'm not super sure how to actually do that, though, so any advice is appreciated. Thanks!
    Last edited by mrolson0603; 12-17-2018 at 07:50 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Hello mrolson0603,

    Welcome to the forum!

    Splitting data up is a tricky business, especially addresses. The key to easy parsing is consistency in syntax (like that ever happens). The more of the actual data we can see will help us to provide you with the best solution.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    12-17-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Looking for help with slightly advanced delimiting & text to columns.

    I can't really share the actual data I'm using for privacy reasons, but they're all in a very similar format. Currently I'm manually splitting them up and it's taking a while, I suspect I can do it much faster if I can get Excel to do it for me. I can show you some example addresses, though. (Changed every name and number, format is identical)

    10198 Johnson DR Sacramento, VA 12345
    203 Timothy PKWY Austin, VA 99999
    816 Jimothy AVE NW Washington, DC 20006 <- These might be the tricky ones if there's multiple CAPS, might need to fix those manually but they're rare
    11211 Tohnson Lake LOOP Boston, VA 64875

    They're generally consistent (the number of words in the street isn't consistent but it generally ends in an all caps keyword, same with city etc)

    There's about 500-600 of those a week, automating it would save me probably 4-5 hours a week which would be fantastic.

    Looking at the last hundred addresses I've only seen ONE that didn't have some kind of road signifier at the end (ST/RD/whatever, I still dont know what those are technically called), and the ones I can see being problematic are addresses on things like "NW Fir ST" because the NW would trigger a delimiter and it needs to go after ST. Unless I keep like a list of endings and populate it over time so it only delimits if it finds that specific affix...

    I dont have a lot of experience with VBA, I worked with Google Sheets scripting for a bit and some java but I'm really not good at this lol
    Last edited by mrolson0603; 12-17-2018 at 08:58 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Perhaps this will be of some help.
    Note that this proposal employs a list of endings (O2:O6) as stated in post #3.
    B2: =AGGREGATE(14,6,SEARCH(O$2:O$6,A2),1)
    C2: =SEARCH(" ",A2,B2)
    D2: =LEFT(A2,C2)
    E2: =MID(A2,LEN(D2)+1,SEARCH(",",A2)-(LEN(D2)+1))
    F2: =MID(A2,SEARCH(",",A2)+2,2)
    G2: =RIGHT(A2,5)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looking for help with slightly advanced delimiting & text to columns.

    This Code:

    Please Login or Register  to view this content.
    Results in this:

    Data Range
    A
    B
    C
    D
    6
    Street
    City
    State
    7
    10198 Johnson DR Sacramento, VA 12345
    10198 Johnson DR
    Sacramento
    VA 12345
    8
    203 Timothy PKWY Austin, VA 99999
    203 Timothy PKWY
    Austin
    VA 99999
    9
    816 Jimothy AVE NW Washington, DC 20006
    816 Jimothy AVE NW
    Washington
    DC 20006
    10
    11211 Tohnson Lake LOOP Boston, VA 64875
    11211 Tohnson Lake LOOP
    Boston
    VA 64875
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Try the attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-17-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Quote Originally Posted by jindon View Post
    Try the attached.
    Please Login or Register  to view this content.
    That almost works perfectly, actually, except it doesn't separate city/state/zip correctly, but I think I can fix it...

    Apologies for the late reply, holidays were hectic.


    Edit: I have no idea how to fix this, but it's almost perfect. Here's how they end up:

    Address Street address State Zip
    10198 Johnson DR Sacramento, VA 12345 10198 Johnson DR Sacramento VA 12345

    So the City is under State and the state is put next to the zip code
    Last edited by mrolson0603; 01-04-2019 at 07:10 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Try change to
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-17-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Looking for help with slightly advanced delimiting & text to columns.

    that works really well, actually! Thank you for your help so far. I have two more questions. How would I handle truncating zip codes from xxxxx-xxxx to xxxxx with this macro? I didn't realize until just now that they do that. Adding a "-" might work but I'm not very familiar with how macro patterns are organized. I don't need anything after the first five numbers.

    And how would this handle if the entire address is in caps? I'm guessing that's how it determines ST/LOOP/etc so it might not work at all, I'm just curious. I've learned a decent amount just reading stuff online and messing with the macro.

    Also, and this is completely unrelated, when I pull addresses (we use LexisNexis) they show up in two lines

    Please Login or Register  to view this content.
    Is there a way to paste that into a single line easily, or somehow incorporate that into the macro? I know you can double click a cell and paste it, but that's a little annoying. If not, that's also fine.

    Thanks again for all your help!
    Last edited by mrolson0603; 01-07-2019 at 10:09 PM.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Looking for help with slightly advanced delimiting & text to columns.

    1)
    Don't quote whole posts -- it's just clutter.
    If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.


    2) If you upload a sample workbook with data and the result that you want, it will help.

  11. #11
    Registered User
    Join Date
    12-17-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Sorry about that, edited previous post. I will upload a sample workbook tomorrow so you can see what I'm dealing with. Again, I really appreciate the help, macros are a little difficult for me to even comprehend but I'm getting there.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Looking for help with slightly advanced delimiting & text to columns.

    OK, and thanks for editing your post.

    I will be busy rest of the day anyway, so will wait for the workbook.

  13. #13
    Registered User
    Join Date
    12-17-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Alright, I've (hopefully) attached my example book. Realistically I don't even know if this is possible, but ideally I put the full address in column F and the next three populate based on that address, essentially what I was trying to do above.
    The address being in all caps might be the biggest obstacle, I have no idea how to address that. I've written similar things in Google Sheets but Excel doesn't work the same way and I'm not familiar with its limitations.

    example book.xlsx

    I appreciate the help so far, I'm still really lost on this tbh.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Looking for help with slightly advanced delimiting & text to columns.

    This is completely different from the one you originally asked.

    All in capital letters? and the result that you want is only 3 rows?

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Looking for help with slightly advanced delimiting & text to columns.

    Do you have list of city names separately. If so add the list in the file and upload.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 05-17-2018, 10:20 AM
  2. Replies: 1
    Last Post: 08-19-2016, 07:03 AM
  3. Advanced Text To Columns
    By psgolfer32381 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2015, 12:58 PM
  4. Having trouble delimiting long text cell
    By Dsking85 in forum Excel General
    Replies: 2
    Last Post: 07-24-2014, 10:59 AM
  5. Question on Delimiting Text in Cell for phrases before and after semicolons
    By undergraduate in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2010, 04:41 AM
  6. [SOLVED] Macro delimiting text if column has value
    By Beverly76 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2006, 06:45 PM
  7. [SOLVED] Pasting from clipboard-how to control text-to-columns delimiting
    By stebro in forum Excel General
    Replies: 1
    Last Post: 06-15-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1