Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Thumbs up Unanswered: Excel Programming

    Suppose Cell A1 has "TWID10293 Comments" entry and
    cell A2 has "TW298382 Comment on this." entry.
    I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".

  2. #2
    Join Date
    Oct 2003
    Posts
    17

    Re: Excel Programming

    Originally posted by Rajneesh
    Suppose Cell A1 has "TWID10293 Comments" entry and
    cell A2 has "TW298382 Comment on this." entry.
    I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".
    You will need to create a public function in some module in your xls.

    Public Function FindNumPart(aStr As String) As String
    '
    ' Find continuous numeric part from the given string
    Dim i As Integer
    Dim sResult As String
    Dim bNumFound As Boolean
    bNumFound = False
    sResult = ""

    For i = 1 To Len(aStr)
    If IsNumeric(Mid(aStr, i, 1)) Then
    bNumFound = True
    sResult = sResult & Mid(aStr, i, 1)
    Else
    If bNumFound Then
    Exit For
    End If
    End If
    Next
    FindNumPart = sResult
    End Function

    In the excel sheet cell (for example b1) you will need to put
    =IF(ISERROR(VALUE(FindNumPart(A1))),"", VALUE(FindNumPart(A1)))

    Here I have assumed that there is only one and first continous number within the string, that you are interested in. The for loop is exited when non numeric character after the number is found.

    (For the second row, copy the same formula in the cell b2)

    HTH

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Smile Re: Excel Programming

    Thanks a lot.

    Originally posted by jparagg
    You will need to create a public function in some module in your xls.

    Public Function FindNumPart(aStr As String) As String
    '
    ' Find continuous numeric part from the given string
    Dim i As Integer
    Dim sResult As String
    Dim bNumFound As Boolean
    bNumFound = False
    sResult = ""

    For i = 1 To Len(aStr)
    If IsNumeric(Mid(aStr, i, 1)) Then
    bNumFound = True
    sResult = sResult & Mid(aStr, i, 1)
    Else
    If bNumFound Then
    Exit For
    End If
    End If
    Next
    FindNumPart = sResult
    End Function

    In the excel sheet cell (for example b1) you will need to put
    =IF(ISERROR(VALUE(FindNumPart(A1))),"", VALUE(FindNumPart(A1)))

    Here I have assumed that there is only one and first continous number within the string, that you are interested in. The for loop is exited when non numeric character after the number is found.

    (For the second row, copy the same formula in the cell b2)

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •