By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Splitting columns with SPACE as delimiter

P: 1
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
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 446
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
Jan 8 '08 #2

Minion
Expert 100+
P: 108
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function parse(ByVal inString, Optional ByVal delimiters)
  2. 'Take a string, and return it as a one dimensional array
  3. ' of individual values as delimited by any of several
  4. ' characters. None of those characters are returned in
  5. ' the result. Provide a default list of delimiters, which
  6. ' should come from registry. But allow override.
  7.  
  8.     Dim delimitList, oneChar, aWord, codeCount
  9.     Dim arrayCodes()
  10.  
  11.     If IsMissing(delimiters) Then
  12.         'We should get these from Registry
  13.         delimitList = " ,/!|"
  14. 'Characters recognized as delimiters
  15.  
  16.     Else
  17.         delimitList = delimiters
  18. 'user can override if needed
  19.  
  20.     End If
  21.     Dim i, j, k
  22.     i = Len(inString)
  23.     For j = 1 To i
  24. 'Read one character at a time
  25.  
  26.         oneChar = VBA.Strings.Mid(inString, j, 1)
  27.         k = InStr(delimitList, oneChar)
  28. 'Is this one a delimiter?
  29.         If k = 0 Then
  30.             aWord = aWord & oneChar
  31. 'If is isn't, add to the current word
  32.         End If
  33.         If k <> 0 Or j = i Then
  34. 'If it is, or if we're finished
  35.             If aWord > "" Then
  36.                 codeCount = codeCount + 1
  37.                 ReDim Preserve arrayCodes(codeCount)
  38.                 arrayCodes(codeCount) = aWord
  39. 'Save new word
  40.                 aWord = ""
  41.             End If
  42.         End If
  43.     Next j
  44.     parse = arrayCodes
  45. 'Return the array
  46. End Function
  47.  
To call and handle this function you'll need some code as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim splitString()
  2.  
  3. splitString = parse(<<value>>," ")
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1. Dim j, i as Integer
  2. Dim rst as Recordset
  3.  
  4. Set rst = <<recordset definition here>>
  5.  
  6.    j = uBound(splitString)
  7.  
  8.   for i = 1 to j
  9.      rst.fields(i) = splitString(i)
  10.   next i
  11.  
Hope this helps.

- Minion -
Jan 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.