Formula to extract numbers

[agibsonsw]

Hello. (Excel 2003)
Does anyone have a formula to extract all numbers from a cell? The cell might be a combination of letters and numbers 'AB123CD4'.

Can this be done in Excel rather than VBA? Thanks, Andy.

[HansV]

In what form would you like to extract the numbers? One number per cell in different cells next to each other, or one number per cell in different cells below each other, or a semicolon-delimited list of numbers in one cell, or ...?

(You'll probably need VBA)

[agibsonsw]

Hi. Initially all numbers in a single, separate cell. I thought it might be possible with an array formula, but suspect that I might need VBA.
Ta, Andy.

[HansV]

Could the "numbers" contain a minus sign, and/or a decimal point? Or all they all non-negative whole numbers?

[agibsonsw]

The are all non-negative whole numbers. Andy.

[HansV]

Here is a macro that you can use. Before running it, select a series of cells in a single column. The cells immediately to the right of the selection will be overwritten with the numbers, so you should insert enough blank columns if necessary.

Code: Select all

Sub ExtractNumbers()
  Dim rng As Range
  Dim i As Integer
  Dim intOffset As Integer
  Dim strChar As String
  Dim strNum As String
  Dim strVal As String
  Dim blnNew As Boolean
  Dim blnOld As Boolean
  ' Loop through cells
  For Each rng In Selection.Columns(1).Cells
    ' Initialize variables
    strVal = rng.Value
    blnOld = False
    intOffset = 0
    ' Loop through characters
    For i = 1 To Len(strVal)
      ' Get i-th character
      strChar = Mid(strVal, i, 1)
      ' Is it a digit?
      blnNew = IsNumeric(strChar)
      If blnNew And blnOld Then
        ' Already building a number
        strNum = strNum & strChar
      ElseIf blnNew And Not blnOld Then
        ' Start a new number
        strNum = strChar
      ElseIf Not blnNew And blnOld Then
        ' Just finished a number
        intOffset = intOffset + 1
        rng.Offset(0, intOffset) = CLng(strNum)
      Else ' Not blnNew And Not blnOld
        ' Nothing doing
      End If
      ' Set up for next iteration
      blnOld = blnNew
    Next i
    ' End of value - do we have a final number?
    If blnNew Then
      intOffset = intOffset + 1
      rng.Offset(0, intOffset) = CLng(strNum)
    End If
  Next rng
End Sub

[agibsonsw]

Excellent. Ta.