CODEWITHSUNDEEP
vbaexceldelimiter
Pato
Pato

Reputation: 9

How do I set up code to dynamically delimit multiple times?

I'm analyzing some data pulled from a website and right now it's formatted like this within a single cell in Excel, for example:

[2015-08-01--2010-01-01], Jerry Smith; [2009-12-31--2000-01-01], John Brown

where all of that is located in, let's say, A1.

I want to delimit it first by the semi-colon, then transpose the information, and then separate into columns the two date periods, so that way the final result for the example I listed above turns into a 2x3 cell grouping with the columns being 1. Start date 2. End date 3. Name

I've played around with some VBA code and I'm getting frustrated because I can't even figure out how to code the first step of delimiting by the semi-colon.

This is what I'm using so far:

    Sub CommandButton1_Click()
Selection.TextToColumns _
  Destination:=Range("A1"), _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=False, _
  Semicolon:=True, _
  Comma:=False, _
  Space:=False, _
  Other:=False, _
  OtherChar:=False, _

  End Sub

I know that this is the incorrect first step for delimiting by semi-colon, so what do I need to do to fix it and how do I approach the next steps I described?

Also, how can I write the code correctly such that it can adapt to 3+ name and date range pairings instead of only two like I listed in my example cell?

Upvotes: 0

Views: 228

Answers (2)

user4039065
user4039065

Reputation:

Use variant arrays to first Split on the semi-colon, then split each piece on the comma. The dates are split/parse into actual dates and cell number formatting is applied to recreate the format of the original string dates.

Sub CommandButton1_Click()
    Dim v As Long, vTMPs As Variant, vVALs As Variant, vDTs As Variant
    With ActiveSheet
        'split on semi-colon (e.g. Chr(59))
        vTMPs = Split(.Cells(1, 1).Value2, Chr(59))
        For v = LBound(vTMPs) To UBound(vTMPs)
            Debug.Print vTMPs(v)
            'split each piece on the comma
            vVALs = Split(vTMPs(v), Chr(44))
            'stuff it underneath and split the dates
            With .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                vDTs = Split(Mid(Trim(vVALs(LBound(vVALs))), 2, Len(Trim(vVALs(LBound(vVALs)))) - 2), "--")
                .Cells(1, 1) = DateSerial(Left(vDTs(0), 4), Mid(vDTs(0), 6, 2), Right(vDTs(0), 2))
                .Cells(1, 2) = DateSerial(Left(vDTs(1), 4), Mid(vDTs(1), 6, 2), Right(vDTs(1), 2))
                .Resize(1, 2).NumberFormat = "yyyy-mm-dd"
                .Offset(0, 2) = Trim(vVALs(UBound(vVALs)))
            End With
        Next v
    End With
End Sub

Each element of the final split is put into the next blank row in column A.

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

dim strSplit() as string

strSplit =split(range("a1").value,";")
range("b1").value=strsplit(0)
range("b2").value=strSplit(1)

is the 1st bit :) then the same will apply to each of strSplit, using "," the 2nd time and will be date in 1 and name in the other

split("test show"," ") gives an array (0)="test" (1)="Show"

It can also be done using SUBSTITUTE and FIND/SEARCH in a formula, but can get tricky to track.

Upvotes: 0

Related Questions