software

I would like to set up a formula that references a string in another cell but
truncates the beginning, middle, or end of it. The cell containing the data
always has five names separated by four commas, as shown below

Example:
A1 = Contains the text string quot;Simmons, Bogut, Magloire, Ford, Reddquot;
B1 = Want a formula that returns only the last two names in A1, quot;Ford, Reddquot;
C1 = Want a formula that returns only the first three names in A1, quot;Simmons,
Bogut, Magloirequot;
D1 = Want a formula that returns only the third name in A1, quot;Magloirequot;

Thank you for your help,
Bob

In B1: =RIGHT(A1,LEN(A1)-1-SEARCH(quot;,quot;,A1,1 SEARCH(quot;,quot;,A1,1 SEARCH(quot;,quot;,A1))))
In C1: =SUBSTITUTE(A1,quot;, quot;amp;B1,quot;quot;)
In D1: =RIGHT(C1,LEN(C1)-1-SEARCH(quot;,quot;,C1,1 SEARCH(quot;,quot;,C1)))--
Regards,
Davequot;bobquot; wrote:

gt; I would like to set up a formula that references a string in another cell but
gt; truncates the beginning, middle, or end of it. The cell containing the data
gt; always has five names separated by four commas, as shown below
gt;
gt; Example:
gt; A1 = Contains the text string quot;Simmons, Bogut, Magloire, Ford, Reddquot;
gt; B1 = Want a formula that returns only the last two names in A1, quot;Ford, Reddquot;
gt; C1 = Want a formula that returns only the first three names in A1, quot;Simmons,
gt; Bogut, Magloirequot;
gt; D1 = Want a formula that returns only the third name in A1, quot;Magloirequot;
gt;
gt; Thank you for your help,
gt; Bob

I think I would use the Data gt; TextToColumns, comma delimited, to separate
all five names into their own columns, and then CONCATENATE the ones I wanted
back together............

Vaya con Dios,
Chuck, CABGx3
quot;bobquot; wrote:

gt; I would like to set up a formula that references a string in another cell but
gt; truncates the beginning, middle, or end of it. The cell containing the data
gt; always has five names separated by four commas, as shown below
gt;
gt; Example:
gt; A1 = Contains the text string quot;Simmons, Bogut, Magloire, Ford, Reddquot;
gt; B1 = Want a formula that returns only the last two names in A1, quot;Ford, Reddquot;
gt; C1 = Want a formula that returns only the first three names in A1, quot;Simmons,
gt; Bogut, Magloirequot;
gt; D1 = Want a formula that returns only the third name in A1, quot;Magloirequot;
gt;
gt; Thank you for your help,
gt; Bob

On Tue, 20 Dec 2005 11:31:04 -0800, bob gt; wrote:

gt;I would like to set up a formula that references a string in another cell but
gt;truncates the beginning, middle, or end of it. The cell containing the data
gt;always has five names separated by four commas, as shown below
gt;
gt;Example:
gt;A1 = Contains the text string quot;Simmons, Bogut, Magloire, Ford, Reddquot;
gt;B1 = Want a formula that returns only the last two names in A1, quot;Ford, Reddquot;
gt;C1 = Want a formula that returns only the first three names in A1, quot;Simmons,
gt;Bogut, Magloirequot;
gt;D1 = Want a formula that returns only the third name in A1, quot;Magloirequot;
gt;
gt;Thank you for your help,
gt;Bob

Two methods:

Using native Excel functions:

B1:=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,quot;,quot;,CHAR(1),3)) 2,255)
C1:=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,quot;,quot;,CHAR(1),3) )-1)
D1:=MID(C1,FIND(CHAR(1),SUBSTITUTE(C1,quot;,quot;,CHAR(1),2)) 2,255)

Using regular expressions:

Download and install Longre's free morefunc.xll add-in from
xcell05.free.fr/B1:=REGEX.MID(A1,quot;\w ,\s\w $quot;)
C1:=REGEX.MID(A1,quot;\w ,\s\w ,\s\w quot;)
D1:=REGEX.MID(A1,quot;\w quot;,3)
--ron