+ Reply to Thread
Results 1 to 16 of 16

Best way to parse array of strings with format A_B_C_Otherstuff

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Best way to parse array of strings with format A_B_C_Otherstuff

    Hi,

    I have an array of strings such as this

    A_B_C_Otherstuff

    The Otherstuff can contain additional underscores and anyother characters. What i want to do is parse the

    A_B_C from the Otherstuff



    I want to parse the A_B_C into an array and then the Otherstuff into another array.

    I am not sure the best way to do this.

    Any ideas? Even if all you do is show me the best way to parse the string into two that would be helpful.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    Hi

    Could you pls, upload a small sample workbook for us?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    sure let me create one

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    i am enclosing just a test file of what an example string may look like and what the two resultant parsed strigns would look like.

    Again, i would like to do this via VBA.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    duh......forgot to attach the actual file........
    Attached Files Attached Files

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    I am not able to give you a suggestion, via VBA, but i just want to tell you that i do not see any test file!...


    Edit:ok, i saw it
    Last edited by Fotis1991; 04-20-2012 at 07:32 AM. Reason: edit.

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    fortis1191, if its not in vba thats ok .....maybe i can convert it myself.........

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    option
    Please Login or Register  to view this content.
    Last edited by nilem; 04-20-2012 at 08:12 AM.

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    nilem,

    Thanks for the quick response.

    Two quick questions:
    1) You commented out 3 lines of code.....was the commented out lines "another way to do something"
    2) can you explain this line of code...

    Please Login or Register  to view this content.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    x(i, 2) = Split(x(i, 1), "_", 4)(3)

    Suppose x(i, 2) is 'A2_B2_C2_DET2-Rev2_Site.xlsx'
    Split(x(i, 1), "_", 4) is an array of four elements: A2 (#0), B2 (#1), C2 (#2), and DET2-Rev2_Site.xlsx (#3)
    Split(x(i, 1), "_", 4)(3) is the third element of the array, i.e. DET2-Rev2_Site.xlsx

    Try commenting out the line
    Please Login or Register  to view this content.
    and uncomment the line
    Please Login or Register  to view this content.
    to get DET2-Rev2_Site instead DET2-Rev2_Site.xlsx

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    thanks Nilem! That helped a lot!

  12. #12
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    when i run the code and i use this as a test file name i get a run time error

    "A1_B1C1DET1-Rev1Site.xlsx"


    Any ideas?

  13. #13
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    here's an idea
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    thanks Nilem,

    I think i understand now why the runtime error occurred. There was a string which did not have 3 "_" characters. So, i think regardless of what i use, if i am going to check the string to make sure it is of the appropriate format then i am going to have to use a different approach than using the split function.

    thanks again for your help!!!!

  15. #15
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    the string format i am trying to check and then parse is of this format.

    x_y_z_otherstuff

    x => can be any combination of characters other than "_"
    y => can be any combination of characters other than "_"
    z => can be any combination of characters other than "_"

    otherstuff=> can be any combination of characters INCLUDING a "_"

    if the string has this format then i can parse it. otherwise i have to let the user know the format is wrong.

  16. #16
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: Best way to parse array of strings with format A_B_C_Otherstuff

    nilem,

    i used your code plus a little of my own.....may not be the best way but it seems to work.

    It also forced me to really think about what is an acceptable string format.

    x_y_z_DET_otherstuff2

    x => can be any combination of characters other than "_"
    y => can be any combination of characters other than "_"
    z => can be any combination of characters other than "_"
    DET=> string must contain these text characters
    otherstuff2=> can be any combination of characters but does NOT include "_"
    overall string must contain 4 underscores

    if the string has this format then i can parse it and i use the parse code you provided.

    i am enclosing a file with this code.....

    thanks again nilem!!!!!!!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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