Solved Split Function Using Multiple Criteria

[EzGoingKev]

Good morning.

I have text in a table like this:

Make
ALFA ROMEO
FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY
HINO (MEDIUM DUTY)
MERCEDES-BENZ
NISSAN

Some data has the make and " - " and then extra info. Some has the extra info in parentheses. Some does not have any extra info that needs to be removed.

I need it like this:

Make
ALFA ROMEO
FREIGHTLINER
HINO
MERCEDES-BENZ
NISSAN

I googled this and tried using Array but received errors. I wrote this code here:

Public Function GetMakeName(strIn As Variant)

    strIn = strIn & ""
 
On Error GoTo ErrorHandler

    Dim text(1 To 2) As String
 
    text(1) = " - "
    text(2) = " ("
 
    Dim item As Variant
 
    For Each item In text
 
        GetMakeName = Split(strIn, item)(0)
 
    Next item
 
Error_Exit:
    Exit Function

ErrorHandler:
    MsgBox Error$
    Resume Error_Exit

End Function

It runs without errors but gives me this as result:

Expr1
ALFA ROMEO
FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY
HINO
MERCEDES-BENZ
NISSAN

It is only doing the text2 from the code but not the text1.

Would someone please advise on what I need to change to get this to work?

 

[theDBguy]

What happens if the data has more than two "-" in it? What do you want to get back from it? For example:

FREIGHTLINER - TRUCKS - 5 TON - MEDIUM/HEAVY DUTY

 

[EzGoingKev]

What happens if the data has more than two "-" in it? What do you want to get back from it? For example:

FREIGHTLINER - TRUCKS - 5 TON - MEDIUM/HEAVY DUTY

Using your example I would just want "Freightliner"

I just went through the data sets that I have samples and it is the two " - ". I did not see anything with more than the two " - ". I do not have all the data sets though so I cannot rule it out. The only thing that is consistent at my company is no two things are ever done the same. If they were it was by accident.

I used " - " with the spaces because there is Mercedes-Benz. If I used just the "-" I would only get Mercedes.

 

[plog]

From your first post:

FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY
should resolve to FREIGHTLINER - TRUCKS

From your latest post:
FREIGHTLINER - TRUCKS - 5 TON - MEDIUM/HEAVY DUTY
should resolve to "FREIGTHLINER"

Is that accurate? The key to this is writing your rules in plain all encompassing english. "I want all characters before the second dash", I want all characters before the last dash", etc. With the 2 examples above you've greatly increased the complexity of your rules.

My suggestion to you is to try and right your rules out as clearly and concisely as you can in english. With that you can then start to code a function in VBA to implement them.

Also, don't use just the 2 examples above as the only place to base your rules. Make sure what you want to happen with HINO (MEDIUM DUTY) is covered as well.

 

[EzGoingKev]

Sorry, I did not delete it all in my OP. I fixed it and added in Mercedes-Benz.

I want all the characters left of:
- the first " - " which is space, a dash, and then another space. FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY resolves to FREIGHTLINER.
- the first " (" which is a space and a left parentheses. HINO (MEDIUM DUTY) resolves to HINO.

If the data does not contain either the " - " or " (" then no resolution is required. NISSAN would remain Nissan.

Mercedes-Benz would remain Mercedes-Benz.

 

[theDBguy]

Sorry, I did not delete it all in my OP. I fixed it and added in Mercedes-Benz.

I want all the characters left of:
- the first " - " which is space, a dash, and then another space. FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY resolves to FREIGHTLINER.
- the first " (" which is a space and a left parentheses. HINO (MEDIUM DUTY) resolves to HINO.

If the data does not contain either the " - " or " (" then no resolution is required. NISSAN would remain Nissan.

Mercedes-Benz would remain Mercedes-Benz.

To me, that translates to "I want all the characters on the left up to the first space character." Would that be an accurate interpretation? If so, you could use something like:

IIf(InStr([FieldName]," ")>0, Left([FieldName], InStr([FieldName]," ")-1), [FieldName])

Hope that helps...

 

[EzGoingKev]

To me, that translates to "I want all the characters on the left up to the first space character." Would that be an accurate interpretation? If so, you could use something like:

IIf(InStr([FieldName]," ")>0, Left([FieldName], InStr([FieldName]," ")-1), [FieldName])

Hope that helps...

No because I have makes like Alfa Romeo, John Deere, etc that would split them.

I specifically need it for " - " and " (". And just in case something else came up I could add in a Text(3) to my code.

 

[plog]

I want all the characters left of:
- the first " - " which is space, a dash, and then another space. FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY resolves to FREIGHTLINER.
- the first " (" which is a space and a left parentheses. HINO (MEDIUM DUTY) resolves to HINO.

If the data does not contain either the " - " or " (" then no resolution is required. NISSAN would remain Nissan.

Mercedes-Benz would remain Mercedes-Benz.

This is good and Split() is not the way to go. The way to do it is with IIF statements to test for the presence of your delimiters ( dash and parenthesis). You do that with the InStr function:

MS Access: InStr Function

This MSAccess tutorial explains how to use the Access Instr function with syntax and examples. The Microsoft Access Instr function returns the position of the first occurrence of a string in another string.

www.techonthenet.com

It finds the position of a substring (or character) in a string. Then inside that you can go about extracting just the part you want using the Mid() function:

MS Access: Mid Function

This MSAccess tutorial explains how to use the Access Mid function with syntax and examples. The Microsoft Access Mid function extracts a substring from a string (starting at any position).

www.techonthenet.com

 

[EzGoingKev]

The first data set I worked with had the FREIGHTLINER - TRUCKS - MEDIUM / HEAVY DUTY so I used:

IIf([Make] Like "* - *",Left([Make],InStr([Make]," - ")-1),[Make])

That worked fine. My boss asked me to see if it would work on another data set. That data set has the HINO (MEDIUM DUTY). I do not believe you can use multiple criteria in the InStr part of the statement so I figured writing a custom function using the Split command would work better than using multiple iif statements. Especially if a new variable comes up.

 

[jdraw]

For consideration:

OneLine: IIf(InStr([MAKE]," - ")>0,Left([MAKE],InStr([MAKE]," - ")),IIf(InStr([MAKE]," (")>0,Left([MAKE],InStr([MAKE]," (")),[make]))

Function

Function truckmake(MAKE As Variant) As String
    Dim a As Variant
    If IsMissing(MAKE) Then Exit Function

    a = InStr(MAKE, " - ")
    If a > 0 Then
        truckmake = Left(MAKE, a)
        Exit Function
    End If
    a = InStr(MAKE, " (")
    If a > 0 Then
        truckmake = Left(MAKE, a)
        Exit Function
    Else
        truckmake = MAKE
    End If
End Function

 

[EzGoingKev]

That worked. Thanks.

 

[jdraw]

You are welcome. Happy to help.

 

[arnelgp]

you can also create a Table for the Master list of all Makers (tblMaker in the sample).
then based on this table you create a function that will check if the Make is in the record
of tblMaker.
Note Volvo is not in tblMaker so in the query (Query1), the Maker is "?".
if you see "?" in the query, you add Volvo to tblMaker and Refresh the query.

 

[EzGoingKev]

I thought about a master make table.

The db will be used by other people so I have no control over making sure a make master table is updated.

 

[arnelgp]

you use what is best for you.
who knows, in the future, maybe somebody will find use to the master make table approach.