VBA + macro + to copy and paste

[llo]

I need to <NOBR>copy</NOBR> only part of a cell that holds (06:00am - 03:00pm). I only need to <NOBR>copy</NOBR> the 06:00. And then paste the "06:00" onto another worksheet into one cell on it's own. And then mimic that <NOBR>process</NOBR> for the "03:00". Can someone assist? All this doing so with a <NOBR>macro</NOBR> (<NOBR>VBA</NOBR>)

 

[Tom Urtis]

Is that what is really in the cell and do you know if the cell is hence a text value, not a time and date format, meaning you want to parse the values into two sets, one before the dash and one after?

 

[llo]

Yes, that is exactly what is in the cell. I would like the time format without the "am" ( if possible) along with after the dash, the time format without the "pm", if possible. Each time pasted into two individual cells.

 

[Tom Urtis]

Well, you stressed that you wanted VBA but you can accomplish this with formulas too.

If it is cell A1 that exactly contains, as you confirmed for example,
(06:00am - 03:00pm)

then this formula in cell B1 would return 6:00
=SUBSTITUTE(LEFT(A1,FIND(" ",A1)-3),"(","")

and this formula in cell C1 would return 3:00
=SUBSTITUTE(TRIM(RIGHT(A1,LEN(A1)-SEARCH("-",A1))),"pm)","")

If you really want a macro to do that, here is one which will put those values into B1 and C1 respectively, which you can modify (or not) for desired time format if you choose.

Sub test()
Dim strTime$
strTime = Range("A1").Text
With WorksheetFunction
Range("B1").Value = Trim(Mid(strTime, .Find("(", strTime) + 1, .Find("a", strTime) - .Find("(", strTime) - 1))
Range("C1").Value = Trim(Mid(strTime, .Find("-", strTime) + 1, .Find("p", strTime) - .Find("-", strTime) - 1))
End With
End Sub

 

[llo]

In addition, how would I format a display for a 24 hour clock setting. Meaning if the time shows 06:00pm and I want it to show 18:00. And visa versa, if it is showing 12:00pm, for it to display 12:00, instead of 00:00. I have an ending time function representing TIME(12,0,0). [For hour, minute and second], along with TIME(24,0,0), however, one of those function satisfies the 06:00pm to reflect 18:00 and the TIME(24,0,0) reflects the 12:000pm to be 12:00. How can I derive at a function that will satisfy both simoutaneously?

 

[llo]

Thanks for the response.