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.
Formula to extract numbers
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Formula to extract numbers
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78492
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to extract numbers
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)
(You'll probably need VBA)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Formula to extract numbers
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.
Ta, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78492
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to extract numbers
Could the "numbers" contain a minus sign, and/or a decimal point? Or all they all non-negative whole numbers?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Formula to extract numbers
The are all non-negative whole numbers. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78492
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to extract numbers
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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Formula to extract numbers
Excellent. Ta.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.