With a list of text as these two examples, (each in one cell, A1, A2 etc.)

03:04 stuff etc things whatever
04:23 bla bla bla bla

How can I add say, 5 seconds to the :04 and the :23
And have the list look like this:

03:09 stuff etc things whatever
04:28 bla bla bla bla

And it might be good to know how to add 1.05 to the strings and have it look like this:

04:09 stuff etc things whatever
05:28 bla bla bla bla


This code pulls the correct seconds strings out but I'm lost on how to add to 5 to a "number as text" and throw it back into the text statement.

To add 1.05, the code would need to be changed to include the minutes and seconds is my guess.

Thanks,
Howard


Sub StringAddFour()

Dim LRow As Long
Dim aRng As Range
Dim c As Range
Dim strMid As String

LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each c In aRng
With c
strMid = Mid(c, 4, 2)
MsgBox strMid
End With
Next

End Sub

Hi Howard,

Am Mon, 6 Oct 2014 18:55:40 -0700 (PDT) schrieb L. Howard:

03:04 stuff etc things whatever
04:23 bla bla bla bla

try:

Sub Add4Min()
Dim LRow As Long
Dim rngC As Range
Dim dblSub As Double

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
dblSub = TimeValue(Left(rngC, 5))
rngC = Format(dblSub + TimeSerial(0, 4, 0), "hh:mm") _
& " " & Mid(rngC, 7, 99)
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

try:



Sub Add4Min()

Dim LRow As Long

Dim rngC As Range

Dim dblSub As Double



With ActiveSheet

LRow = .Cells(Rows.Count, 1).End(xlUp).Row

For Each rngC In .Range("A1:A" & LRow)

dblSub = TimeValue(Left(rngC, 5))

rngC = Format(dblSub + TimeSerial(0, 4, 0), "hh:mm") _

& " " & Mid(rngC, 7, 99)

Next

End With

End Sub


Regards

Claus B.


Very nice, Claus.

Just to be sure I understand it...

This line pulls the time value out of the string and is Dimmed as Double to accommodate time and dates:
dblSub = TimeValue(Left(rngC, 5))


This formats the time value as hh:mm (where it was text in the cell)
rngC = Format(dblSub + TimeSerial(0, 4, 0), "hh:mm")


And the "+ TimeSerial(0, 4, 0)" adds 0 hours, 4 minutes, 0 seconds to the time value.

And this grabs all the text past the time value. (with a space and up to 99 characters)
& " " & Mid(rngC, 7, 99)

Is that correct?

Howard

Hi Howard,

Am Tue, 7 Oct 2014 01:31:13 -0700 (PDT) schrieb L. Howard:

This line pulls the time value out of the string and is Dimmed as Double to accommodate time and dates:
dblSub = TimeValue(Left(rngC, 5))

This formats the time value as hh:mm (where it was text in the cell)
rngC = Format(dblSub + TimeSerial(0, 4, 0), "hh:mm")

if you concatenate a time or a date with a string without formatting you
will get the value in decimal.

And the "+ TimeSerial(0, 4, 0)" adds 0 hours, 4 minutes, 0 seconds to the time value.

so it is easy to change to other additonal times

And this grabs all the text past the time value. (with a space and up to 99 characters)
& " " & Mid(rngC, 7, 99)

yes, that is all correct
If your text is longer you have to change the 99 to your maximum length


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Great. Thanks a bunch for the code and the info.

Regards,
Howard

If your text is longer you have to change the 99 to your maximum
length

Length is optional and so if left out the length will be everything
after the start position. Only specify length when you want to
'extract' a specific nuber of characters from the entire string!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Typo...

If your text is longer you have to change the 99 to your maximum
length

Length is optional and so if left out the length will be everything
after the start position. Only specify length when you want to
'extract' a specific number of characters from the entire string!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Tue, 07 Oct 2014 12:56:30 -0400 schrieb GS:

Length is optional and so if left out the length will be everything
after the start position. Only specify length when you want to
'extract' a specific nuber of characters from the entire string!

this is new to me. Thank you for this information.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Tue, 07 Oct 2014 12:56:30 -0400 schrieb GS:

Length is optional and so if left out the length will be everything
after the start position. Only specify length when you want to
'extract' a specific nuber of characters from the entire string!

I tested it. But it is only working with VBA. For the function MID into
the worksheet the third argument (length) is a must.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional