Extract text when it matches specific format

[newbiecoder]

Hello! I have a table named tblOMNIDBValidationExtract and I need to extract anything formatted as "L00000000" from column RECORD_KEY.The length of each record varies. I would like to extract the data to another column named ACC_NO in the same table. Here are some examples:

RECORD_KEY	ACC_NO
01A12345678201805252018050206807050	A12345678
20180430A98765432 7	A98765432

Any help would be greatly appreciated!

[Beetle]

Does the ACC_NO always begin with A, or do you consider the first occurrence of any alpha character to be the beginning of the ACC_NO? Also, can there be occurrences of alpha characters that are not related to the ACC_NO?

[newbiecoder]

The ACC_NO can start with any letter and contains any 8 numbers for a total of 9 characters in length. There may be an additional random letter in the string, but it doesn’t fit the pattern

[newbiecoder]

Can this be done using the expression builder?

[Micron]

I think not. If the position of the first alpha was a constant, you could use the Mid function alone. Otherwise, it will require a combination of functions. Problem can be, will the alpha characters ever be D or E? They can be interpreted as scientific notation.

[newbiecoder]

Unfortunately, there is a possibility that there could be an E or a D in the string. For example, "010EKDPRAA4B49249000 201804309" should return B49249000.

[newbiecoder]

Currently, I am manually using Mid functions to pull the ACC_NO. There are three mid functions I am using. The ACC_NO starts at either the 3rd, 9th, or 12th space (i.e. =Mid(RECORD_KEY,3,9) / =Mid(RECORD_KEY,9,9) / =Mid(RECORD_KEY,12,9)...). Is there vba code that can run the mid function and if it returns a value formatted correctly ("L00000000") then it puts the value in column ACC_NO? However, if the mid value returns an incorrectly formatted string then it will move on the next mid function until it finds the proper function to use.

[Micron]

Can it be said that if you start from the right and go left that the 1st letter will always be the starting point?

[newbiecoder]

Can it be said that if you start from the right and go left that the 1st letter will always be the starting point?

Unfortunately, the first letter read from the right isn’t always the starting point.

[Micron]

Then is there a constant at all? After reviewing the prior posts I can't tell. Maybe a sample set of one or two dozen would help us to discover a reliable pattern.

[newbiecoder]

Then is there a constant at all? After reviewing the prior posts I can't tell. Maybe a sample set of one or two dozen would help us to discover a reliable pattern.

I apologize for any confusion. I am currently away from my computer that has the data for an hour or so, but here are some examples. There is only really one pattern and that’s “L00000000”:

63628634A12345678538362 (Should show as A12345678)

GEB12345678 (Should show as B12345678)

EFG83632693C12345678EJ132 (Should show C12345678)

[newbiecoder]

In the third example you can see that there are two potential results just going off the formatting criteria (G83632693 and C12345678), however the correct result is the last possible combination of L000000000 which is C12345678.

[ssanfu]

PMFJI,

How do you know that "C12345678" is the correct result?
How is this determined?

[newbiecoder]

PMFJI,

How do you know that "C12345678" is the correct result?
How is this determined?

It’s a system generated report that formats the data like that. I’m sure that “C12345678” is the correct result because we manually pull the substring and compare it to another list later on in our process. It’s determined by being the last possible combination of L00000000 in the string.

[Micron]

So the approach might be to start from the right and in a loop For i = 1 To Len(str) where str is the value being examined, increment counter n looking for 8 successive integers. If not successful at any point, start n at 0 again and continue. When 8 are found, go left 1 more character and extract a total of 9 IF that character is alpha. ??
If 9th isn't alpha then carry on until 1 alpha is found and extract 9 from there??

EDIT: if that is accurate, you should post a db with a table of test data. It should contain a field for storing the derived value as well.
I don't mind helping out with complex problems, but I believe it's incumbent upon the poster to do some of the work.

PMFJI,

I always enjoy your input. Keep on jumping in