Formula giving unexpected results

**johnmerlino** · 11-29-2010, 10:16 PM

Hey all,

I have this formula:

=TRIM(LEFT(MID(B2,FIND(". ",B2)+1,255),IF(ISNUMBER(SEARCH(",",B2)),FIND(",",MID(B2,FIND(". ",B2)+1,255))-1,RIGHT(FIND(" ",MID(B2,FIND(". ",B2)+1,255))-1))))

When there is a comma after name:
Lasalle Bank Na Plaintiff vs. Jonathan Jason Ferragut, et al Defendant

I want to return Jonathan Jason Ferragut, for example.

When there is no comma after the name:

Hsbc Bank Usa Na Plaintiff vs. Faith M Hills Defendant

I still want to return the name:
Faith M Hills

However, in the latter case, my formula is just returning a blank cell and not the name.

I attached an example of the issue. Notice the last row, the field does not return the name, but rather a blank cell.

Thanks for response.

**Maistrye** · 11-29-2010, 10:53 PM

Hi,

The portion of your formula that is causing you issues is the FALSE part of the inside IF statement. However, you will need to rearrange your statement somewhat. I'd recommend something like the following -- it is making the assumption that all of your data has the word " Defendant" in it. The thing you were trying to do with the space has the issue of matching any space, not just the one (last one) you were trying to match. (There are ways of doing it however.)

The following may or may not meet your needs.

PHP Code:


=TRIM(MID(A3,FIND("vs.",A3)+3,IF(ISERROR(FIND(",",MID(A3,FIND("vs.",A3)+3,LEN(A3)))),FIND(" Defendant",A3),FIND(",",A3))-(FIND("vs.",A3)+3)))

If the above is not suitable, my recommendation is to set up your statement as follows, Encapsulated in a TRIM(). You can fill in the commented sections with the appropriate formulas. Note: it's the last of the three lines I'm recommending as your setup, the first two just show how it's derived.

PHP Code:


=MID(A3,[Find starting point],[Find length])
=MID(A3,[Find starting point],[Find end point] - [Find starting point])
=MID(A3,[Find starting point],IF([Comma exists], [Find comma point], [Find alternate split point]) - [Find starting point])

[Find starting point] =(FIND("vs.",A3)+3)
etc.

Please note, in the Formula ribbon, there is a button called Evaluate Formula. This button allows you to view what is being calculated at each step and see where your formula has issues. I find it very useful for diagnosing problems like this.

Hope that helps,
S

**protonLeah** · 11-29-2010, 10:56 PM

The attached uses ttwo helper columns since I try to avoid long formulas:

**SDruley** · 11-30-2010, 10:43 AM

My example may also help.

**squiggler47** · 11-30-2010, 10:58 AM

=TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("vs.",A1&"vs.")-3),", et al",""),"Defendant",""))

again simple! Works with all three examples, now the wait for the other cases!

**johnmerlino** · 11-30-2010, 10:38 PM

Thanks for responses, especially last one. This ultimately worked:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("vs.",A1&"vs.")-3),", et al",""),"Defendant",""),", Et Al","")," ""","")," Jr","")," Jr.",""))

**squiggler47** · 12-01-2010, 01:20 AM

Jr. is redundant as you have already deleted Jr so Jr. will never appear! always substitute the shorter string last!

So you either need to swap Jr and Jr. or just change Jr. to .