PDA

View Full Version : Pulling Information from a String



Djblois
06-30-2010, 10:54 AM
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.

Paul_Hossler
06-30-2010, 11:10 AM
1. One Way


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


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

Paul

Bob Phillips
06-30-2010, 11:11 AM
Dim ary As Variant

With ActiveCell

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

GTO
06-30-2010, 11:12 AM
Hi Daniel,

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

Mark

GTO
06-30-2010, 11:17 AM
Okay, I'll just slink back to the corner now... Damn! I didn't type that much or that slow! LOL

Djblois
06-30-2010, 11:18 AM
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.

brettdj
07-03-2010, 09:40 PM
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