Parsing a string of data

Posted on 2011-04-20
Medium Priority
Last Modified: 2012-05-11
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
Question by:frank_guess
LVL 22

Accepted Solution

Nico Bontenbal earned 1000 total points
ID: 35433381
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.
LVL 24

Expert Comment

ID: 35433389
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

LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 35433416
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.
LVL 49

Expert Comment

by:Dale Fye
ID: 35433450
Oops, forgot the delimiter (" ") in the split part of that operation.

Author Comment

ID: 35433704
Let me take a look at both and I will get back to you, once I have tried each method.  Thanks

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 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