URGENT: Parsing a String

Posted on 2006-04-20
Last Modified: 2010-08-05
Hi All

I have a string formatted as follows:

2005443,"1/20050602104455/636","13/04/2006 14:49:02 ACT DWE : Printed:s80 - No Property"

I need to break this string down .... I use the split function and would not ordinarily have trouble, but because I need to break it down a little further I am getting lost a little

Using the above string as an example .... I need to break the string down into the following variables

FileNum = 2005443
ClientRef = 1/20050602104455/636
DateTime = 13/04/2006 14:49:02
Act = ACT
Author = DWE
Note = Printed:s80 - No Property

All of the strings are typed as strings except the datetime variable which is a date.

Notice the part of the string which commences with the DateTime variable is what I am having trouble with, as in my example string it is all one field.

Commented coded answers are appreciated



Question by:dwe0608
    LVL 18

    Expert Comment

    Use the split function to split the main string with a ","
    Then use the Split function again to split the last string with a " ".
    LVL 1

    Author Comment

    I gave that some thought; but the last string consists of many spaces, so it would return everything after "13/04/2006 14:49:02 ACT DWE" as individul fields ....
    LVL 18

    Expert Comment

    I agree..just split with a space and contactenate first two splitted variables. Example:
    Dim strSplit() as string
    strSplit=split("13/04/2006 14:49:02 ACT DWE"," ")
    This would return 4 values. Ignore last two and use only first two
    strFinal = strSplit(0) & strSplit(1)
    LVL 1

    Author Comment


    Thanks for the input ... how would you code splitting the entire string into the variables as I have suggested them ... ?
    LVL 18

    Expert Comment

    Dim strCommaSplit() as string
    Dim strSpaceSplit() as string

    strCommaSplit=split(2005443,"1/20050602104455/636","13/04/2006 14:49:02 ACT DWE : Printed:s80 - No Property",",")

    strSpaceSplit=split(strCommaSplit(2)," ")
    strFinal = strSpaceSplit(0) & strSpaceSplit(1)
    LVL 44

    Accepted Solution

    I would recommend the following tweak:

    Dim strCommaSplit() as string
    Dim strColonSplit() as string
    Dim strSpaceSplit() as string

    strCommaSplit = Split(2005443,"1/20050602104455/636","13/04/2006 14:49:02 ACT DWE : Printed:s80 - No Property", ",")

    strColonSplit() = Split(strCommaSplit(2), " : ")

    strSpaceSplit=split(Trim(Mid$(strColonSplit(0), 20)), " " )

    FileNum = strCommaSplit(0)
    ClientRef = strCommaSplit(1)
    DateTime = Left$(strColonSplit(0), 19)
    Act = strSpaceSplit(0)
    Author = strSpaceSplit(1)
    Note = strColonSplit(1)

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now