Hi Suman
u can try below UDF also
'Below User Define Function extract Number from string
Function ExtractNumber(rng As Range)
Dim i As Integer
For i = 1 To Len(rng)
Select Case Asc(Mid(rng.Value, i, 1))
Case 0 To 64, 123 To 197
ExtractNumber = ExtractNumber & Mid(rng.Value, i, 1)
End Select
Next i
End Function
'Below User Define Function extract Text from string
Function ExtractText(stdText As String)
Dim str As String, i As Integer
stdText = Trim(stdText)
For i = 1 To Len(stdText)
If Not IsNumeric(Mid(stdText, i, 1)) Then
str = str & Mid(stdText, i, 1)
End If
Next i
ExtractText = str
End Function
HTH
Mahesh
On Thu, Oct 6, 2011 at 2:27 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:
> 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
>
--
----------------------------------------------------------------------------------
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
