Remove number from string in excel
 

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

Remove number from string in excel
 

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

Remove number from string in excel
 

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

Remove number from string in excel
 

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

Remove number from string in excel
 

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

Remove number from string in excel
 

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

Remove number from string in excel
 

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

Remove number from string in excel
 

=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