+ Reply to Thread
Results 1 to 10 of 10

How to parse a simple text string with VBA?

  1. #1
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    How to parse a simple text string with VBA?

    Hi Everyone
    I am reading a text file which gives me separate lines of data.

    How do I parse a single line of data delimited by the ";" character into variables which I can then use to populate text boxes, labels, combo boxes etc. on a user form.

    Each data line string can have different numbers of data items.
    The data items can be of different lengths.
    I understand the idea of arrays.
    I DON'T want to use SPLIT!

    I know its probably only a simple looping and text handling thing but I can't quite figure out the logic to move through the data line to extract the individual items into individual variables.

    DOH!

    Thanks
    barry
    Last edited by barryleajo; 09-27-2010 at 04:08 PM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to parse a simple text string with VBA?

    Why don't you want to use Split?
    Remember what the dormouse said
    Feed your head

  3. #3
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: How to parse a simple text string with VBA?

    Thanks Romperstomper
    My need is to keep control of UP TO 13 sets of data
    Each data set has around 30 individual data elements
    The user form will select which set of data to work on using a combo box
    I think this is probably a two-dimensional array jobby?
    I understand that SPLIT can only populate a single dimension array.
    barry

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to parse a simple text string with VBA?

    That is true but you can just use a 1D array of arrays. Or just store the 13 lines in the array and then split whichever one is chosen at runtime.

  5. #5
    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: How to parse a simple text string with VBA?

    Hello barryleajo,

    If you provide a sample of the data you are working with then we can provide you with more options. If you have a workbook that you can post that would be best. It should include a sample of the desired result.
    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!)

  6. #6
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: How to parse a simple text string with VBA?

    As ever many thanks Romperstomper and Leith for your quick responses.

    Leith - I will include files if needs be but my query is for a general 'approach' rather than to fix a specific problem. I am more than happy to do the leg work to develop my own skillsb rather than tie you guys up on trivia.

    One important point is that I can decide the format of the text file.

    It is a straightforward read data in from a text file - I can do this OK
    Put variable values into userform controls - I can do this (in principle anyway)
    My problem is the logic to cut up a string of varying length delimited by ";" into a varying number of variables.
    I don't want to over-complicate this so use of SPLIT has definite advantages but I am unsure about how to keep the sets of data togther and reference them. With a 2D array my logic was (Data_Set x, Data_Item y) - but this approach needs me to cut up the string

    Rompertomper - I like your idea of each of the 13 lines in the array and SPLIT at runtime.
    I can re-design the text file format to do this.
    Could you explain more about what is and how I would set-up/use a .."1D array of arrays" please.

    With many thanks
    barry

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to parse a simple text string with VBA?

    Like this for example:
    Please Login or Register  to view this content.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: How to parse a simple text string with VBA?

    using semicolon ; as listseparator:

    Please Login or Register  to view this content.



  9. #9
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: How to parse a simple text string with VBA?

    I thought I posted a "thankyou" to close this thread yesterday but I can't see it so just for completeness and courtesy I am posting again.

    To my rapidly emerging virtual mentor Romperstomper - thank you for your beautifully elegant solution of using 'array of arrays'.
    I re-designed my text file and it works like a dream.
    Yesterday morning I didn't know where to go with this and by the evening it was all but sorted.

    This was yet another example where a keen 'improver' would never have found this solution.
    We remain indebted to you passing on your great experience.
    I did tip your scales yesterday.

    Thanks also to snb for your reply.
    barry

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to parse a simple text string with VBA?

    Barry,
    Thank you for your kind words. It is always a pleasure to help someone who likes to learn!

+ 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