searching for text followed by number

  • Hi


    Its a very wierd question..
    I have a large file with lots of text, with a few lines which are necessary
    The reqd lines are in the following format


    xxxxxxxxx yyyy zzzzzzzz


    where xxxxxx is text, yyyyy is number and z may or may not exist and can be ne type


    The problem is neither do i know the length of the text or the length of the number
    Is thr anyway I can extract by just somehow searching for text followed by number, extracting the text and the number.


    Thanx


    Aadarsh

  • Re: searching for text followed by number


    Hi Aadarsh,


    Search UDF section of this site, you can find solution to this.


    This is it.


    [vba]
    Function ExtractNumber(rCell As Range)
    Dim iCount As Integer, i As Integer
    Dim sText As String
    Dim lNum As String
    sText = rCell


    For iCount = Len(sText) To 1 Step -1
    If IsNumeric(Mid(sText, iCount, 1)) Then
    i = i + 1
    lNum = Mid(sText, iCount, 1) & lNum
    End If


    If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount



    ExtractNumber = CLng(lNum)
    End Function
    [/vba]



    HTH.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!