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.

 

[johnmerlino]

I figured it out:

=IF(ISNUMBER(FIND(",",MID(A30,FIND("&",A30,FIND("&",A30)+1)+1,LEN(A30)))),TRIM(RIGHT(A30,LEN(A30)-FIND("*",SUBSTITUTE(A30,"&","*",LEN(A30)-LEN(SUBSTITUTE(A30,"&","")))))),CONCATENATE(LEFT(A30,FIND(",",A30)-1),",",TRIM(MID(A30,FIND("&",A30,FIND("&",A30)+1)+1,LEN(A30)))))

 

[johnmerlino]

not pretty, but it works

 

[Aladin Akyurek]

Try something like...

=IF(ISNUMBER(FIND(",",REPLACE(A30,1,FIND("#",SUBSTITUTE(A30,"&","#",2)),""))),1,0)

Replace 1 and 0 with appropriate expressions.

 

[johnmerlino]

great, another problem. If it doesn't meet any of these conditions, then I get VALUE! error, when I just want cell to be empty.

 

[pgc01]

Hi

If there are a maximum of 2 ampersands, as in the examples you can use:

=ISNUMBER(SEARCH("*&*&*,",A1))

 

[johnmerlino]

Both my solution and Aladin's work. The problem now is I get a VALUE error if neither of the conditions are true. Is there a way to default it to "" an empty space if neither condition is true?

In other words, if it doesn't contain two ampersands, then default to ""

 

[johnmerlino]

Something like this may work:
=IF(ISNUMBER(FIND("&",A1,FIND("&",A1)+1)),TRUE,FALSE)

 

[johnmerlino]

ok this ultimately worked:

=IF(ISNUMBER(FIND("&",A1,FIND("&",A1)+1)),IF(ISNUMBER(FIND(",",MID(A1,FIND("&",A1,FIND("&",A1)+1)+1,LEN(A1)))),TRIM(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"&","*",LEN(A1)-LEN(SUBSTITUTE(A1,"&","")))))),CONCATENATE(LEFT(A1,FIND(",",A1)-1),",",TRIM(MID(A1,FIND("&",A1,FIND("&",A1)+1)+1,LEN(A1))))),"")

 

[Sandeep Warrier]

How about

Excel Workbook
	A	B
1	HernaraMartin,Harry & Marie Anne & Ann Sally	HernaraMartin,Ann Sally
2	HernaraMartin,Harry & Marie Anne & Jenners,Sally	Jenners,Sally
3	HernaraMartin,Harry & Marie Anne
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))=2,IF(ISNUMBER(SEARCH("*&*&*,",A1)),"",LEFT(A1,FIND(",",A1)))&TRIM(RIGHT(SUBSTITUTE(A1,"&",REPT(" ",255)),255)),"")