Return text between 2nd and 3rd hyphen

[yangya]

I've seen this answered before but it was always for the text between the first and second hyphen (I need the text that's between the second and third hyphen).


This is what I found that returns the text between first and second hyphen:
Mid([Barcode],InStr([Barcode],"-")+1,(InStr(InStr([Barcode],"-")+1,[Barcode],"-") -InStr([Barcode],"-"))-1)



My text string (sample) is like this:
11.5RT-10B53A-2233-050716-SP-55-105.0-125


What I need returned is 2233


Thanks for any help

 

[NauticalGent]

I would consider an Array using the hyphen to delimit the elements, then just use the elements you need, just remember arrays are zero based.

 

[yangya]

Not really following you. I thought just some of the things in the query statement would need to be slightly modified?

 

[NauticalGent]

Try this thread:

https://access-programmers.co.uk/forums/showthread.php?t=217471

 

[yangya]

Thank you NauticalGent for your help, but as a newbie I'm just not getting it.
Isn't there just simply a modification to:


Mid([Barcode],InStr([Barcode],"-")+1,(InStr(InStr([Barcode],"-")+1,[Barcode],"-") -InStr([Barcode],"-"))-1)


to get me what I need?

 

[NauticalGent]

None that I can think of. The MID function stops at the first character you search for, in this case the hyphen. I am not sure how you would instruct it to inform the first hyphen and move to the next.

However, a user defined function as is showcased in the thread could return the elements you are looking for and be called from a query. I am not at a place where I write one and test it first, it would take me an hour or two before I can put something together.

I will check back here then, if someone has not thrown you a life-line by then, I will see what I can do.

 

[isladogs]

It can be done using Mid & InStr but the expression will get very messy

Easier to use the Split function:

Function TestSplit()
    'This will get the text between the 2nd & 3rd hyphen
    Dim strText As String
    
    strText = "11.5RT-10B53A-2233-050716-SP-55-105.0-125"
    TestSplit = Split(strText, "-")(2)
    
    Debug.Print TestSplit

End Function

NOTE:
Split(strText, "-")(0) = 11.5RT
Split(strText, "-")(1) = 10B53A etc

 

[yangya]

Thank you ridders. Problem is that the strText will change on each record.


PS: I don't mind "messy" using Mid & InStr if it will just give me the answer. There's a need to just get the expression in a saved query. Thanks again.

 

[arnelgp]

I favor ridders code.

 

[NauticalGent]

As do I, and so will the OP once you show him how to use it in a query.

 

[isladogs]

@yangya

If the barcode field is the one you want to parse, then add an expression field in your query

Expr1: Split([Barcode],"-")(2)

Otherwise, post your query SQL and tell me which field contains the text you want to parse. I'll modify your query to include the split code and post back.

 

[June7]

Modifying Colin's code. Procedure goes in a general VBA module.

Function TextSplit(strText As String, intPart As Integer)
    Dim aryText As Variant
    aryText = Split(strText, "-")
    TextSplit = aryText(intPart)
End Function

Code assumes every record has a value and the values have same structure - actually, at least 2 hyphens.
Then expressions in query that call the function:
PartA: TextSplit([Barcode], 1)
PartB: TextSplit([Barcode], 2)

 

[NauticalGent]

Yangya,I attached a small db as an example on how to call this function from a query. Take it for a test drive!

 

[isladogs]

Yangya,I attached a small db as an example on how to call this function from a query. Take it for a test drive!

So small its invisible!

 

[yangya]

I thought I sent this reply in before, but here it is again:
Thank you all for your help. I finally got the jist of what I need to do (June7's answer - slightly tweaked from ridders code) did the trick.


Again, many thanks to all who replied!!!

 

[NauticalGent]

So small its invisible!

Yeah, that does pose a minor problem! For some reason the AWF screen does not render correctly at work so the option to upload does not show on screen.
I tried to cancel my input but it appears I submitted instead. A pity too because I was a little proud of it, but the OP is good to go so I have missed my shot at 15 min of fame.

 

[Gasman]

Nauticalgent,

I *think* I know what you will have done, but would still like to see it, to confirm?

 

[yangya]

Need to extend this post...
The issue I'm having now is when I try to import the data by using excel, I get the following error message "Undefined function 'TextSplit' in expression.

 

[isladogs]

Gasman
I just received this email from NG:

Colin,
Here is a copy of the DB GasMan asked about. I won't be home for another 6 hours. Would you mind posting it on my behalf?

See attached

yangya

The issue I'm having now is when I try to import the data by using excel, I get the following error message "Undefined function 'TextSplit' in expression.

Linked Excel files can't be edited from Access
Suggest you import the Excel spreadsheet to a temporary table in Access, then run the query or function on the temp table

 

[Gasman]

@ridders
Thank you Colin.

@NauticalGent
There was no urgency, so thank you for the extra effort in getting it posted.