# pull in target from string

#### deb

##### Active Member
I have a field that is a string but at the end has (2) or other number.
The string lengths vary. I need the integer as it's own field.

ie
The brown rabbit (3)
The blue ox (10)

new field
3
10

thanks

### 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.
Re: pull enterget from string

Try this:

Excel Workbook
AB
1MixNumber Only
2The brown rabbit (3)3
3The blue ox (10)10
Sheet1

Re: pull enterget from string

I don't know what an enterget is but this might be what you seek. This puts the integer from each string in its (not "it's") own field. There can be only one instance of ( and of ) in the string. I added sample data for testing purposes.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Book1
ABCDE
1text stringleft ( locationright ) locationinteger between ( and )integer between ( and ) singlecell
2The brown rabbit (3)182033
3The blue ox (10)13161010
4The brown veal 99
5the tan fois grois ()202100
Sheet14
Cell Formulas
RangeFormula
B2=IFERROR(SEARCH("(",A2),"")
C2=IFERROR(SEARCH(")",A2),"")
D2=IFERROR(NUMBERVALUE(MID(A2,B2+1,C2-B2-1)),"")
E2=IFERROR(NUMBERVALUE(MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1)),"")
[/FONT]

Re: pull enterget from string

It looks like this question was posted in the Access forum, but both answers provided are Excel answers.

deb,
Are you looking for a strictly calculated field solution, or is it acceptable to create a User Defined Function using VBA. Then you would be able to use that function like any other Access function.

Re: pull enterget from string

Assuming that every record has parentheses in it, if your field name was "Field1", the calculation would look like:
Code:
``Mid([Field1],InStr([Field1],"(")+1,InStr([Field1],")")-InStr([Field1],"(")-1)+0``

Re: pull enterget from string

Thank you, this works perfectly

Re: pull enterget from string

You are welcome.

Replies
8
Views
115
Replies
3
Views
262
Replies
9
Views
221
Replies
1
Views
278
Replies
2
Views
243

1,203,115
Messages
6,053,593
Members
444,674
Latest member
DWriter9

### 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.

### Which adblocker are you using?

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

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