UCS_Staff
asked on
Text to Columns in Access
I preface this question by clarifying that I already know how to accomplish this task in Excel, Word, TextPad etc. I want to know if/how this can be done directly in MS Access to avoid the whole export-parse-import process.
The "Original POST" column contains data that is grouped using dashes (-). Some of the cells have 1 dash between two values (GATE2-001), others have 2 dashes separating 3 values (51-AB-492), and still others have no dashes and just a single value (0010)
What I need to do is to split this single column into 3 columns, RIGHT JUSTIFIED, using the dash as a delimiter. See example below. Is this possible in Access? If so, what is the syntax? (the spacing of this sample isn't great, please view the attached file for a better look)
Original POST Resulting POST NewCol 1 NewCol 2
GATE-001 GATE 001
GATE-002 GATE 002
GATE-003 GATE 003
GATE-004 GATE 004
GATE-005 GATE 005
51-AB-492 51 AB 492
51-AB-493 51 AB 493
51-AB-494 51 AB 494
51-AB-495 51 AB 495
51-AB-496 51 AB 496
51-AB-497 51 AB 497
00010 00010
00011 00011
00012 00012
00013 00013
00014 00014
00015 00015
00016 00016
Text-to-Columns-Example.xls
The "Original POST" column contains data that is grouped using dashes (-). Some of the cells have 1 dash between two values (GATE2-001), others have 2 dashes separating 3 values (51-AB-492), and still others have no dashes and just a single value (0010)
What I need to do is to split this single column into 3 columns, RIGHT JUSTIFIED, using the dash as a delimiter. See example below. Is this possible in Access? If so, what is the syntax? (the spacing of this sample isn't great, please view the attached file for a better look)
Original POST Resulting POST NewCol 1 NewCol 2
GATE-001 GATE 001
GATE-002 GATE 002
GATE-003 GATE 003
GATE-004 GATE 004
GATE-005 GATE 005
51-AB-492 51 AB 492
51-AB-493 51 AB 493
51-AB-494 51 AB 494
51-AB-495 51 AB 495
51-AB-496 51 AB 496
51-AB-497 51 AB 497
00010 00010
00011 00011
00012 00012
00013 00013
00014 00014
00015 00015
00016 00016
Text-to-Columns-Example.xls
To test this, create a test func
eg
Public Sub TestGetPart()
Debug.Print "COLUMN 1", "COLUMN 2", "COLUMN 3"
Debug.Print GetPart("51-AB-492", 1), GetPart("51-AB-492", 2), GetPart("51-AB-492", 3)
Debug.Print GetPart("GATE-001 ", 1), GetPart("GATE-001 ", 2), GetPart("GATE-001", 3)
Debug.Print GetPart("00012", 1), GetPart("00012", 2), GetPart("00012", 3)
End Sub
and run this (click anywhere in code and hit F5) then check results in immediate window. It looks like this
COLUMN 1 COLUMN 2 COLUMN 3
51 AB 492
GATE 001
00012
eg
Public Sub TestGetPart()
Debug.Print "COLUMN 1", "COLUMN 2", "COLUMN 3"
Debug.Print GetPart("51-AB-492", 1), GetPart("51-AB-492", 2), GetPart("51-AB-492", 3)
Debug.Print GetPart("GATE-001 ", 1), GetPart("GATE-001 ", 2), GetPart("GATE-001", 3)
Debug.Print GetPart("00012", 1), GetPart("00012", 2), GetPart("00012", 3)
End Sub
and run this (click anywhere in code and hit F5) then check results in immediate window. It looks like this
COLUMN 1 COLUMN 2 COLUMN 3
51 AB 492
GATE 001
00012
ASKER
I'd like to do it in an Access query, if possible.
Along this line, I've copied your sample into a new Access query, and it gives me a syntax error (Missing operator) around the 'AS [Resulting Post]' section. Any ideas?
Along this line, I've copied your sample into a new Access query, and it gives me a syntax error (Missing operator) around the 'AS [Resulting Post]' section. Any ideas?
Sorry about that. I had a rogue opening ( and one missing also
corrected sql
select [Original POST], GetPart([Original POST],1) AS [Resulting POST], GetPart([Original POST],2) AS NewCol1, GetPart([Original POST],3) AS NewCol3
from mytable
change mytable to your tablename obviously
corrected sql
select [Original POST], GetPart([Original POST],1) AS [Resulting POST], GetPart([Original POST],2) AS NewCol1, GetPart([Original POST],3) AS NewCol3
from mytable
change mytable to your tablename obviously
Original POST Resulting POST NewCol1 NewCol3
51-AB-492 51 AB 492
GATE-001 GATE 001
00012 00012
51-AB-492 51 AB 492
GATE-001 GATE 001
00012 00012
man, display is messed up.
With regards to a access query, it probably can be done but will be full of iif's probably, a little messy. Thats why I prefer a easier to read and maintain solution by using udf's.
With regards to a access query, it probably can be done but will be full of iif's probably, a little messy. Thats why I prefer a easier to read and maintain solution by using udf's.
ASKER
I misunderstood before. I realize now that you were saying to use the the query in conjunction with the VB code. I was trying to use it independently.
It only takes a couple of minutes to export the table to Excel, split the columns and re-import into Access. I was hoping to speed things up by running a query directly in Access, but if that takes longer than my original plan, it's not worth it.
I appreciate your time, and that you didn't make me feel like an idiot, as usually happens here:-)
It only takes a couple of minutes to export the table to Excel, split the columns and re-import into Access. I was hoping to speed things up by running a query directly in Access, but if that takes longer than my original plan, it's not worth it.
I appreciate your time, and that you didn't make me feel like an idiot, as usually happens here:-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
add this to a module and save it (ensure module name not the same as func name)
Public Function GetPart(ByVal sSource As String, ByVal iPos As Integer) As String
Dim sWords() As String
If InStr(1, sSource, "-") > 0 Then
If InStrRev(sSource, "-") = InStr(1, sSource, "-") Then
sSource = "-" & sSource
End If
Else
sSource = "--" & sSource
End If
sWords = Split(sSource, "-")
If iPos > UBound(sWords) + 1 Then
GetPart = ""
Else
GetPart = sWords(iPos - 1)
End If
End Function
Now in your query do this
select ([Original POST], GetPart([Original POST],1) AS [Resulting Post], GetPart[Original POST],2) AS NewCol1, GetPart([Original POST],3) AS NewCol3
from mytable