Results 1 to 4 of 4

Thread: Extracting Numeric Values From Alphanumeric Text

  1. #1
    Member
    Join Date
    Jun 2012
    Posts
    39
    Rep Power
    0

    Question Extracting Numeric Values From Alphanumeric Text

    I need to extract numeric values from a cell with alphanumeric or having other character to another cell.
    There is no specific format of the cell.
    I have the similar cells in thousands.

    For eg:
    A1: ab2hgi;234kjf8r

    Result:
    22348

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Here is a macro that you can try...
    Code:
    Sub ReduceToNumbersOnly()
      Dim X As Long, Z As Long, LastRow As Long, Strng As String, vArr As Variant
      Const FirstOutputCell As String = "B2"
      Const DataColumn As String = "A"
      Const StartRow As Long = 2
      LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
      vArr = Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)
      For X = 1 To UBound(vArr)
        Strng = ""
        For Z = 1 To Len(vArr(X, 1))
          If Mid(vArr(X, 1), Z, 1) Like "#" Then Strng = Strng & Mid(vArr(X, 1), Z, 1)
        Next
        vArr(X, 1) = Strng
      Next
      Range(FirstOutputCell).Resize(UBound(vArr)) = vArr
    End Sub
    The only thing you need to do is change the three constants (the Const statements) to match your actual layout. You can set the FirstOutputCell constant to your first data cell if you want to overwrite your original data with the numbers contained within them.

Similar Threads

  1. Replies: 6
    Last Post: 06-01-2013, 03:24 PM
  2. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  3. Replies: 2
    Last Post: 03-21-2013, 08:51 PM
  4. Replies: 2
    Last Post: 09-24-2012, 09:20 PM
  5. Replies: 2
    Last Post: 02-29-2012, 08:24 PM

Posting Permissions

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