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 "Simmons, Bogut, Magloire, Ford, Redd"
B1 = Want a formula that returns only the last two names in A1, "Ford, Redd"
C1 = Want a formula that returns only the first three names in A1, "Simmons,
Bogut, Magloire"
D1 = Want a formula that returns only the third name in A1, "Magloire"

Thank you for your help,
Bob

In B1: =RIGHT(A1,LEN(A1)-1-SEARCH(",",A1,1+SEARCH(",",A1,1+SEARCH(",",A1))))
In C1: =SUBSTITUTE(A1,", "&B1,"")
In D1: =RIGHT(C1,LEN(C1)-1-SEARCH(",",C1,1+SEARCH(",",C1)))


--
Regards,
Dave


"bob" wrote:

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 "Simmons, Bogut, Magloire, Ford, Redd"
B1 = Want a formula that returns only the last two names in A1, "Ford, Redd"
C1 = Want a formula that returns only the first three names in A1, "Simmons,
Bogut, Magloire"
D1 = Want a formula that returns only the third name in A1, "Magloire"

Thank you for your help,
Bob

I think I would use the Data 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



"bob" wrote:

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 "Simmons, Bogut, Magloire, Ford, Redd"
B1 = Want a formula that returns only the last two names in A1, "Ford, Redd"
C1 = Want a formula that returns only the first three names in A1, "Simmons,
Bogut, Magloire"
D1 = Want a formula that returns only the third name in A1, "Magloire"

Thank you for your help,
Bob

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

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 "Simmons, Bogut, Magloire, Ford, Redd"
B1 = Want a formula that returns only the last two names in A1, "Ford, Redd"
C1 = Want a formula that returns only the first three names in A1, "Simmons,
Bogut, Magloire"
D1 = Want a formula that returns only the third name in A1, "Magloire"

Thank you for your help,
Bob

Two methods:

Using native Excel functions:

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

Using regular expressions:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/


B1: =REGEX.MID(A1,"\w+,\s\w+$")
C1: =REGEX.MID(A1,"\w+,\s\w+,\s\w+")
D1: =REGEX.MID(A1,"\w+",3)



--ron