Find/Mid Formula

[pauldaddyadams]

Hi,

I need a formula created that will enable me to find a number within a text and then report the number in another cell.

Example texts are:

"unplanned absences (not work related) (8)" and "Working days (YTD) (53)"

I was thinking of doing a find/mid formula but I am unsure if this would be best and how to do it.

I would want it to find the number 8 or 53 in the text examples above

Any suggestions?

[Andy Pope]

Is the number always within the second set of brackets or the last set of brackets?

[sweep]

Hi,

There are various methods of doing this:

http://www.excelforum.com/showthread.php?t=630231

[pauldaddyadams]

Hi, Thanks for the quick reply.

The number will always be in the last brackets.

Some texts only have one set of brackets with the numbers between 1 and four digits long.

example texts are:
FTE's at the end of the month (654)
Leavers (727)
worked in the month (include everyone) (2)
Value (1116)

So using the above I would want the following results if possible:
654
727
2
1116

Hope this helps?

[Andy Pope]

Assuming original text in B2 one way would be

=MID(B2,FIND("^",SUBSTITUTE(B2,"(","^",LEN(B2)-LEN(SUBSTITUTE(B2,"(",""))))+1,FIND("^",SUBSTITUTE(B2,")","^",LEN(B2)-LEN(SUBSTITUTE(B2,"(",""))))-FIND("^",SUBSTITUTE(B2,"(","^",LEN(B2)-LEN(SUBSTITUTE(B2,"(",""))))-1)

[pauldaddyadams]

Hi,

That worked a treat, it done excatley what I wanted it to thanks.

Is there any way it can return the result as a number though rather than selecting it all, copying it, pasting as a value and then converting it to a number?

The reason being is i am using a Vlookup and it needs it to be a number.

[Andy Pope]

Wrap the VALUE formula around it

[martindwilson]

you could try

Please Login or Register  to view this content.

[Andy Pope]

Hi martin,
whilst your formula is certainly a lot shorter and easier to understant note that 1 of the first example cases will fail.

"Working days (YTD) (53)"

[pauldaddyadams]

Hi,

Just wanted to say thanks for your help Andy!! The formula worked Perfectly

[martindwilson]

yep i see that, my fault,humble pie,dindt notice Working days (YTD) (53) from original post!

[daddylonglegs]

Some texts only have one set of brackets with the numbers between 1 and four digits long.

example texts are:
FTE's at the end of the month (654)
Leavers (727)
worked in the month (include everyone) (2)
Value (1116)

All these examples have the number in brackets right at the end. If that's always the case try this formula to extract the number when data's in A1

=LOOKUP(10^4,-RIGHT(A1,{3,4,5,6}))

This formula should return a number within the last brackets, anywhere in the text....

=LOOKUP(10^4,MID(A1,LOOKUP(2^15,FIND("(",A1,ROW( INDIRECT("1:"&LEN(A1)))))+1,{1,2,3,4})+0)