MID / SEARCH error on the words "Spark" and "Park"

[MrSamExcel]

Why does my formula in B5 and B6 break with the words "SPARK" and "PARK" (regardless of upper/lower case)? As you can see, replacing SPARK with SARK works fine. I think the issue lies in the MID part of the formula because it generated a result when I just used the SEARCH and LEN parts.

Excel 2013 64 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td][/td][td=bgcolor:#00B050]NAME=[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td][/td][td=bgcolor:#00B050]PAR[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td][/td][td][/td][td]Formula Result[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]HOLDING ASOFDATE="03/31/2017" CUSIP="87612G101" MKT_VALUE="22998904.50" NAME="TARGA RESOURCES CORP" PAR="383955.00" PERCENT_PORT="1.5" PRICE="59.90"[/td][td=bgcolor:#FFFFFF]=MID(A4,SEARCH($B$1,A4)+(LEN($B$1)+1),SEARCH($B$2,A4)-SEARCH($B$1,A4)-(LEN($B$1)+3))[/td][td]TARGA RESOURCES CORP[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]HOLDING ASOFDATE="03/31/2017" CUSIP="B0T9JZ902" MKT_VALUE="21217562.58" NAME="SPARK INFRASTRUCTURE GROUP" PAR="11733643.00" PERCENT_PORT="1.4" PRICE="2.37"[/td][td=bgcolor:#FFFFFF]=MID(A5,SEARCH($B$1,A5)+(LEN($B$1)+1),SEARCH($B$2,A5)-SEARCH($B$1,A5)-(LEN($B$1)+3))[/td][td]#VALUE![/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]HOLDING ASOFDATE="03/31/2017" CUSIP="B0T9JZ902" MKT_VALUE="21217562.58" NAME="PARK INFRASTRUCTURE GROUP" PAR="11733643.00" PERCENT_PORT="1.4" PRICE="2.37"[/td][td=bgcolor:#FFFFFF]=MID(A6,SEARCH($B$1,A6)+(LEN($B$1)+1),SEARCH($B$2,A6)-SEARCH($B$1,A6)-(LEN($B$1)+3))[/td][td]#VALUE![/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]HOLDING ASOFDATE="03/31/2017" CUSIP="B0T9JZ902" MKT_VALUE="21217562.58" NAME="SARK INFRASTRUCTURE GROUP" PAR="11733643.00" PERCENT_PORT="1.4" PRICE="2.37"[/td][td=bgcolor:#FFFFFF]=MID(A7,SEARCH($B$1,A7)+(LEN($B$1)+1),SEARCH($B$2,A7)-SEARCH($B$1,A7)-(LEN($B$1)+3))[/td][td]SARK INFRASTRUCTURE GROUP[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[CyrusTheVirus]

It is because your formula is looking for "PAR", which in A5 and A6 there is "PAR" within the group/corp names... Change B2 to "PAR=" not just "PAR", that should correct it...

... remember, you are trying to find a unique identifier (or phrase) that every cell possesses... and in your scenario "PAR=" seems to to be something you could use for that. "PAR" is not unique because "PAR" can be within a group/corp name, but the odds of a group/corp name containing "PAR=" is incredibly unlikely.

 

[Scott Huish]

Try this formula instead:
=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("NAME=",A1)+5,""),"""",REPT(" ",255)),255))

 

[MrSamExcel]

Staring me right in the face...worked perfectly, thank you!
Scott: Your suggestion worked as well, I just need to spend some time to understand exactly how it works. Thanks.