using mid and instr

[David]

Hello all,
I have a field titled Subject that will have a value CROps ID# in it.
Immediately following this value there will be numbers. Sometime 3 digits,
sometime 4 or 5.... then a space and the rest of the field.

How can I make this statement more dynamic so it will pull back just the
numbers between CROpS ID (plus 10) and CROpS ID (plus 10) to a space?

Mid([Subject],InStr([Subject],"CROpS
ID")+10,Len([Subject])-InStr([Subject],"CROpS ID")-15)

Thank you,
David

 

[Lee San Diego]

I think you want to use Instr to find the position of the space character. I
would say more but I am not sure what the general pattern is of the datas in
this field. For example, why the "+10"?

Is it this crops id that is a fixed 10 characters in length? Me confused.

 

[David]

Me too.. lol

The field will have the string of "CROpS ID# " followed by the id number..
then more text.

Sample1:
this is a test for CROpS ID# 1234 for this newsgroup.
Desired Result:
1234


Sample2:
this is a test for CROpS ID# 123 for this newsgroup.
Desired Result:
123

I think you want to use Instr to find the position of the space character. I
would say more but I am not sure what the general pattern is of the datas in
this field. For example, why the "+10"?

Is it this crops id that is a fixed 10 characters in length? Me confused.

Hello all,
I have a field titled Subject that will have a value CROps ID# in it.
Immediately following this value there will be numbers. Sometime 3 digits,
sometime 4 or 5.... then a space and the rest of the field.

How can I make this statement more dynamic so it will pull back just the
numbers between CROpS ID (plus 10) and CROpS ID (plus 10) to a space?

Mid([Subject],InStr([Subject],"CROpS
ID")+10,Len([Subject])-InStr([Subject],"CROpS ID")-15)

Thank you,
David

 

[John Spencer]

Formula to get the number and all the trailing junk
Mid(Subject,Instr(1,Subject,"Crops ID" + 10))

Formula to get everything in X before the space
LEFT(X,Instr(1,X," ")-1)

Replacing X with the string that gets the number and all the trailing junk.


LEFT(Mid(Subject,Instr(1,Subject,"Crops ID" + 10)),
Instr(1,Mid(Subject,Instr(1,Subject,"Crops ID" + 10))," ")-1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[David]

Thank you John...
I had to modify it slightly, but it worked like a charm. Here is what I
ended up with.

Left(Mid([Subject],InStr(1,[Subject],"Crops
ID")+10),InStr(1,Mid([Subject],InStr(1,[Subject],"Crops ID")+10)," ")-1)

Formula to get the number and all the trailing junk
Mid(Subject,Instr(1,Subject,"Crops ID" + 10))

Formula to get everything in X before the space
LEFT(X,Instr(1,X," ")-1)

Replacing X with the string that gets the number and all the trailing junk.


LEFT(Mid(Subject,Instr(1,Subject,"Crops ID" + 10)),
Instr(1,Mid(Subject,Instr(1,Subject,"Crops ID" + 10))," ")-1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello all,
I have a field titled Subject that will have a value CROps ID# in it.
Immediately following this value there will be numbers. Sometime 3 digits,
sometime 4 or 5.... then a space and the rest of the field.

How can I make this statement more dynamic so it will pull back just the
numbers between CROpS ID (plus 10) and CROpS ID (plus 10) to a space?

Mid([Subject],InStr([Subject],"CROpS
ID")+10,Len([Subject])-InStr([Subject],"CROpS ID")-15)

Thank you,
David

 

[John W. Vinson]

Hello all,
I have a field titled Subject that will have a value CROps ID# in it.
Immediately following this value there will be numbers. Sometime 3 digits,
sometime 4 or 5.... then a space and the rest of the field.

One sneaky way is to use the Val() function, which extracts the numeric
portion of the beginning of a string:

Val(Mid([Subject],InStr([Subject],"CROpS ID")+10))


John W. Vinson [MVP]

 

[Mark Burns]

Spaces are tricky things to hinge your string search on.
I'd go with the "#" sign instead:
trim(Mid([Subject],InStr([Subject],"#") + 1))
gets the rest of the stuff after the # dropping any leading spaces (so even
if it's non-numeric, you'll get it all - cuz you never know when they're
going to slip a "# 2A453" value in on you in the future...)

wrote:

sometime 4 or 5.... then a space and the rest of the field.

One sneaky way is to use the Val() function, which extracts the numeric
portion of the beginning of a string:

Val(Mid([Subject],InStr([Subject],"CROpS ID")+10))


John W. Vinson [MVP]