String Loop Help Using VBA in Access or Excel - Need Ideas!

[wareagle1984]

I am stuck..the bottom line is I have a string:

String ="01/01/2014 I did this 02/05/2015 I just did this as well 05/06/2017"

Essentially I need to build the string as such:
NewString = "01/01/2014 I did this
02/05/2015 I just did this as well
05/06/2017"

I have been trying to write a loop in the string (and recordset) using a number of techniques, such as using INSTRREV to find character positions of the years (Note the years span over a decade) and inserting a chr(10) after the position but have not had much luck. I can do this in vba using access or excel - I really just need someone to help bounce some ideas off.

Thanks for any help or ideas, I am new to this forum but have a few years experience of VBA, Access and Excel.

 

[wareagle1984]

I should provide more clarity, I need to break each line AFTER each of the various number of years

 

[Micron]

You really have to analyze how consistent your string will be - depending on the approach, some or all factors have to be constant. In this case, I'd guess that my take requires the very first segment to always begin with a 10 character date. So loosely put, Mid function, begin at position 11 (i), test for numeric character (IsNumeric) if not, move to next, add 1 to i, repeat. When true, assign Len(string,i) & vbcrlf to a string variable, Mid over to i +11 and test numeric again. Rinse and repeat.
Probably flawed, maybe even a better way. I'll think about that as you ponder my suggestion.

 

[Micron]

so now it's this?
"01/01/2014
I did this 02/05/2015
I just did this as well 05/06/2017"

 

[wareagle1984]

Thanks, I thought about that - the issue I have is this, think of this string as a comment field more or less so its subject to being highly variable. I think your approach would work, as you said, if I had some more constants in the string. I do know that all date fields are 10 digit, but the string may have 3,4,5.. sets of 10 digit fields. I thinking now about running through the string using a recordset and parsing out 4 characters at a time until finding the year, and then using a select case for each year to build in the line break...That made sense in my head but perhaps not how I just explained it!

 

[Micron]

The length wouldn't matter and can be determined and used as a brake if need be. There's also the possibility that you can Split() out the dates if that's all you need. That would be much simpler, but I'm not sure I fully understand if you're just needing that or you need to break a long string so that dates are at the end of a line that is separated by paragraph breaks. This will grab the first date if the array is declared:
arySplit = Split("01/01/2014 I did this 02/05/2015 I just did this as well 05/06/2017", " ", -1)
Debug.Print arySplit(0)

 

[wareagle1984]

So think of it like this...I am looking at each of the dates and for every date after the first one I need to insert a line break before the date so essentially it would look something like the following:

String ="01/01/2014 I did this" & chr(10) & "02/05/2015 I just did this as well" & chr(10) & "05/06/2017 I also did this and some more"

So what I have been trying is parsing through the string in 4 character increments isolating the years in a select case statement and trying to build a new string with the breaks. Thank you very much for joining in on this.

 

[Micron]

I suppose breaking down your actual strings in a manner you've defined makes more sense than leaving the last date on a line of it's own. Maybe you could use the following more or less as is, or modify it to suit whatever sort of line wrapping you need. I went this route because you "guaranteed" that the dates would be 10 characters. The only thing I haven't allowed for is if a non date character is a digit. You'd have to apply further tests.
I get one leading space in the immediate window on the first line; not sure why but I'll leave you to take care of that.

Public Sub testSplit()
Dim strAddChar As String, strConcat As String, strInput As String
Dim i As Integer, c As Integer

strInput = "01/01/2014 I did this 02/05/2015 I just did this as well 05/06/2017"
strConcat = Left(strInput, 10)
c = 11
'start at 11 since we know Left 10 is a date
For i = 11 To Len(strInput) - 1 'unsure if -1 matters
    strAddChar = Mid(strInput, c, 1) 'check character at c (11 at start)
    If Not IsNumeric(strAddChar) Then
        strConcat = strConcat & strAddChar
        SendKeys "^g ^a {DEL}" 'clear imm. window; disable after testing
        Debug.Print strConcat
        c = c + 1
    Else 'character is a number (part of a date??)
        strConcat = strConcat & vbCrLf & Mid(strInput, c, 10) 'grab next 10 date characters
        SendKeys "^g ^a {DEL}" 'clear imm. window; disable after testing
        Debug.Print strConcat
        c = c + 10 'move pointer by +10 since we just added a date
        i = i + 10 'counter needs to catch up
    End If
Next

End Sub

OUTPUT:
01/01/2014 I did this
02/05/2015 I just did this as well
05/06/2017

 

[xenou]

Also possible invoking regular expression (but only tested on the one example):

Sub foo()

Dim str As String
Dim re As Object 'As RegExp
Dim myMatch As Object 'As Match
Dim myMatches As Object 'As MatchCollection

'//Microsoft VBSCript Regular Expressions 5.5
Set re = CreateObject("VBScript.RegExp")

str = "01/01/2014 I did this 02/05/2015 I just did this as well 05/06/2017"

With re
    .IgnoreCase = True
    .MultiLine = True
    .Global = True
    .Pattern = "\d\d\/\d\d/\d\d\d\d"
End With

If re.Test(str) Then
    Set myMatches = re.Execute(str)
    For Each myMatch In myMatches
        str = Replace(str, myMatch, vbNewLine & myMatch)
    Next myMatch
End If

Debug.Print str

End Sub

Result:
01/01/2014 I did this
02/05/2015 I just did this as well
05/06/2017

 

[Micron]

Thanks, I never got into regular expressions. Maybe I should read up on it.
Then again, I'm at the stage when I learn a new fact, an old one falls off the shelf.