Remove number from string in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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)
Click to expand...

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"
This would probably work for you...

=VALUE(MID(Sheet2!A1,FIND(":",Sheet2!A1)+1,255))
Click to expand...

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
 
Back
Top