Extracting parts of names

[Guest]

Hi,

Does anyone know how to extract "lysell, kent" from "lysell, kent(17)"? I
don't want the "(17)" part. This should work no matter what spacing is in the
name before the "(17)". For example, what if the text in a cell is
"lysell,kent(17)"? Or "lysell,kent M.(17)", etc.

Thanks in advance,

 

[Pete_UK]

Try this:

=LEFT(A1,LEN(A1)-4)

Hope this helps.

Pete

 

[Peo Sjoblom]

Is it always (17)?

=SUBSTITUTE(A2,"(17)","")

if not


=IF(ISNUMBER(FIND("(",A2)),TRIM(LEFT(A2,FIND("(",A2)-1)),A2)

 

[David Biddulph]

=LEFT(A1,FIND("(",A1)-1)

 

[Guest]

Thanks Peo!

What if the value that I want to extract is contained in the sheet tab name?
For example, what if "kent, lysell(17)" or any other name and number (within
the parenthesis) is actually the sheet tab name? How can I refer to this name
and have its value extracted using the formula that you kindly supplied me?

Cheers!

 

[Ron Rosenfeld]

Hi,

Does anyone know how to extract "lysell, kent" from "lysell, kent(17)"? I
don't want the "(17)" part. This should work no matter what spacing is in the
name before the "(17)". For example, what if the text in a cell is
"lysell,kent(17)"? Or "lysell,kent M.(17)", etc.

Thanks in advance,

=TRIM(LEFT(A1,FIND("(",A1)-1))
--ron

 

[Peo Sjoblom]

One way

=TRIM(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34),FIND("(",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34))-1))


the workbook has to be saved first