Trim to leave charters between charter |?

[yetirider]

Hi

In a field i have text in the format
XX|XXXXXX|TEST|X|XX|XXX|XXXXXXXXXXXXX

X represents text that may be in the fields and will vary in length.

I need to extract the text after the second | and before the third |
So in the example above I would just be left with "TEST".

Could someone help me trim this?

Many Thanks Phil

 

[jdraw]

See this link for info and example of the Split() function

 

[yetirider]

Thanks, not sure how to implement that.
In the mean time i've used InStr to count the number of charters up to the second | and the third |.
Just need a way so it correctly shows the text in between.

 

[June7]

Build a VBA custom function that utilizes array object and Split() function. The function could be called in query or textbox. Crude example:

Function getText(strString)
Dim LArray() As String
LArray = Split(strString, "|")
getText = LArray(2)
End Function

Call the function from query.
SELECT *, getText([fieldname]) FROM tablename;

 

[yetirider]

Build a VBA custom function that utilizes array object and Split() function. The function could be called in query or textbox. Crude example:

Function getText(strString)
Dim LArray() As String
LArray = Split(strString, "|")
getText = LArray(2)
End Function

Call the function from query.
SELECT *, getText([fieldname]) FROM tablename;

Thats great, understand that example.

Many Thanks Phil

 

[abq]

If you don't want to use VBA and just a formula, here is what you need:

Assuming "YETI" is the name of your field

mid(YETI,(instr((instr(YETI,"|")+1),YETI,"|")+1),(instr(instr((instr(YETI,"|")+1),YETI,"|")+1,YETI,"|")-instr((instr(YETI,"|")+1),YETI,"|")-1))

This will only work if you do not have a leading "|" in your string, and only extracts the 3rd group.

-abq-