Extracting numbers in alphanumeric cells (but in different places!)

[vespervesper]

I have something like the above. I wish to extract only the numbers from the "Skills" column - but the numbers themselves aren't always an exact character length (although usually 5-6), and they aren't always in the same place (sometimes leading, sometimes ending).

Also: - and I have no idea of this is possible - can you do the above but also show the other columns, something like a filter? But really the first question is more important

Many thanks!

 

[jim may]

Paste this User Defined Function (UDF) in a Standard Module - then you will be able to use it in a regular worksheet as I have done in Cells E2:E5.

Function num(inpt As String)
     Dim i As Integer
     For i = 1 To Len(inpt)
         If IsNumeric(Mid(inpt, i, 1)) Then
             num = num & Mid(inpt, i, 1)
         End If
     Next i
     num = CLng(num)
End Function

Excel Workbook
	A	B	C	D	E
1	Skills	City	Status		Numb Only
2	Handyman,182840				182840
3	Electrician
4	Fabric,17586				17586
5	184759,Gas				184759
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E2	=IF(num(A2)>0,num(A2),"")
E3	=IF(num(A3)>0,num(A3),"")
E4	=IF(num(A4)>0,num(A4),"")
E5	=IF(num(A5)>0,num(A5),"")

 

[mikerickson]

A CSE formula like
=MAX(IFERROR((MID(E1&REPT("x",255), COLUMN(A1:Z107), ROW(A1:Z107)))+0,0))

will do what you want