Remove number from string in excel

[Chris]

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

 

[Bernie Deitrick]

Chris,

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

HTH,
Bernie
MS Excel MVP

 

[PCLIVE]

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

 

[Mike H]

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

 

[Mike H]

Hmmm I like that!!

Chris,

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

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

I have the following string: "No. of Unsuccessful Calls: 1"

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

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3