URGENT: Parsing a String

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



Who is Participating?
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)
Use the split function to split the main string with a ","
Then use the Split function again to split the last string with a " ".
dwe0608Author Commented:
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 ....
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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)
dwe0608Author Commented:

Thanks for the input ... how would you code splitting the entire string into the variables as I have suggested them ... ?
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)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.