String Split

[gripper]

HI Members,
I have a table named “keyword”. This table is created by importing various excel sheets and in the process of doing so it places the name of the excel file in the “file_name” field. Here is a typical file name entry “home_&_garden_104_bbq_grills_&_accessories”

I have added two more fields “Category” and “Subject”

What I would like is a solution ideal of how I can keep the “file_name” field untouched but take the info and split it into two columns.

For instance using the above file name entry I would split it as follows:
For the “Category” it would have an entry of “Home & Garden” (prefer the underscores removed and replaced with a <space>)
For “Subject” it would have an entry of “104 bbq grills & accessories”.

The common delimiter (if that is the right term) of all the file names where I want the split is the Number. For instance the 104 will go to the Subject column and everything before is the Category column. This is consistent with all the entries.
I look forward to hearing some suggestions.
Thanks

 

[SydneyGeek]

I couldn't think of an obvious way to do this with string functions so I used a couple of custom functions.

Query:

SELECT tblDummy.DummyText, firstpart([dummytext]) AS Category, secondpart([dummytext]) AS Subject
FROM tblDummy;

Code (Alt + F11, Insert > Module, paste the code) :

Function SplitNumber(strIn As String) As Long
    Dim i As Integer
    
    For i = 1 To Len(strIn)
        If Mid(strIn, i, 1) Like "#" Then Exit For
    Next i
    SplitNumber = i
End Function
Function FirstPart(strIn As String) As String
    Dim lngSplit As Long
    lngSplit = SplitNumber(strIn)
    FirstPart = Trim(Replace(Left(strIn, lngSplit - 1), "_", " "))
End Function
Function SecondPart(strIn As String) As String
    Dim lngSplit As Long
    lngSplit = SplitNumber(strIn)
    SecondPart = Trim(Replace(Mid(strIn, lngSplit), "_", " "))
End Function

Denis