Selected Answer
Hi Jana,
This is the formula that will do the job.
=SUBSTITUTE(LEFT(MID($C2,FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),2))+1,100),FIND(CHAR(160),SUBSTITUTE(MID($C2,FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),2))+1,100),",",CHAR(160),2))-1),","," AND ")
It is based on the SUBSTITUE() function which allows you to specify the nth occurrence of a character to be changed. So, in FIND(CHAR(160),SUBSTITUTE(C2,",",CHAR(160),2) the second comma is first converted to CHAR(160) and then "found" by the FIND() function. Remember to read the formula from inside to the outer. CHAR(16) is just any character which doesn't normally occur in the text. Replace with with another, if it does.
The MID() being given a length of 100 characters is immaterial and intentionally large because the function ignores the instruction to retrieve characters which aren't there.
With that said, this is the way to build the formula.
[F2] =MID($C2,FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),2))+1,100)
[G2] =LEFT($F2,FIND(CHAR(160),SUBSTITUTE($F2,",",CHAR(160),2))-1)
[H2] =SUBSTITUTE($G2,","," AND ")
To assemble the formula, replace the two occurrences of $F2 in G2 with the full formula in F2 (without the = sign). Then repeat the action by replacing $G2 in H2 with what is now in G2.