Omitting right part of text string
 

Hi,

I want to omit the right part of a text string that starts with "(" and ends
in ")" while returning the left part of the text string. I would have a cell
that contains a name such as "Lysell, Kent". Right after this name a number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4 digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Omitting right part of text string
 

Give this a try (assuming your text is in A1)...

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

Rick



"klysell" wrote in message ...
Hi,

I want to omit the right part of a text string that starts with "(" and ends
in ")" while returning the left part of the text string. I would have a cell
that contains a name such as "Lysell, Kent". Right after this name a number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4 digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Omitting right part of text string
 

Sub trimcells()
mc = "G"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
c.Value = Left(c, InStr(c, "(") - 1)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klysell" wrote in message
...
Hi,

I want to omit the right part of a text string that starts with "(" and
ends
in ")" while returning the left part of the text string. I would have a
cell
that contains a name such as "Lysell, Kent". Right after this name a
number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4
digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Omitting right part of text string
 

Thanks guys...
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Don Guillett" wrote:

Sub trimcells()
mc = "G"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
c.Value = Left(c, InStr(c, "(") - 1)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klysell" wrote in message
...
Hi,

I want to omit the right part of a text string that starts with "(" and
ends
in ")" while returning the left part of the text string. I would have a
cell
that contains a name such as "Lysell, Kent". Right after this name a
number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4
digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Omitting right part of text string
 

a tiny bit shorter.
=LEFT(a1,FIND("(",a1)-1)
=LEFT(A1,FIND("(",A1&"(")-1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try (assuming your text is in A1)...

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

Rick



"klysell" wrote in message
...
Hi,

I want to omit the right part of a text string that starts with "(" and
ends
in ")" while returning the left part of the text string. I would have a
cell
that contains a name such as "Lysell, Kent". Right after this name a
number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4
digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Omitting right part of text string
 

I tacked on the "(" to protect the formula from the #VALUE! error if the
cell contents were empty or if the text did not contain a "(" character in
it. I was sort of anticipating the OP would be copying the formula down.

Rick


"Don Guillett" wrote in message
...
a tiny bit shorter.
=LEFT(a1,FIND("(",a1)-1)
=LEFT(A1,FIND("(",A1&"(")-1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try (assuming your text is in A1)...

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

Rick



"klysell" wrote in message
...
Hi,

I want to omit the right part of a text string that starts with "(" and
ends
in ")" while returning the left part of the text string. I would have a
cell
that contains a name such as "Lysell, Kent". Right after this name a
number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4
digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Omitting right part of text string
 

I see your point under the cited circumstances. Good thinking

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
I tacked on the "(" to protect the formula from the #VALUE! error if the
cell contents were empty or if the text did not contain a "(" character in
it. I was sort of anticipating the OP would be copying the formula down.

Rick


"Don Guillett" wrote in message
...
a tiny bit shorter.
=LEFT(a1,FIND("(",a1)-1)
=LEFT(A1,FIND("(",A1&"(")-1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try (assuming your text is in A1)...

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

Rick



"klysell" wrote in message
...
Hi,

I want to omit the right part of a text string that starts with "(" and
ends
in ")" while returning the left part of the text string. I would have a
cell
that contains a name such as "Lysell, Kent". Right after this name a
number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4
digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557