On Sun, 6 Jul 2008 06:37:58 +0200, "Stan in South Africa" <me@there wrote:

Office 2003, Win XP Pro.
I am presented with a worksheet containing dozens of cells in which is
contained data of different lengths. eg
Zippohat 52131123Sa011880702
Baker 18164
Renatsainv17247
15592 Inv
Each contains an invoice number, as follows:
18807
18164
17247
15592
These invoice numbers range between 15000 and 20000
Is there any way to extract only the invoice number from each cell.


You can do it with a User Defined Function:

To enter the function, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula of the type:

=Invoice(cell_ref) into some cell. (cell_ref could also be the actual string
itself.

If a value of 15000-20000 does not exist, the function will return a #VALUE!
error, although a more descriptive result could be substituted.

===================================
Option Explicit
Function Invoice(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(1[5-9]\d{3})|(200000)"
If re.test(str) = True Then
Set mc = re.Execute(str)
Invoice = mc(0).Value
Else
Invoice = CVErr(xlErrValue)
End If
End Function
===================================
--ron