parse 7 digits after (, but only if numeric

deb

Active Member
Joined
Feb 1, 2003
Messages
400
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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):
Code:
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:
Code:
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:
Code:
MyNumber: IIf(IsNumeric(IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")),Mid([Data],InStr([Data],"(")+1,7),"")
 
Last edited:
Upvote 0
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):
Code:
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:
Code:
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:
Code:
MyNumber: IIf(IsNumeric(IIf(InStr([Data],"(")>0,Mid([Data],InStr([Data],"(")+1,7),"")),Mid([Data],InStr([Data],"(")+1,7),"")
 
Upvote 0
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!!
 
Upvote 0
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?
 
Upvote 0
figured it out...
Part13: IIf(InStr([sTurbineSite],"(")>0,Mid([sTurbineSite],InStrRev([sTurbineSite],"(")+1,7),"")

used InstrRev
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top