how do I extract only certain data from a cell?

[Stan in South Africa]

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.

 

[Ragdyer]

Try this:

=MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&151617181920000)),5)

And copy down as needed.

This should work - as long as there are no spaces within the actual 5 digit
invoice number itself.

 

[Stan in South Africa]

Thank You!

Try this:

=MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&151617181920000)),5)

And copy down as needed.

This should work - as long as there are no spaces within the actual 5
digit
invoice number itself.

 

[Rick Rothstein \(MVP - VB\)]

One possible problem... an entry like this: Oops 183AB18801

Rick

 

[Ron Rosenfeld]

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

 

[Ragdyer]

You're welcome ... but by now you probably see the shortcomings.

 

[Ragdyer]

You're right!

Though it was probably enough to get the OP started.