Consulting

Results 1 to 7 of 7

Thread: Pulling Information from a String

  1. #1
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location

    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.
    Thank You,
    Daniel Blois
    http://studenthacker.blogspot.com/

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Dim ary As Variant

    With ActiveCell

    ary = Split(Mid$(.Value2, InStr(.Value2, ": ") + 2, InStr(.Value2, ".") - InStr(.Value2, ": ") - 1), "|")
    End With
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Hi Daniel,

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

    Mark

  5. #5
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Okay, I'll just slink back to the corner now... Damn! I didn't type that much or that slow! LOL

  6. #6
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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.
    Thank You,
    Daniel Blois
    http://studenthacker.blogspot.com/

  7. #7
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •