How can I separate numbers from text ?

[uday]

Please suggest me to separate numbers from cell that contain text along with the number.
The length of the data can be anything.It can 1,2 or above 100.

 

[pecoflyer]

Does the string contain many numbers or only one? Perhaps post a small sample

 

[uday]

Please find the attachment.

 

[Somendra Misra]

Hi Uday,

Also care to post the manual solution also as there are combination of numbers.

Regards,

 

[uday]

I did not understand the manual solution?Could you please explain me in details if miss out something ?

 

[Somendra Misra]

Just give what number you want as answer for the sample your posted.

Regards,

 

[uday]

Every number...I want extract only numbers from the cell which contain text and other special charecters

 

[bobhc]

You may find this link of help

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

As to your question, data starts in A1 in B1 this formula

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)


.

 

[Faseeh]

This should also work:

Function SaperateNum(Num As String)
For i = 1 To Len(Num)
m = Mid(Num, i, 1)
If IsNumeric(m) = True Then Numm = Numm + m
Next i
SaperateNum = Numm
End Function

 

[uday]

Thanks, It's working................

 

[bobhc]

Which one!

 

[Faseeh]

@bobhc: Both are working. :D

 

[bobhc]

Good day Faseeh
Yep would be nice to know which one was the choice.....