Extracting parts of names
 

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,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Extracting parts of names
 

Try this:

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

Hope this helps.

Pete

On May 3, 6:03 pm, klysell .(donotspam) wrote:
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,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Extracting parts of names
 

Is it always (17)?

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

if not


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


--
Regards,

Peo Sjoblom



"klysell" .(donotspam) wrote in message
...
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,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Extracting parts of names
 

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

"klysell" .(donotspam) wrote in message
...
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,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Extracting parts of names
 

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098


"Peo Sjoblom" wrote:

Is it always (17)?

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

if not


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


--
Regards,

Peo Sjoblom



"klysell" .(donotspam) wrote in message
...
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,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Extracting parts of names
 

On Thu, 3 May 2007 10:03:02 -0700, klysell .(donotspam)
wrote:

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

Extracting parts of names
 

One way

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


the workbook has to be saved first





--
Regards,

Peo Sjoblom


"klysell" .(donotspam) wrote in message
...
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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098


"Peo Sjoblom" wrote:

Is it always (17)?

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

if not


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


--
Regards,

Peo Sjoblom



"klysell" .(donotspam) wrote in message
...
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,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098