PDA

View Full Version : advanced replace macro



killbill
02-14-2014, 02:55 PM
Hello
I have a spreadsheet with loads data with times and dates.
1)Column A cells include time like 15:00:00.
I would like a macro that replaces the cell with only the minutes.
e.g 15:00:00 replace with 0, 15:01:00 replace with 01, 15:02:00 replace with 02, and so on.

2)Column B has dates like 01.02.2008.
I would like a macro that replace the cell with only the last 2 digits of the year.
e.g 01.02.2008 replace 08, 05:01:10 replace with 10 and so on.

Thank you in advance!

p45cal
02-14-2014, 04:23 PM
cross posted:
http://www.mrexcel.com/forum/excel-questions/757871-advanced-replace-macro.html
http://www.excelforum.com/excel-programming-vba-macros/989513-advanced-replace-macro.html#post3585942

Aussiebear
02-14-2014, 06:57 PM
One can only hope that killbill did the right thing and notified the other forums that the thread has been cross posted elsewhere.

killbill
02-14-2014, 07:13 PM
Sorry guys but honestly I didn't know that it was wrong!

westconn1
02-14-2014, 09:20 PM
very basic anyway


for each cel in range("a:a")
if isempty(cel) then exit for ' exit on empty cell
cel.value = mid(cel.value, 4, 2)
cel.offset(, 1).value = right(cel.offset(, 1).value, 2)
next

killbill
02-15-2014, 09:55 AM
Thank you westconn1.And sorry for cross-posting.

Aussiebear
02-16-2014, 02:01 AM
Cross posting suggests that you have little faith in expecting a positive response from this forum. Can I say that those people who contribute to this forum do so on a voluntary basis. and as such their contributions a valued. Whether they are right or wrong is irrelevant, they have tried their best to provide a solution to your issue, and for that you need to be thankful. Please acknowledge others when gaining an advantage ( knowledge). There are others who have gained a reputation for serial cross posters and they gain very little response where ever they post

killbill
02-16-2014, 07:46 AM
I am grateful.
I did not know.Sorry for that.