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.



  • Re: searching for text followed by number

    Hi Aadarsh,

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

    This is it.

    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


Participate now!

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