Pulling Information from a String

If I have a string like this "CUSTOMER: 9997|9998|9999. DEPARTMENT: 15|01|03.", minus the quotes - how would I get the 3 numbers after Customer but before Department. Plus it will not always be 3 numbers - it can be 1, 2, 3, 4, 5, etc. I would do the same for after Department but if I can see how to do it for Customer I should have no problem converting it for all the other words that I will try to find.



Plus, if I have a string like this "From JAN-01-2010 To DEC-31-2010" How do I get the Date Range in code.

1. One Way

[VBA]
Option Explicit
Sub drv()
Dim s As String
Dim v As Variant
Dim i As Long

s = "CUSTOMER: 9997|9998|9999. DEPARTMENT: 15|01|03."
s = Replace(s, " ", "|")
s = Replace(s, "||", "|")
s = Replace(s, " ", vbNullString)
s = Replace(s, ".", vbNullString)

v = Split(s, "|")
i = LBound(v)

While v(i) <> "CUSTOMER:"
i = i + 1
Wend

i = i + 1
While v(i) <> "DEPARTMENT:"
MsgBox "Customer = " & v(i)
i = i + 1
Wend

i = i + 1
While i <= UBound(v)
MsgBox "Department = " & v(i)
i = i + 1
Wend
End Sub
[/VBA]

2. Similar, but use DateSerial to combine the Year/month/day pieces

Paul

[vba]

Dim ary As Variant

With ActiveCell

ary = Split(Mid$(.Value2, InStr(.Value2, ": ") + 2, InStr(.Value2, ".") - InStr(.Value2, ": ") - 1), "|")
End With
[/vba]

Hi Daniel,

Are the vertical bars actually vertical bars, or is your example using the bars to represent cells (columns) in Excel?

Mark

Okay, I'll just slink back to the corner now... Damn! I didn't type that much or that slow! LOL

GTO, The Bars are the Pipe key on the \ key which is sometimes above the enter key.

Xld, I always wondered what is the difference between Value and Value2?

I thank all of you for the input.

Value2 treats Date and Currency formats differently from Value, see http://www.decisionmodels.com/calcsecretsj.htm

When I tested this in my code to use variant arrays rather than ranges,http://www.experts-exchange.com/A_2684.html , I had 8% faster performance with a full column of dates in column A (xl 2003), 2% when the entire column was processed with blank cells

Cheers

Dave