Close to an extraction but not close enough

[DSNTNOENUF]

I have a very simplified version of a text string below. I am trying to use mid and find to extract the middle (Bold) data from the string.

Right now this formula would return the 123 and 1234567 of the below line items.

Can someone look at my formula and tell me how to make it count inward so many numbers of the of the carrot delimiter so that I can extract the data between it (5 I think in the actual file)? Text to column as a precursor is not an option because of complexity of the file.

=MID(INPUT!$C1,FIND("^",INPUT!$C1)+1,FIND("^",INPUT!$C1,FIND("^",INPUT!$C1)+1)-1-FIND("^",INPUT!$C1))


*^123^howdy duty^jovial
*^1234567^purdy^winter outside fun time^

 

[njimack]

Excel Workbook
	A	B
1	*^123^howdy duty^jovial	123
2	*^1234567^purdy^winter outside fun time^	1234567
Sheet2

 

[jolivanes]

Here are two more, both for the string in A1

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"^",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(A1,"^",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(A1,"^",CHAR(1),2))-1)
and/or
=MID(MID(A1,3,99),FIND("^",MID(A1,3,99),1)+1,FIND("^",MID(MID(A1,3,99),FIND("^",MID(A1,3,99),1) + 1,255),1) - 1)

 

[DSNTNOENUF]

Thank-you so very much.