Hi Amods, Please find the user defined funtions for extracting the numbers and text.
Function ExtractNumber(rCell As Range, _ Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double Dim iCount As Integer, i As Integer, iLoop As Integer Dim sText As String, strNeg As String, strDec As String Dim lNum As String Dim vVal, vVal2 sText = rCell If Take_decimal = True And Take_negative = True Then strNeg = "-" strDec = "." ElseIf Take_decimal = True And Take_negative = False Then strNeg = vbNullString strDec = "." ElseIf Take_decimal = False And Take_negative = True Then strNeg = "-" strDec = vbNullString End If iLoop = Len(sText) For iCount = iLoop To 1 Step -1 vVal = Mid(sText, iCount, 1) If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum If IsNumeric(lNum) Then If CDbl(lNum) < 0 Then Exit For Else lNum = Replace(lNum, Left(lNum, 1), "", , 1) End If End If If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid (lNum, 1, 1)) Next iCount ExtractNumber = CDbl(lNum) End Function Function ExtractString(rCell As Range) As String Dim iCount As Integer, i As Integer, iLoop As Integer Dim sText As String, strNeg As String, strDec As String Dim lString As String Dim vVal, vVal2 sText = rCell iLoop = Len(sText) For iCount = iLoop To 1 Step -1 vVal = Mid(sText, iCount, 1) If IsNumeric(vVal) = False Then i = i + 1 lString = Mid(sText, iCount, 1) & lString If IsNumeric(lString) = False Then If Len(lString) < 0 Then Exit For Else lString = Replace(lString, Left(lString, 1), "", , 1) End If End If If i = 1 And lString <> vbNullString Then lString = CStr(Mid (lString, 1, 1)) Next iCount ExtractString = lString End Function Thanks Valli "Unless you try to do something beyond what you have already mastered, you will never grow." On Jan 7, 7:09 pm, Amods Bagwe <amodba...@yahoo.co.in> wrote: > Please help me to get solution . > > Need to split the numbers & words using formula & not macros. > > dsasd2323 fssdf34235 sdsefges34 44rrr4 > (In ths Alphanumeric value, the numbers & words should be separated in > different columns) > > EG: 123 abc45 tr OUTPUT Should be 123 45(One cell) abc tr(Another cell) > > The INTERNET now has a personality. YOURS! See your Yahoo! > Homepage.http://in.yahoo.com/
-- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe