Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
Great. Thanks a bunch for the code and the info. Regards, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
I tested it. But it is only working with VBA. For the function MID
into the worksheet the third argument (length) is a must 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
On Tuesday, October 7, 2014 11:39:33 AM UTC-7, GS wrote:
I tested it. But it is only working with VBA. For the function MID into the worksheet the third argument (length) is a must 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 Info noted, thanks. Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add to a "number as text"
"Claus Busch" wrote:
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. [....] I tested it. But it is only working with VBA. For the function MID into the worksheet the third argument (length) is a must. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
macro for converting number stored as "text" (or preceeded with ') to "number" formatting | Excel Programming | |||
macro for converting number stored as "text" (or preceeded with ') to "number" formatting | Excel Programming | |||
how to converts a number to text eg. "2" become "two" | New Users to Excel | |||
how to converts a number to text eg. "2" become "two" | New Users to Excel |