Extracting varying length string between two characters (1 Viewer)

DaniLucas

New member
Local time
Today, 00:26
Joined
May 31, 2016
Messages
6
Hi. I know I've done this before, but for the life of me, can't remember how. I need to be able to extract a string that will vary in length, that will always be between to characters. For example, these are document names:

02-20-00-AI-0035
02-2006-00-HG-0137
02-21-00-JW-1001
04-2115-01-AI-1013

I only need the portions (highlighted in red text) that will always be between the first and second "-" character. This portion of the document name is the "skid number". I need to be able to extract just the skid number from the document name.

If this can be done in an expression written into the design view of a query, that would be most ideal as I am not great with VBA at all.

Thank you!!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:26
Joined
Oct 29, 2018
Messages
21,358
Hi. Assuming the skid number always starts at the 4th character, then maybe you could try something like this:
Code:
Mid([FieldName],4,InStr(Mid([FieldName],4),"-")-1)
(untested)
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:26
Joined
Sep 21, 2011
Messages
14,048
How about using Split?
tt="02-2006-00-HG-0137"
? split(tt,"-") (1)
2006

I'd write a simple function to return the value.
 

DaniLucas

New member
Local time
Today, 00:26
Joined
May 31, 2016
Messages
6
Thank you all! TheDBGuy, your solution seems to work well. I am not sure if they'll ever change the first bit of the document name to be more than two characters in the future, but for now, it always starts with two characters before the first "-". Would there be a way to do this if they did change it, however?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:26
Joined
Oct 29, 2018
Messages
21,358
Thank you all! TheDBGuy, your solution seems to work well. I am not sure if they'll ever change the first bit of the document name to be more than two characters in the future, but for now, it always starts with two characters before the first "-". Would there be a way to do this if they did change it, however?
Yes, we can use the initial "-" I suppose. One way to do it is by using the Split() function like Gasman suggested earlier, but it will require some VBA code.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:26
Joined
Sep 21, 2011
Messages
14,048
Yes, use the Split function. As long as they do not move the locations of the skid number you should be OK.
In fact the function could also take the relevant part position, delimiter so it would just be changing the part number index number.

Code:
Function fnSkidNumber(strPart As String, strDelim As String, iIndex As Integer)
Dim strSplitArray() As String

strSplitArray = Split(strPart, strDelim)
fnSkidNumber = strSplitArray(iIndex)

End Function

Code:
? fnSkidNumber("02-2006-00-HG-0137","-",1)
2006
 
Last edited:

Users who are viewing this thread

Top Bottom