Extracting characters embedded in a string

[peskywinnets]

Any ideas re an elegant dependable way that I can extract the bit in red out of a text string...

7501243706 For Transaction: 7501021926DSPT21174531807

...in other words when the characters DSPT are found, get everything that follows....so from that long text string, I'd like to end up with DSPT21174531807

the only constant is the DSPT bit ....it probably will be in the same place each time but I can't be sure (hence I'm not sure I can just use a right() function ...ideally what I'd like to do is hunt through the string for the existence of DSPT if located (it won't always be present in the string being tested...only sometimes) get it & all that follows.

 

[Deleted member 147267]

mid("7501021926DSPT21174531807",instr(1,"7501021926DSPT21174531807","DSPT21174531807"))

If you have assigned the string to a variable, use that and it will be alot shorter.

Do you need help with the rest (e.g. DSPT not being there?)


EDIT - I make boo boo in paste

mid("7501021926DSPT21174531807",instr(1,"7501021926DSPT21174531807","DSPT"))

 

[June7]

Just search for "DSPT" but will error if not found so use IIf(). Expression in query:

IIf(fieldname LIKE "*DSPT*", Mid(fieldname, InStr(fieldname, "DSPT")), fieldname)

 

[peskywinnets]

Thanks...perhaps I should have been clearer, the numbers following on from the DSPT will be different each time

As it goes the text is a variable named Description, therefore I seek the final part of the solution you offered...

mid(Description,instr(1,Description,??))

EDIT: Just seen June7's post ...that looks promising...I'll give that a go

 

[Deleted member 147267]

hmmm, I missed the part where it was stated that this was sql, where you can use IIF. Post is in vba part of forum??

 

[June7]

If want to process in VBA, will be little more complicated because for IIf in VBA (or textbox), all parts must be able to evaluate, even if it is not part that returns value. So use If Then Else in VBA.

 

[peskywinnets]

To give some feedback/closure, June7's solution worked a charm ...

OriginalDisputeID = Mid(Description, InStr(Description, "DSPT")

I have not got a clue what's going on with the command (I always thought that with the MID() function that it was necessary to give the number of characters to be extracted - that command has gone right over my head! Sometimes there's no more space in my head to take on board this stuff...so I will just put this one down to "VBA fairydust" & accept it as is)

Many thanks to all

 

[June7]

The InStr() function returns a position number when substring is found so that provides the position to begin extraction. If length is not specified, Mid returns everything following that position.

You said some values will not have "DSPT" substring, in which case your expression should error.

Error occurs when InStr returns 0 as Mid cannot have 0 position. Here is version that will work in VBA or textbox:

Mid(x, IIf(InStr(x, "DSPT") = 0, 1, Instr(x, "DSPT")))

or

Mid(x, IIf(x LIKE "*DSPT*"), Instr(x, "DSPT"), 1)

 

[jdraw]

Hi Pesky,

You can always get some insight into vba commands/functions, their syntax and examples at
techontheNet Ms Access Functions alphabetically or M$oft Access Functions by Category. These are worth bookmarking.
Good luck with your project.

 

[Deleted member 147267]

Was going to point out in my next post that Description is a reserved word so I'll just pass it on regardless of that not happening.

 

[peskywinnets]

The InStr() function returns a position number when substring is found so that provides the position to begin extraction. If length is not specified, Mid returns everything following that position.

I was lying in bed last night & this conclusion popped into my head (it's not great to think about VBA when lying in bed!) ...thanks for the clarification.

You said some values will not have "DSPT" substring, in which case your expression should error.

the main body of code has an IF test, as follows...

      ElseIf InStr(Description, "DSPT") Then 
              OriginalDisputeID = Mid(Description, InStr(Description, "DSPT"))

....therefore it won't error

Many thanks!

 

[June7]

Okay, but post 8 shows one-line options that do not require If Then Else.

 

[DickyP]

This will be simple to resolve by using a RegEx if processing in VBA.

For example:

Public Function StripDSPT(pstrIn As String) As String
    Dim mtches As VBScript_RegExp_55.MatchCollection
    Static RE As New VBScript_RegExp_55.RegExp
   
    RE.Pattern = "DSPT\d{1,}"
 
    If RE.Test(pstrIn) Then
        Set mtches = RE.Execute(pstrIn)
        StripDSPT = mtches.Item(0)
    Else
        StripDSPT = vbNullString
    End If
   
End Function

Of course, whether this a good idea on not depends on circumstances. It is elegant and reliable.

 

[MarkK]

Debug.Print "DSPT" & Split("7501243706 For Transaction: 7501021926DSPT21174531807", "DSPT")(1)