| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to return part of a text string | Excel Discussion (Misc queries) | |||
| How do I extract part of a text string | Excel Discussion (Misc queries) | |||
| removing part of text string to another cell | Excel Discussion (Misc queries) | |||
| Use a formula to delete part of a text string? | Excel Worksheet Functions | |||
| Omitting Part of the Y-Axis | Charts and Charting in Excel | |||
Linear Mode
