Add to a "number as text"

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

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
 
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.
Click to expand...


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")
Click to expand...

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.
Click to expand...

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)
Click to expand...

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


Regards
Claus B.
 
If your text is longer you have to change the 99 to your maximum
length
Click to expand...

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...
Length is optional and so if left out the length will be everything
after the start position. Only specify length when you want to
Click to expand...
'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!
Click to expand...

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


Regards
Claus B.
 
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!
Click to expand...

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.
 
I tested it. But it is only working with VBA. For the function MID
into
the worksheet the third argument (length) is a must
Click to expand...

I was referring to its use in your code, and your comment to Howard
about changing its value >99. I understood you to be using it same as
is required in Worksheet.Function and thought it helpful to know that
the VB[A] works differently.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I tested it. But it is only working with VBA. For the function MID

the worksheet the third argument (length) is a must
Click to expand...



I was referring to its use in your code, and your comment to Howard

about changing its value >99. I understood you to be using it same as

is required in Worksheet.Function and thought it helpful to know that

the VB[A] works differently.
Click to expand...


Info noted, thanks.

Howard
 
Claus Busch said:
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.
Click to expand...
[....]
I tested it. But it is only working with VBA. For the
function MID into the worksheet the third argument
(length) is a must.
Click to expand...

Both statements are correct. So what is your point?

Comments should always be interpreted in the context in which they are made.

You are using the VBA Mid function, and GS's comment is about the VBA Mid
function.

VBA and Excel are different languages, so it should not be surprising if
functions with the same name behave differently in both languages.

For example, if we use the VBA Trim function, we might point out that it
removes only leading and trailing spaces.

You might point out that the Excel TRIM function also replaces two or more
spaces in the middle with one space.

Both statements are correct. One applies to the VBA function; the other
applies to the Excel function. So what?

PS: WorksheetFunction functions should behave like the Excel counterparts
they are intended to emulate. There is no WorksheetFunction.Mid function.
But there is WorksheetFunction.Trim function.
 
Back
Top