Hi,

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)

Thanks
C

Chris,

=VALUE(SUBSTITUTE(Sheet2!A1,"No. of Unsuccessful Calls: ",""))

HTH,
Bernie
MS Excel MVP


"Chris" wrote in message
...
Hi,

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)

Thanks
C

Not sure exactly what you mean. But if you want to just return the number
at the end of the string, then assuming that the number will always be
preceded by ": ", then one way may be:

=RIGHT(Sheet2!A1,LEN(Sheet2!A1)-FIND(":",Sheet2!A1)-1)*1

HTH,
Paul


"Chris" wrote in message
...
Hi,

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)

Thanks
C

An easy solution is to extract the rightmost character but I suspect you
would then tell us the number could be 10 or 999 so a more involved approach
is required.

I missed the bit about having it on a different sheet so put this on the
same sheet and on Sheet 1 put =Sheet2!B1 or wherever it is. Conversly you
could change all the reference to add Sheet2!

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

It's an array so Ctrl+Shift+enter

Mike



"Chris" wrote:

Hi,

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)

Thanks
C

Hmmm I like that!!

"Bernie Deitrick" wrote:

Chris,

=VALUE(SUBSTITUTE(Sheet2!A1,"No. of Unsuccessful Calls: ",""))

HTH,
Bernie
MS Excel MVP


"Chris" wrote in message
...
Hi,

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)

Thanks
C

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))

Rick

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

=MID(Sheet2!A1,27,99)*1

Vaya con Dios,
Chuck, CABGx3



"Chris" wrote:

Hi,

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)

Thanks
C