UPDATE ISNUMBER and nested FINDs not working as expected

**johnmerlino** · 11-07-2010, 08:00 PM

Hey all,

My initial post was too confusing. I will try to simplify.

I have this record:

Please Login or Register  to view this content.

Notice after the second ampersand, there's a name without the comma.

Now look at this record:

Please Login or Register  to view this content.

Notice after the second ampersand, there's a name with a comma delimited last from first and middle initial.

I have this formula:

Please Login or Register  to view this content.

This successfully addresses the situation where there's a comma and outputs this:

Please Login or Register  to view this content.

However, in instance where there's no comma delimiting last and first name after the second ampersand, it outputs:

Please Login or Register  to view this content.

This is wrong. It's supposed to output:

Please Login or Register  to view this content.

In other words, if there's a comma, then we take the last name after the second ampersand, otherwise we pull the last name from the very beginning of cell.

Thanks for response.Attachment 89414

**zbor** · 11-08-2010, 03:02 AM

And what is input text?

Best would be to upload example excel workbook so you can get answer ASAP and we don't need to spend time retyping your data.
Thx.

**johnmerlino** · 11-08-2010, 10:07 PM

What I'm basically trying to figure out is how to determine if a comma exists after that second ampersand.

If it exists, then run this:

Please Login or Register  to view this content.

If it doesn't, then run this:

Please Login or Register  to view this content.

I know there's a handy formula like this:
=IF(AND(LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))
But in this specific situation I don't know best way to determine whether a comma exists after second ampersand.

**johnmerlino** · 11-08-2010, 11:08 PM

I figured it out:

Please Login or Register  to view this content.

**squiggler47** · 11-10-2010, 02:13 AM

Ok given the data so far :-

KNUD J & MARIA L HOSTRUP
HOSTRUP, KNUD J & MARIA L
Sarah Ann Little Junior
Jenners,Ann Sally
HernaraMartin,Harry & Marie Anne & Ann Sally

Would need 3 formulas to split them up :-

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Basically what we have is several cases where "," or "&" are present or not, combining these :-

=CHOOSE((LEN(B2)-LEN(SUBSTITUTE(B2,"&","")))*3+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

gives us choices based on the number which is unique for each of the above cases this then selects the correct formula for each case in each column.

you will notice that some choices in the formula have letters "C","F","G","I" these are so far unknown cases if you get the letter showing up just create a formula to get the correct result and paste it in the formula over the letter in quotes!

Please find attached the file I used to create the formulas, the part formulas are highlighted in yellow, along with the final formula!