Dear Suman, Please try below function and see attached sheet........................
Through Excel
B1=MID(B3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0,1,2,3,4,5,6,7,8,9")),LEN(B3))
Press ctrl+shift+Enter
C1==LEFT(A3,LEN(A3)-LEN(B3))
Through VBA
Function extractonlytxt(txt)
Dim txt1 As String
txt1 = ""
For i = 1 To Len(txt)
If (Asc(Mid(txt, i, 1)) > 64 And Asc(Mid(txt, i, 1)) < 90) Or (Asc(Mid(txt,
i, 1)) > 96 And Asc(Mid(txt, i, 1)) < 123) Then
txt1 = txt1 & Mid(txt, i, 1)
End If
Next i
extractonlytxt = txt1
End Function
Function exctractonlynumber(abc)
Dim abc1 As String
abc1 = ""
For i = 1 To Len(abc)
If IsNumeric(Mid(abc, i, 1)) Then
abc1 = abc1 & Mid(abc, i, 1)
End If
Next
exctractonlynumber = abc1
End Function
--
Thanks & regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
*http://noorain-ansari.blogspot.com/* <http://noorain-ansari.blogspot.com/>
On Wed, Oct 5, 2011 at 3:12 PM, Suman <ksuman1...@gmail.com> wrote:
> Hi Group
> I have multiple data in column A (Alphabet with numeric values) n i want to
> separate them?
>
> Available Data
>
> *A*
> suman1456 Sam221 Manni457
>
> I want like this:
>
> suman 1456 Sam 221 Manni 457
>
>
>
>
> --
>
>
>
>
>
>
>
>
>
> Suman Kumar
>
> Mob: +91 9810333884
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Extract Alpha+Numeric.xlsm
Description: Binary data
