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