INSTR

[Chris]

Hi,
I have a table of approximately 1400 rows of text. I am trying to break
the text into a few fields. The substring that I am trying to break by
consists of 2 or 3 letters, followed by 4 numbers. I don't know what the
letters or numbers are, just that it will be 2 or 3 letters followed by 4
numbers.
Is there someway that I can use INSTR with wildcard characters to find
the location of this substring?

Thanks.

 

[kingston via AccessMonster.com]

Try this:

NumbersPortion: Right([Field],4)
LettersPortion: Left([Field],Len([Field])-Len(CStr(Right([Field],4))))
The location of the break is simply Len(LettersPortion)

 

[Chris]

Hi,
Thanks for the reply. Now I realize how poorly I phrased my question.
Here is an example of the text strings that I have to work with:

Premium Bill Post BAP0300 Billing & AR Process D

I would like to have 3 fields,
Premium Bill Post
BAP0300
Billing & AR Process D

It's the middle field that can consist of 2 or 3 letters, followed by 4
numbers.
Hope this makes it a little more clear.

Try this:

NumbersPortion: Right([Field],4)
LettersPortion: Left([Field],Len([Field])-Len(CStr(Right([Field],4))))
The location of the break is simply Len(LettersPortion)

Hi,
I have a table of approximately 1400 rows of text. I am trying to break
the text into a few fields. The substring that I am trying to break by
consists of 2 or 3 letters, followed by 4 numbers. I don't know what the
letters or numbers are, just that it will be 2 or 3 letters followed by 4
numbers.
Is there someway that I can use INSTR with wildcard characters to find
the location of this substring?

Thanks.

 

[kingston via AccessMonster.com]

AFAIK, Instr() doesn't work with wildcards. I think you'll need to write a
custom function. Here are some suggestions:

Loop and look for text chunks: Chunk = Mid([String],1,Instr([String]," "))
If Chunk Not Like "*#### " then concatenate these pieces for the first part.
If Chunk Like "*#### " then you've got your middle part.
Reduce the string by used chunks: [String] = Mid([String],Len(Chunk)+1)
What's left over is your third part. HTH

Hi,
Thanks for the reply. Now I realize how poorly I phrased my question.
Here is an example of the text strings that I have to work with:

Premium Bill Post BAP0300 Billing & AR Process D

I would like to have 3 fields,
Premium Bill Post
BAP0300
Billing & AR Process D

It's the middle field that can consist of 2 or 3 letters, followed by 4
numbers.
Hope this makes it a little more clear.

Try this:

[quoted text clipped - 12 lines]

 

[John Spencer]

If your string always has spaces as separators you could write a custom
function using split and then step through the array looking for an element
that is like [A-z][A-Z]### or like [A-z][A-z][A-Z]###. With that you
should be able to parse your text into three parts.

Dirk Goldgar should be popping in with a plug for RegEx right about now.

Hi,
Thanks for the reply. Now I realize how poorly I phrased my question.
Here is an example of the text strings that I have to work with:

Premium Bill Post BAP0300 Billing & AR Process D

I would like to have 3 fields,
Premium Bill Post
BAP0300
Billing & AR Process D

It's the middle field that can consist of 2 or 3 letters, followed by 4
numbers.
Hope this makes it a little more clear.

Try this:

NumbersPortion: Right([Field],4)
LettersPortion: Left([Field],Len([Field])-Len(CStr(Right([Field],4))))
The location of the break is simply Len(LettersPortion)

Hi,
I have a table of approximately 1400 rows of text. I am trying to
break
the text into a few fields. The substring that I am trying to break by
consists of 2 or 3 letters, followed by 4 numbers. I don't know what
the
letters or numbers are, just that it will be 2 or 3 letters followed by
4
numbers.
Is there someway that I can use INSTR with wildcard characters to
find
the location of this substring?

Thanks.