Colon as a Delimiter

[atr8340]

I am trying to take a string (specifically one in the format "2008-09-01 00:00") and write an if statement to perform a process if the value to the right of the rightmost colon is anything but "00"

I have extracted the text from the cell using the code:

For h = 2 To NumberOfRows
  Cells(h, 1).Select
  Dim v As Variant
  Mytext = Cells(h, 1).Text

"Mytext" displays "2008-09-01 00:00"

I do not know how to use a colon as a delimiter to find the value of the last two values in the string. Would I use the "Split()" function?

[Rabbit]

Split could be used. You could also use InStrRev in conjunction with a Right.

[patjones]

Split( ) actually returns an array, so you need to Dim an array and then do the assignment...

Dim arr() As String
arr = Split(MyText, ":")

You can then reference the array elements as you wish.

Pat

[Rabbit]

If you only need to use it once and you know which element you want, you can do this

Split(MyText, ":")(1)

[ADezii]

Yet another approach should you have multiple Colons in the String:

Dim strTest As String

strTest = "2008-09-01 00:100"

'See if the Value to the right of last Colon <> "00"
If Mid$(strTest, InStrRev(strTest, ":") + 1) <> "00" Then
  'Process Code here
End If

[NeoPa]

Austin Redd:
I do not know how to use a colon as a delimiter to find the value of the last two values in the string. Would I use the "Split()" function?

I'm not sure what you mean by the value of the last two values in the string, as you've only explained a requirement to see the value after the colon. I guess you probably mean last two digits. If so, the following code, as explained by Rabbit in post #4, will give you access to that :

Split(MyText, ":")(1)

[ADezii]

@NeoPa: that logic would not work for the 'rightmost' Colon, as indicated by:

write an if statement to perform a process if the value to the right of the rightmost colon is anything but "00"

This would indicate to me that there can be more than 1 Colon in the String. Off Tangent again? (LOL)

[Mihail]

Dim LastCharacters As String

For i = Len(MyText) To 1 Step -1
    If Mid(MyString, i, 1) = ":" Then 'The character number i is ":"
        LastCharacters = Mid(MyString, i+1)
Exit For
    End If
Next i

Debug.Print LastCharacters

[NeoPa]

That's good critical thinking ADezii, but in this case I expect it's just a case of the OP being unable to express their question clearly enough. Notice the following :

Austin Redd:
(specifically one in the format "2008-09-01 00:00")

I worked on the basis that what they'd meant to say was :
write an if statement to perform a process if the value to the right of the colon (found towards the right-hand end of the string) is anything but "00"

If they hadn't already stated specifically what the expected format was then your querying this would be well worth raising and the code maight well need to be done differently. This code only handles the minutes value and never any seconds value if that were to be included.

[patjones]

If the format of the string is always as shown in the OP's example, then I really think this is a simple matter of using Split( ) in the manner that Rabbit indicates in post 4.

On the other hand, if the format has a varying number of delimiters, in some fashion that the OP has not pointed out to us yet, then it makes good sense to use an array as I show in post 3. This way, regardless of whether the string splits into two, five, or fifteen smaller parts (or whatever), you can always just reference the last element of the array to obtain the rightmost part of the parent string.

Pat

[NeoPa]

As the OP used the word explicitly in describing the format of "2008-09-01 00:00" I doubt that is the case. Assuming it were however, you might instead use :

Split(MyText, ":")(UBound(Split(MyText, ":")))

You could store it into an array first to save running the function procedure twice.

[atr8340]

Thank you ADezii. That gave me what I needed to work with. The code I used was this:

'find the last row in the "A" column
numberofrows = Range("A65536").End(xlUp).Row
'write a for statement to iterate through every row except for the column header row (A1)
For h = 2 To numberofrows

'Use my iteration variable "h" to search through the text in each cell
Cells(h, 1).Select
Dim MyText As String
MyText = Cells(h, 1).Text

'If the last two digits in "MyText" are not "00"...
If Mid$(MyText, InStrRev(MyText, ":") + 1) <> "00" Then

    'Place zeros at the end of the two cells above the "Hth" row
    'Use these cells to preform an autofill operation
    'This replaces the last two values in every cell below
    Cells(h - 2, 1).Select
    Dim MyText2 As String
    MyText2 = Cells(h - 2, 1).Text
    NewText = Mid$(MyText2, InStrRev(MyText2, ":") - 13)
    Cells(h - 2, 1).Value = NewText & ":00"
    Cells(h - 1, 1).Select
    Dim MyText1 As String
    MyText1 = Cells(h - 1, 1).Text
    NewText2 = Mid$(MyText1, InStrRev(MyText1, ":") - 13)
    Cells(h - 1, 1).Value = NewText2 & ":00"
    Range(Cells(h - 2, 1), Cells(h - 1, 1)).Select
    Selection.AutoFill Destination:=Range(Cells(h - 2, 1), Cells(numberofrows, 1)), Type:=xlFillDefault
End If
Next h