parse 7 digits after (, but only if numeric

[deb]

access 2010

I need to parse the data for the 7digit numbers within a string.
The digits will always be 7 in length. Some data in () are not numbers. Some do not have ending ).
example data:

Park smith (sam)
Park miller 23
WT 456 Bisa
Keepan (2345678)
Ington (3456789)
Untom (3245678

I need the 7 digits only an to be formatted as numeric.

2345678
3456789

I am the worst at parsing, need your expertise.

 

[Joe4]

Without VBA, I elected to do it in a two part process.

First, pull the 7 characters after the "(", like this (note, I named the field "Data" for this example):

Part1: IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")

Then, check to see if it is Numeric, and if it is, return it:

Part2: IIf(IsNumeric([Part1]),[Part1],"")

You could wrap it into one long statement if you like, though it will be a little long, messy, and redundant.
I think it is easier to work with broken into two.
Here is what one formula would look like:

MyNumber: IIf(IsNumeric(IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")),Mid([Data],InStr([Data],"(")+1,7),"")

 

[deb]

Works perfectly!!! Thanks so much

Without VBA, I elected to do it in a two part process.

First, pull the 7 characters after the "(", like this (note, I named the field "Data" for this example):

Part1: IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")

Then, check to see if it is Numeric, and if it is, return it:

Part2: IIf(IsNumeric([Part1]),[Part1],"")

You could wrap it into one long statement if you like, though it will be a little long, messy, and redundant.
I think it is easier to work with broken into two.
Here is what one formula would look like:

MyNumber: IIf(IsNumeric(IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")),Mid([Data],InStr([Data],"(")+1,7),"")

 

[Joe4]

You are welcome!

 

[deb]

I just noticed that some of my data actually has two sets of ().

The formula provide by Joe4 works great on the single set of () but when there are two it misses the numbers. I apologize that I did not notice this issue before.

the excellent code that Joe4 provided is:
MyNumber: IIf(IsNumeric(IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")),Mid([Data],InStr([Data],"(")+1,7),"")

How can I edit this to catch the data that has two ()s.

i.e. jasmine (jasi) (3298568)

Sorry about my goofup, should know my data better!!

 

[Joe4]

I think that formula would get really long and messy. I would not even attempt a built-in formula for that.
I would create my own function in VBA (a "User Defined Function" or "UDF") for that.
Are you open to such a solution?

 

[deb]

figured it out...
Part13: IIf(InStr([sTurbineSite],"(")>0,Mid([sTurbineSite],InStrRev([sTurbineSite],"(")+1,7),"")

used InstrRev

 

[Joe4]

figured it out...
Part13: IIf(InStr([sTurbineSite],"(")>0,Mid([sTurbineSite],InStrRev([sTurbineSite],"(")+1,7),"")

used InstrRev

Yes, that would work under the following assumption: If there are more than one set of parentheses, the numbers will always appear in the LAST set of parentheses.
If the numbers could appear in the first set, then that won't work in those instances.
I didn't want to make the assumption that they would always be in the last set.

If there are never more than two sets, and the numbers could appear in either one, you could do this without VBA in three steps:
- Part1: check the first set (like I posted)
- Part2: check the second set (like you posted)
- Part3: check to see if Part1 is numeric. If it is, return that. Otherwise return Part 2.