How to TRIM away unwanted data from a cell

[meppwc]

I have a column that, that if data exists, then I want to trim away everything except for a 7 digit number (if a 7 digit number exists)
The cells can contain, letters, numbers (other than 7 digits next to each other), or be blank

Is there any way to trim away everything except for the 7 digit number (if exists)?

 

[Rick Rothstein]

Just so you know, NeonRedShapie's UDF will return a value for text like this (as well as other variations)...

sda 1.2,3-$ dsk

As long as your numbers are whole numbers (no decimal point), here is a routine that is immune to those problems and it works whether the number is surrounded by spaces or not (use it the same way, although note the name change)...

Function GetNumber(S As String, Count As Long) As Variant
  Dim X As Long
  For X = 1 To Len(S) - Count + 1
    If Mid(S, X, Count) Like String(Count, "#") Then
      GetNumber = Mid(S, X, Count)
      Exit Function
    End If
  Next
  GetNumber = ""
End Function