Find 5 Letters in Text String

[djayhawk]

Hello,

I have a list of text strings and I'm looking to only return 5 digit numbers from them. They vary in length and position and sometimes there are numbers that are longer than 5 digits (I don't want these). Is there a formula that might work for doing this

Here is an Example. I want the red

IC--65543--12#
67890-Beach--7645712
RB#$12097**

 

[Michael M]

How about a custom Function ?
Insert this into a standard module

Function FD(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "(\d{5})"
  If .Test(s) Then FD = .Execute(s)(0).SubMatches(0)
End With
End Function

Excel 2003
	A	B
1	IC--65543--12#	65543
2	67890-Beach--7645712	67890
3	RB#$12097**	12097
Sheet1

Cell Formulas
Range	Formula
B1	=FD(A1)
B2	=FD(A2)
B3	=FD(A3)

 

[Michael M]

Previosu function can give incorrect data, use this one

Function FD(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "(?:^|\D)(\d{5})(?!\d)"
  If .Test(s) Then FD = .Execute(s)(0).SubMatches(0)
End With
End Function

 

[djayhawk]

That works great, thanks I appreciate your help!