Pull Out Numbers From String

[Curious]

This column is comment. Numbers mixed with text in the cells. They
look like

Statue of liberty 12345 statue of liberty
12345 statue of liberty statue of liberty
statue of liberty statue of liberty 12345

The number (product order code) could be anywhere.

How do I pull out the numbers by formula or by VB code?

Thanks in advance.

HZ

 

[Gord Dibben]

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Or all in place but original data will be overwritten leaving just numerics.

Public Sub StripAllAZs()
''strips out everything except numbers
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 57) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

This code works

Function ExtractNumber(target As Range) As Long

ExtractNumberString = target
Do While (Left(ExtractNumberString, 1) < "0") Or _
(Left(ExtractNumberString, 1) > "9")
ExtractNumberString = Mid(ExtractNumberString, 2)
Loop

ExtractNumber = Val(ExtractNumberString)

End Function

 

[Guest]

You have some good VBA solutions, so here's a formula solution:

With
A1 containing text with some consecutive numbers in it.

This formula extracts those numbers
B1:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Note: if there are no numbers, the formula returns 0

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP