try to use this UDF and see if it helps.. Function GetNumbers(S As String, Index As Integer) '-------------------------------- ' siti Vi / jakarta, 30-08-2009 '-------------------------------- Dim ArrN(), i%, tmp$, n%, t$, t2$
S = Trim(S) & "|"
For i% = 1 To Len(S) - 1
t$ = Mid(S, i%, 1)
t2$ = Mid(S, i% + 1, 1)
If InStr(1, "0123456789.", t$) > 0 Then
If InStr(1, "0123456789.", t2$) > 0 Then
tmp$ = tmp$ & t$
Else
tmp$ = tmp$ & t$
If Len(tmp$) > 0 Then
n = n + 1
ReDim Preserve ArrN(1 To n)
ArrN(n) = Val(tmp$)
tmp$ = ""
End If
End If
End If
Next i%
GetNumbers = ArrN(Index)
End Function
'-------------------------
formula in worksheet
=GetNumbers($B3,1)
=GetNumbers($B3,2)
=GetNumbers($B3,3)
and so on
On Tue, Apr 19, 2011 at 8:44 PM, karan kanuga <karankan...@gmail.com> wrote:
> Hi,
>
> Can any1 pls let me know how do i separate the text and nos in a
> given sentence. PFA the sheet where i need to separate the nos and text.
>
> Thanks.
>
--
----------------------------------------------------------------------------------
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
ctv_UDF_GetNumber (extract nos from text).xls
Description: MS-Excel spreadsheet
