Parsing character separated string into individual components

S

Sharon

I have a field that was copied and pasted into an access database. This
field needs to be separated into separate fields, which are separated by
commas. I have searched and found an article on The Access Web authored by
Dev Ashish and it appears to be exactly what I need. The problem is I am not
sure where and how to implement this code. Can someone instruct me on the
use of this code to separate fields? Thanks.
 
P

pietlinden

Sharon said:
I have a field that was copied and pasted into an access database. This
field needs to be separated into separate fields, which are separated by
commas. I have searched and found an article on The Access Web authored by
Dev Ashish and it appears to be exactly what I need. The problem is I am not
sure where and how to implement this code. Can someone instruct me on the
use of this code to separate fields? Thanks.
Click to expand...

to which article were you referring?

The easiest way to split this is to use the Split function. then you
can loop through the array and write the contents somewhere.
 
S

Sharon

Below is the article by Dev Ashish. The problem is that the number of
characters could be different for each record. I want it to separate at the
commas.


Strings: Parsing character separated string into individual components
Author(s)
Dev Ashish


(Q) I have string which contains values separated by a comma/colon/semi
colon/space. How can I extract each value from that string?

(A) You can use these two functions provided by Microsoft to retrieve each
value. Note that the functions are written for comma separated values but can
easily be modified to work with any other character. Use the Sub Test as an
example

'******************* Code Start ****************
Function CountCSWords(ByVal s) As Integer
'Counts the words in a string that are separated by commas.
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, ",")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, ",")
Loop
CountCSWords = WC
End Function

Function GetCSWord(ByVal s, Indx As Integer)
'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer

WC = CountCSWords(s)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, ",") + 1
Next Count
EPos = InStr(SPos, s, ",") - 1
If EPos <= 0 Then EPos = Len(s)
GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function

Sub Test()

Dim strAString As String
Dim I As Integer
Dim intCnt As Integer

strAString = "This,calls,the,two,functions,listed,above"

'Find out how many comma separated words
'are present
intCnt = CountCSWords(strAString)


'Now call the other function to retrieve each one in turn
For I = 1 To intCnt
Debug.Print GetCSWord(strAString, I)
Next
End Sub
'******************* Code End ****************
 
Top