Extracting characters embedded in a string (1 Viewer)

peskywinnets · Jun 21, 2019

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.

Micron · Jun 21, 2019

Code:

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

Code:

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

June7 · Jun 21, 2019

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 · Jun 21, 2019

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

Micron · Jun 21, 2019

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 · Jun 21, 2019

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 · Jun 21, 2019

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 · Jun 21, 2019

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 · Jun 21, 2019

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.

Micron · Jun 21, 2019

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 · Jun 22, 2019

June7 said:
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.

June7 said:
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...

Code:

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

....therefore it won't error

Many thanks!

June7 · Jun 22, 2019

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

Extracting characters embedded in a string (1 Viewer)

peskywinnets

Registered User.

Micron

AWF VIP

June7

AWF VIP

peskywinnets

Registered User.

Micron

AWF VIP

June7

AWF VIP

peskywinnets

Registered User.

June7

AWF VIP

jdraw

Super Moderator

Micron

AWF VIP

peskywinnets

Registered User.

June7

AWF VIP

Similar threads

Users who are viewing this thread