Parsing a string of data

I need a function to parse data between (- ) or ( , ) or ( . ) or ( / ).  These could be in different locations within the string.  I need to be able to parse between the first (.) and the second (.) or any one of the other (- ) or ( , ) or ( . ) or ( / ).  

String is as follows:  .AACTS 2001.6400.680.SDC

I need to separate the data into a table as
Field1 = ACCTS 2001
Field2 = 680
Field3 = 6400
Field4 = SDC

Thank You
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
Add this function in a module:
Function GetStringItem(strData As String, intItem As Integer) As String
    Dim strParts() As String
    strData = Replace(strData, "-", ".")
    strData = Replace(strData, "/", ".")
    strData = Replace(strData, ",", ".")
    strParts = Split(strData, ".")
    If UBound(strParts) >= intItem Then
        GetStringItem = strParts(intItem)
    End If
End Function

Open in new window

For example
?GetStringItem(".AACTS 2001.6400.680.SDC",1)
AACTS 2001
?GetStringItem(".AACTS 2001.6400.680.SDC",2)

You can use this function in a query to split the field. Make this query an update or append query to save the data in a table. Since I don't know where the data is coming from (do you have a table with all the data in it?) I can't tell you exactly what to do.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Use Split

Option Compare Database
Option Explicit

Public Sub splitIt()
    Dim aString As String: aString = "AACTS 2001.6400.680.SDC"
    Dim coll As Variant
    coll = Split(aString, ".")
End Sub

Open in new window

Dale FyeCommented:
Use the split function in combination with the replace function, something like:

Dim myArray() as variant

myArray = Split(Trim(Replace(Replace(Replace(Replace([fieldname], "-", " "), ".", " "), "/", " "), ",", " ")))

This will create an array of values that can be referred to as myArray(0)-myArray(n)

I generally use the Lbound(myArray) and UBound(myArray) to identify the upper and lower end of the array.
Dale FyeCommented:
Oops, forgot the delimiter (" ") in the split part of that operation.
frank_guessAuthor Commented:
Let me take a look at both and I will get back to you, once I have tried each method.  Thanks
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.