split string

[gripper111]

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. The numbers can be 1-4 digits

I look forward to hearing some suggestions.

Thanks

 

[judgeh59]

check out the "split" command. It parses a string by the delimiter and puts it into an array that you can search and manipulate...


Have fun

Ernest

Be Alert, America needs more lerts

 

[Triacona]

Dear Gripper111,

I hope this helps:
try

Category: Left([file_name],13)

This will give you: home_&_gardenin a new field called Category in your query
and

Subject: Mid([file_name],15)

This will give you: 104_bbq_grills_&_accessories in a new field called Subject in your query




Thank you,

Kind regards

Triacona

 

[Andrzejek]

I would go with judgeh59's suggestion:

Dim s As String
Dim ary() As String
Dim i As Integer
Dim b As Integer

s = "home_&_garden_104_bbq_grills_&_accessories"

ary = Split(s, "_")

For i = LBound(ary) To UBound(ary)
    If IsNumeric(ary(i)) Then
        b = i
        Exit For
    End If
Next i

s = ""
For i = LBound(ary) To b - 1
    s = s & ary(i) & " "
Next i

Debug.Print "Category is " & s[green]
'home & garden[/green]

s = ""
For i = b To UBound(ary)
    s = s & ary(i) & " "
Next i

Debug.Print "Subject is " & s[green]
'104 bbq grills & accessories[/green]

Have fun.

---- Andy

 

[strongm]

Or

[blue]Option Explicit

Public Sub Example()
    Dim result As Variant
    result = CatSub("home_&_garden_104_bbq_grills_&_accessories")
    Debug.Print "Category: " & result(0)
    Debug.Print "Subject:  " & result(1)
End Sub

Public Function CatSub(strFilename As String) As Variant
    strFilename = Replace(strFilename, "_", " ")
    With CreateObject("vbscript.regexp")
        .Pattern = "(\d{1,4})"
        If .Test(strFilename) Then CatSub = Split(.Replace(strFilename, Chr$(254) & "$1"), Chr$(254))        '$1
    End With
End Function[/blue][/cat]

 

[gripper111]

Hi Strongm

I may need clarification of where to put the code and run it to be sure. I opened VB and created a new module and copied in your suggested code

Public Sub Example()
    Dim result As Variant
    result = CatSub("home_&_garden_104_bbq_grills_&_accessories")
    Debug.Print "Category: " & result(0)
    Debug.Print "Subject:  " & result(1)
End Sub

Public Function CatSub(strFilename As String) As Variant
    strFilename = Replace(strFilename, "_", " ")
    With CreateObject("vbscript.regexp")
        .Pattern = "(\d{1,4})"
        If .Test(strFilename) Then CatSub = Split(.Replace(strFilename, Chr$(254) & "$1"), Chr$(254)) '$1
    End With
        
End Function

I saw no action when I view my table. Be advised that the file name was just a small sampling of the file names to show the lay out. I added 5 more various file names examples below. The ### is the delimiter that splits the string


computers_&_consumer_electronics_4_anti-virus_software.csv
computers_&_consumer_electronics_1_accounting.csv
apparel_72_pearl_jewelry.csv
occasions_&_gifts_39_party_supplies.csv
jobs_&_education_24_foreign_language_certifications.csv


Thanks

 

[strongm]

Er ... I haven't written all your code for you, just a function that splits an input string exactly as per your stated requirements. The Example sub demonstrates exacty how call that function, and how to extract the two split values from the returned variant. I leave it as an exercise for the reader to do something with those two values ...