String handling

[Tcs]

I have a record I'm reading in:

Column : Customer ID ("CXLIB"."UT211AP""UTCSID")

I can grab the 'CXLIB', 'UT211AP' and 'UTCSID' by looking for the double quotes.
But I was wondering of there's an easy way to grab the 'CUSTOMER ID' from the
rec. Can I use something like the MID() function looking for the ':' and '('?
Any idea of what it would look like?

Thanks in advance,

Tom

 

[Tim Ferguson]

I have a record I'm reading in:

Column : Customer ID ("CXLIB"."UT211AP""UTCSID")

To be honest, I think I'd use the RegExp object and get all the bits in one
go.

Tim F

 

[John Spencer (MVP)]

If you are trying to do this in a query, you might use something this complex (UNTESTED)

Trim(Left(Mid(TheField,InStr(1,TheField,":")+1),Instr(Mid(TheField,InStr(1,TheField,"(")-1))))

VBA: (UNTESTED SNIPPET with no error handling)

Public Function fGetBack(TheField as String)

Dim strBack as String

strBack=Trim(Mid(TheField,Instr(1,TheField,":")+1))
strBack=Trim(Left(strBack,Instr(1,strBack,")")-1))
fGetBack = strBack
End Function

 

[Tcs]

VBA is my choice. Thanks a lot,

Tom

If you are trying to do this in a query, you might use something this complex (UNTESTED)

Trim(Left(Mid(TheField,InStr(1,TheField,":")+1),Instr(Mid(TheField,InStr(1,TheField,"(")-1))))

VBA: (UNTESTED SNIPPET with no error handling)

Public Function fGetBack(TheField as String)

Dim strBack as String

strBack=Trim(Mid(TheField,Instr(1,TheField,":")+1))
strBack=Trim(Left(strBack,Instr(1,strBack,")")-1))
fGetBack = strBack
End Function

 

[DickP]

Your previous respondent Tim Ferguson had the right idea - use Regular
Expressions (all you need in your code is a reference to 'Microsoft VBScript
Regular Expressions 5.5' - which you'll have if you are on Win XP/2003 or can
download from MS if you use ME/2000 etc, where you may not be on v5.6 of
windows Script Host) . The code will be simpler and MUCH quicker in the end.
The documentation of the RegExp object in the Windows Script Host 5.6 Help
is excellent and the effort involved in using Reg Expressions will be well
worth it.

Dick