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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.