I have the following string: "No. of Unsuccessful Calls: 1"
I would like to link to the number in this cell only from sheet one (this
string is in cell A1 of sheet 2)

This would probably work for you...

=VALUE(MID(Sheet2!A1,FIND(":",Sheet2!A1)+1,255))

Actually, since the text part of your string is fixed, you could reduce the
above formula to this...

=VALUE(MID(Sheet2!A1,27,255))

Rick