Splitting columns with SPACE as delimiter

[CharlotteDK]

Hi,

I'm working in Access and I need to split a column looking like this:

1de dok 123456 receiver

I need to convert the column to 4 new columns.

I hope someone out there can help me :)

Charlotte

[sierra7]

Hi

You can write code to do this using the InStr() function to find the position of the first space, then slice off the first column using Left(), leaving a remaining string and repeat the operation 4 times....tedious but programmer love things like that!

I'm an engineer so it would take all day to make that work, so (especially if I only need to do it once) I would paste the column into WordPad and save it, then re-import it into Access when you can set the delimiter to a space.

I know this will put the column into a new table but it should be quite easy to manipulate in Access.

Best of luck

[Minion]

First off I'll give you the code to the function I use for splitting a variety of strings into seperate segments. The best part is it has built in delimeters ( ,/!|), but you can pass it a list and override these. I always recommend doing so to make sure you only split where you want to. The return product of this will be an array, which we'll handle next.

The parsing function is as follows:

Public Function parse(ByVal inString, Optional ByVal delimiters)
'Take a string, and return it as a one dimensional array
' of individual values as delimited by any of several
' characters. None of those characters are returned in
' the result. Provide a default list of delimiters, which
' should come from registry. But allow override.
 
    Dim delimitList, oneChar, aWord, codeCount
    Dim arrayCodes()
 
    If IsMissing(delimiters) Then
        'We should get these from Registry
        delimitList = " ,/!|"
'Characters recognized as delimiters
 
    Else
        delimitList = delimiters
'user can override if needed
 
    End If
    Dim i, j, k
    i = Len(inString)
    For j = 1 To i
'Read one character at a time
 
        oneChar = VBA.Strings.Mid(inString, j, 1)
        k = InStr(delimitList, oneChar)
'Is this one a delimiter?
        If k = 0 Then
            aWord = aWord & oneChar
'If is isn't, add to the current word
        End If
        If k <> 0 Or j = i Then
'If it is, or if we're finished
            If aWord > "" Then
                codeCount = codeCount + 1
                ReDim Preserve arrayCodes(codeCount)
                arrayCodes(codeCount) = aWord
'Save new word
                aWord = ""
            End If
        End If
    Next j
    parse = arrayCodes
'Return the array
End Function
 

To call and handle this function you'll need some code as follows:

Dim splitString()
 
splitString = parse(<<value>>," ")
 

Now you have an array of your values as split by the space. By using a loop command you can send each of the values to a variable, textbox, or other location. Just in case I've included a basic loop that would send the values to series of record sets.

Dim j, i as Integer
Dim rst as Recordset
 
Set rst = <<recordset definition here>>
 
   j = uBound(splitString)
 
  for i = 1 to j
     rst.fields(i) = splitString(i)
  next i
 

Hope this helps.

- Minion -