Isolate text within a string

[mwheads]

Hi All

I would like to know how to isolate cetain text within a long string. e.g. If my full field is:
"CHEQUE DEPOSIT J C BLOGGS BAWGE123"

All I need to isolate in the query is the "BA" and the characters after that. All of our references start with a "BA" and the remaining is a definate 6 or 7 characters.
I.E. what I need is only the "BAWGE123"

Any ideas?
thanks,
Paul

 

[DrSimon]

Is the reference always at the end and/or does it have a fixed structure? If not you could have problems

OK not too difficult with either of these:
CHEQUE DEPOSIT J C BLOGGS BAWGE123
CHEQUE DEPOSIT BAWGE123 J C BLOGGS

But what would you do with this?
CHEQUE DEPOSIT J C BALOGGS BAWGE123
What is the difference between BALOGGS and BAWGE123?

Once you have an unambiguous answer, there are ways to help you.
Simon Rouse

 

[mwheads]

Hi Simon,

No, there is no fixed structure, it is the download of the company bank statement and the field is very random depending on what clients have entered as references.
Basically I need a way to isolate only the "BA" and the characters after that. This "new field" will be linked to our client table so any matches that are not exact will thrown out, and captured manually. The ones that do match will save everybody a lot of time.

Is there no kind of ....[FIND] "BA" and [COUNT] 6 or 7 further characters?

 

[DrSimon]

I was going to suggest using Instr(), but if you could have a line like I posed (CHEQUE DEPOSIT J C BALOGGS BAWGE123
) you'd have to have some sophisticated VBA code to pick out the correct string. Is the reference at least a certain number of alpha characters followed by digits? IF not you're on a hiding to nothing.

 

[mwheads]

thanks, Instr() has given me a few ideas, its all i've got at the moment. it will solve the problem in about 80% of the cases where a prior BA does not appear.

P.S. Yes, its always, [BA][3 letters][3 or 4 numbers]

 

[DrSimon]

The P.S. was what I wanted from you:

Here are 2 expressions
3Num: IIf([String] Like "*BA???###",Mid([String],InStr([String],"BA"),7+InStr([String],"BA")),"")

Will extract BAWGE123 from
CHEQUE DEPOSIT J C BLOGGS BAWGE123

4Num: IIf([String] Like "*BA???####",Mid([String],InStr([String],"BA"),8+InStr([String],"BA")),"")

Will extract BALOGGS BAWGE1234 from
CHEQUE DEPOSIT J C BALOGGS BAWGE1234

Simon Rouse