Extract only text

[Rahul Mishra]

Dear Sir,

1 Please provide me any formulas for how can i extract only text

like asdfasdfasdfasdfasdf456+45614561456asdfasdf%%%%asdfasdf

want only text not numeric
2 please provide me any formulas for how can i extract only numeric

 

[ashish mehra]

Hi Rahul,

Refer to the post by Chandoo.

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

Refer to post by Ashish Mathur.

https://fastexcel.wordpress.com/201...nd-designing-a-missing-excel-function-groups/

Regards,
AM

 

[XOR LX]

Hi.

What are your expected results for the string you give?

Regards

 

[Khalid NGO]

Hi Rahul,
There are many ways shared here, if you search it you will find, but I remembered Sir @shrivallabha shared a very useful formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Regards,

 

[Faseeh]

Hi

For Text Only

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

For Numbers Only Change True to False