pull in target from string

[deb]

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

 

[Legacy 436357]

Re: pull enterget from string

Try this:

Excel Workbook
	A	B
1	Mix	Number Only
2	The brown rabbit (3)	3
3	The blue ox (10)	10
Sheet1

 

[DRSteele]

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
	A	B	C	D	E
1	text string	left ( location	right ) location	integer between ( and )	integer between ( and ) singlecell
2	The brown rabbit (3)	18	20	3	3
3	The blue ox (10)	13	16	10	10
4	The brown veal 99
5	the tan fois grois ()	20	21	0	0
Sheet14

Cell Formulas
Range	Formula
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]

 

[Joe4]

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.

 

[Joe4]

Re: pull enterget from string

Assuming that every record has parentheses in it, if your field name was "Field1", the calculation would look like:

Mid([Field1],InStr([Field1],"(")+1,InStr([Field1],")")-InStr([Field1],"(")-1)+0

 

[deb]

Re: pull enterget from string

Thank you, this works perfectly

 

[Joe4]

Re: pull enterget from string

You are welcome.