Extracting parts of names

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
Is it always (17)?

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

if not


=IF(ISNUMBER(FIND("(",A2)),TRIM(LEFT(A2,FIND("(",A2)-1)),A2)
 
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!
 
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,
Click to expand...


=TRIM(LEFT(A1,FIND("(",A1)-1))
--ron
 
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
 
Back
Top