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

	A​	B​	C​
1​		NAME=
2​		PAR
3​			Formula Result
4​	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"	=MID(A4,SEARCH($B$1,A4)+(LEN($B$1)+1),SEARCH($B$2,A4)-SEARCH($B$1,A4)-(LEN($B$1)+3))	TARGA RESOURCES CORP
5​	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"	=MID(A5,SEARCH($B$1,A5)+(LEN($B$1)+1),SEARCH($B$2,A5)-SEARCH($B$1,A5)-(LEN($B$1)+3))	#VALUE!
6​	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"	=MID(A6,SEARCH($B$1,A6)+(LEN($B$1)+1),SEARCH($B$2,A6)-SEARCH($B$1,A6)-(LEN($B$1)+3))	#VALUE!
7​	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"	=MID(A7,SEARCH($B$1,A7)+(LEN($B$1)+1),SEARCH($B$2,A7)-SEARCH($B$1,A7)-(LEN($B$1)+3))	SARK INFRASTRUCTURE GROUP

Sheet: Sheet1

 

[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.