Extracting number only from a Cell

[Kinjal Shah]

Hi,

How to I extract the number from a cell that has text,symbol and number?
For ex- if Cell B5 has "Patch=3", I want cell C5 as "3".

I am not a pro in Excel so the simple solution will be appreciated.

Kinjal.

 

[Mike H..]

Hi,

How to I extract the number from a cell that has text,symbol and number?
For ex- if Cell B5 has "Patch=3", I want cell C5 as "3".

I am not a pro in Excel so the simple solution will be appreciated.

Kinjal.

Hi,

Try this

=--MID(B5,FIND("=",B5)+1,999)

 

[Somendra Misra]

Hi Kinjal,

This is most repeated question on any Excel forum and formula can be small one (as suggested by @Mike H.. ) to a very large one also. This can only be determine by having a look at your data pattern.

So kindly upload a file with at least 20-30 different line data showing the variation in your data.

Else if you data is similar to your first sample, you can go ahead with @Mike H.. formula.

Regards,

 

[Deepak]

In addition to what @Somendra Misra said..

http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

or VBA..

Public Function onlynumeric(mystr As Variant)
Dim myOutput As String, i As Integer

For i = 1 To Len(mystr)
    If IsNumeric(Mid(mystr, i, 1)) Then myOutput = myOutput & Mid(mystr, i, 1)
Next
onlynumeric = myOutput * 1
End Function