Searching if a specific character exists afer nth occurrence of a value

[johnmerlino]

Hey all,

I have the two formulas I want to apply if the condition is true or false. Problem is I need to determine whether there's a comma after the second ampersand in a cell.

For example, if the field looks like this:

HernaraMartin,Harry & Marie Anne & Ann Sally

then I want to execute this formula:

=CONCATENATE(LEFT(A30,FIND(",",A30)-1),",",TRIM(MID(A30,FIND("&",A30,FIND("&",A30)+1)+1,LEN(A30))))

which produces this:

HernaraMartin,Ann Sally

Now if the cell looks like this:

HernaraMartin,Harry & Marie Anne & Jenners,Sally

Since there's a comma after second ampersand, I want to execute this:

=TRIM(RIGHT(A32,LEN(A32)-FIND("*",SUBSTITUTE(A32,"&","*",LEN(A32)-LEN(SUBSTITUTE(A32,"&",""))))))

which outputs this:

Jenners,Sally

Problem is how to determine whether a comma exists after that second ampersand.

Thanks for response.

 

[de909]

Another option:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<2,LEFT(A1,SEARCH
(",",A1)),"")&TRIM(RIGHT(SUBSTITUTE(A1,"&",REPT(" ",200),2),200))