+ Reply to Thread
Results 1 to 18 of 18

complex split string (delimit-text to columns)

  1. #1
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    complex split string (delimit-text to columns)

    Hi i need a function to split names from a single column to many.

    We have a column where 2 or more party names may be in 1 cell, like "John Smith and Jane Smith".

    Names can also be like "John A. Smith" or "John A. Smith and Mary Smith"

    the end result would be columns on 1 row that look like
    A= (source data column)
    B= JOHN A.
    C= SMITH
    D= MARY
    E= SMITH

    when i do this manually, i do a find/replace on " and ", change to a PIPE |,
    then i used data/text to columns/set delimiter to | and split so i now have 2 cols with a full party name in each column b and c. then i add 3 or 4 columns between those 2.
    then i delimit spaces on each of the combined names columns.
    then i concat fname+mi columns back, then copy/paste values over the formulas and clean up the columns to reorder..

    as you can see, many manual steps so would be time consuming and confusing to my very basic general excel users. these manual splits and concats are alot to ask them to do manually.

    i tried recording a macro while i did a simple text to columns, but when i ran it the workbook went crazy.. plus given strict security on our network i think vba code to replicate the above would be best solution.

    i'm also uploading a demo worksheet with raw data names as they would appear on my sheet, any help/suggestions would be so much appreciated, thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: complex split string (delimit-text to columns)

    I am confused by your demo worksheet. I thought I understood what you wanted until I looked at the desired results. There are names in the desired results that don't match the raw data and combinations that I am not sure where the names are coming from. Did you upload the wrong sheet? Or maybe you can explain the rationale if it is different from what you explained in your narrative.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    SORRY my oops!

    uploading a correct demo.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: complex split string (delimit-text to columns)

    Here you go, please note that in your original post said the complete names were in column A while your examples show it in column C, I did it assuming column c was right.

    Please Login or Register  to view this content.
    Tom

    If my answer resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post
    .

  5. #5
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    thank you so much.. now a silly question, how to run this as regular users who are not going to open developer tools and debug?

    can it be run automatically on a clipboard event, like on paste (users will paste the names in col C - and yes, col C was correct)

  6. #6
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    ok nevermind... i figured out how to add a keyboard shortcut to run it.

    and it works PERFECTLY... thanks again!!

  7. #7
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    sorry can i throw in two more parameters to consider?

    what if i have names like this:

    John Van DeKamp
    Susan Mc Donald

    yanno those pesky users, they do weird inputs sometimes and put spaces where they really dont belong.. is there a way we can check for these common ones in the string and like concat the last names that should obviously be in a single field together?

    ex: VAN, VON, MC, MAC, SAN, DE, DI and maybe any others you could think of?

    and i guess we should check for suffixes too...? these should also appear in the last name field:
    jr, sr, II, III, IV (these are the most common i see in the batches i work with)

  8. #8
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: complex split string (delimit-text to columns)

    While the suffixes is easy enough the Van , Von , etc. is problematic because some of them could be first names as well. I do have a solution but it requires a specific format for first and middle names. If the only possible entries are:
    1) First Name
    2) First name, middle initial
    3) First Initial, middle name

    Then the code is easy enough, but if there are other options such as first name, middle name or first initial only, I don't think it is possible.

    Before I begin coding, please let me know which way you want to go
    1) Restrict Last names to no spaces
    2) Restrict First and middle names per above

    Note there is still the problem of two name last names such as "Smith Jones" and I don't think there is any solution for this?

    Thanks,
    Last edited by tom.hogan; 05-08-2014 at 09:39 AM.

  9. #9
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    i understand this dilemma. i'm thinking middle initials should be considered a length or 1 or 2 chars (counting a period), OR, we can maybe clean the string of any punctuation if that complicates things?

    so I have a piece of VB code i was toying with a while ago for another project (it's not really complete i was strating to edit but got moved to something else before i could finish.. so it does not necessarily work in this state, but i think you'll get the gist)

    This VB code creates an array from space delimiters. Then eval's each string in the array for given conditions. what i was thinking of working out in this VB code was to check for number of SPACES in the string and let that guide what happens next..

    so TYPICALLY we assume it's a name like JOHN R. VAN DYKE, we count 3 spaces in the string, that splits the array into 4 strings. We can assume the 1st is always the first name. the 2nd should be eval's for length, and whatever is left can be considered the last name field.

    in the case of 2 names in the field, the first check would always (as you already do in your vba) is check for " and " in the string to split each into the unique seprate parties, then apply the rules of the first part i mentioned above to each of the 2 names. So the first array would be creating 2 strings of seprate parties, then sub-arrays to determine first/last name field placement.. if that makes sense?


    obviously we cant be mind readers or configure full-proof code that will 100% get every name split exactly to the correct fields, but i think we'd be at 95-98% accurate doing it this way..

    I just don't know how to write this for vba.

    also know, i meant to build and return an XML string from the VB code that would return nodes containing firt-last name of party 1 and f-l of party 2, which is difft than what you're doing in the vba. but here is that VB code, if it can inspire a resolution? (I added a few comments for you in there too.)


    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: complex split string (delimit-text to columns)

    I considered using the split function but I'm not sure it gives you much as you then have to reassemble the name. I also considered the use of number of spaces to help but while it is great for 0 and 1 space, it is of little use for more than that, e.g.

    Tom Van Dyke
    could be Tom/Van Dyke or Tom Van/Dyke
    Tom T. Van Dyke
    Could be Tom T./Van Dyke or Tom T. Van/Dyke


    Regardless I will go for the 90% solution. Can you please tell me the possible entries for first and middle? I assume first can be a name or initial and middle can only be a name or initial?

    Thanks
    As I said the suffix thing is relatively easy, it is the middle name/initial that makes it hard. If the middle name can only be an initial with or without a period then easy - otherwise I don't have a solution.

  11. #11
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    hmm. TBH I almost never see a first initial only in the data i work with (doesnt mean it NEVER occurs, but playing by common patterns and percentages its not very likely - but a Joe Von Helsing is much more common pattern, weher Von Helsing is a complete last name)..

    so i would think first is always an actual name with no spaces, except as you are already doing for MI.. MI as you already have it is working beautifully, so i think not change that at all...,

    i guess i dont understand how your existing code works right now - not for a lack of looking or trying to follow, but a lack of vba skills haha.. i was going to try and tweak your code for these things we're discussing now but didnt comprehend it enough to attempt.. someday maybe..

    So anyhoo i guess use your own best jugment how to get JACK A VON SMITH to show as
    first= JACK A
    last= VON SMITH

    is there really no way to do it to compare the literal string values like my VB code was doing (DE DI VON VAN MC etc)? If not I trust you to do what you think will result in the best accuracy.

    and a million billion thanks for your help on this :D

  12. #12
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    ok as i re-read your comments and my reply.. i know i wasnt very clear in my answer, because as i say i cant follow your code too well so i'm not sure how to answer.. but if i can try and answer this part

    If the middle name can only be an initial with or without a period then easy
    yes that is true - treat it as that MI can only be a single letter, then (maybe a period) then a space, and everything after the space following the MI is the last name.

    so first can only be a name, middle can only be an initial, then suffixes as we discussed, and everything between MI and suffix is last name.

    does that clear up what you were trying to confirm?
    Last edited by EXLwiz; 05-08-2014 at 12:44 PM.

  13. #13
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: complex split string (delimit-text to columns)

    OK, I decided to use the split function and not focus on the middle name. Based on limited testing, I believe this works for every case except for when there is an "uncommon" two name last name - like Smith Jones. You can account for this by having your operators enter these with a hyphen i.e. Smith-Jones - then I believe it will work for all cases.
    To prevent redundant code I had to add a function for parsing the names - copy all of the below into a single module.
    Please Login or Register  to view this content.
    Regards,

    Tom
    Last edited by tom.hogan; 05-08-2014 at 02:33 PM. Reason: Forgot to account for 1 name names, now fixed

  14. #14
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    first of all THANK YOU for this amazing effort. you did in a very short time what probably would have taken me weeks or longer to resolve on my own...

    so i've been doing my best to break it when testing (better us than the users right??) well sadly it ook a while but i did finally break it, but now i'm just not sure why.

    at first i thought its because in a list of 1700+ names, there were 2 rows many hundred rows apart from each other that were blank. that was an unexpected scenario even for me! so it throws an error 9 subscript out of bounds.

    so i debugged a little bit...

    line 15 in FillNames
    Please Login or Register  to view this content.
    and line 52 in Parser
    Please Login or Register  to view this content.
    those 2 are where the eror hit on a few difft tries.

    My initial belief was in vba Line 50, the first actual row of code after variables are declared in ParseName function, assumes that Name is not an empty string (somehow the empty got thru to this function?) and attempts to split the array.

    ok if it's because we hit an empty row, fine i get it, i sorted to clear the empty rows out and tried again, got to row 205 and threw the same error, this time while debugging and able to see the full contents of the main array that would hole the 1710 rows array sets. i cant even guess why this error occurs, since there's nothing unusual about that particular nameset (like jane k. doe and husband doe), and the 200 rows above it follow the same pattern more than once, and the next name down also looks fine..i also see you trim leading and trailing spaces from the string before parse, and there are no double spaces or bad chars, i checked (and you also address dbl spaces in code)

    one other thing i tried.. i manually split the 2 party names into 2 columns with my text to cols " and " = | then tried running just the single party name cols thru and it worked for each column.

    so i dunno why its throwing this error, can you elaborate or figure out what's happening?

    heres the actual list i tested with macroDemo-SplitNames.xlsm

  15. #15
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    @tom.hogan: EPIPHANY!!

    i know why it is breaking on this same name over and over.

    the name its breaking on is cANDace. we are splitting on "and" instead of " and " in the new code you posted yesterday. it did not have a leading and trailing space in the new code you did yesterday.. that 'splained it Lucie!! i added the spaces back and now it's all better!

    [edit]- i also had to change the -2 to -1, since it was trimming the last letter of the 1st party last name where theres " and " in bettween names... new code looks like this



    Please Login or Register  to view this content.

    so that resolves that mystery, but is there something which can be done about it breaking on empty rows in between rows, like

    1 - name
    2 - name
    3 - (blank-empty string)
    4 - name

    right now if i ran this code it doesnt split any rows beyond row2.
    Last edited by EXLwiz; 05-09-2014 at 09:47 AM.

  16. #16
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: complex split string (delimit-text to columns)

    Sorry, dumb mistake. When I was separating two person names I was using "AND" instead of " AND " which would improperly split up names with AND in them such as C"and"ace on Row 205. I have corrected for that. I also made a few other changes:
    1) Added code to deal with an empty line in Column C.
    2) If the macro is run with no new names it will cause an error - added a message box for this issue.
    3) Corrected for when First Name is a common name i.e. VAN SMITH would have returned "VAN SMITH" in the last name - now fixed to return "VAN" and "SMITH"

    Here is the corrected code.
    Please Login or Register  to view this content.
    Please note, I suspect there are other conditions that would cause an error. I just don't have the time to code all the possible error checking and per previous post this should be a 90% solution.

    Regards,

  17. #17
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Wink Re: complex split string (delimit-text to columns)

    Quote Originally Posted by EXLwiz View Post

    but is there something which can be done about it breaking on empty rows in between rows, like

    1 - name
    2 - name
    3 - (blank-empty string)
    4 - name

    right now if i ran this code it doesnt split any rows beyond row2.
    I think we were posting at the same time, per my previous post I have corrected for this. Please try my new code, it should be good-to-go!

    Regards,

    Tom

  18. #18
    Registered User
    Join Date
    04-08-2014
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: complex split string (delimit-text to columns)

    I think we were posting at the same time, per my previous post I have corrected for this. Please try my new code, it should be good-to-go!

    Regards,

    Tom
    LOL yes it appears we were.. now worries, we all make simple mistakes like that.. as i said, its why we test and debug so the users never see our bumbles (if we can help it!!)

    ok i think between that AND fix and the new code you just put up that i will be playing with today, i shouold be able to make my own tweaks for some of those other unexpected conditions.. cuz your right, we cant get 100%, we did say 90%.. and the longer i debug the better i am able to follow the code so i may be alright on my own from here anyway.

    so i think i can let you off the hook now...

    again many many HUUUGE thanks for the assist on this - you're a rock star!!!

+ 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: 2
    Last Post: 04-09-2014, 11:48 AM
  2. [SOLVED] Split text string into 5 columns
    By Desree86 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-14-2014, 10:07 AM
  3. Advanced Split Function: Need to pull text string in quotes, within a text string
    By Zamboni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2013, 04:38 PM
  4. Delimit string to columns after using TRIM?
    By peepingtom in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-27-2013, 09:52 PM
  5. Split text string to different columns
    By liarliar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2008, 09:13 AM

Tags for this Thread

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