Excel Question #46

[Alex]

If the second column is all in those format with an 11 digit date you can use a formula to trim the value down to just the first 11 digits. So if in A1 you had:

Jan 06 2010 01:30PM

then in cell B1 put this formula:

=LEFT(A,11)

That will result in A2 having a value of Jan 06 2010. Copy the formula down the column. Then copy column B (select column, control-c) and then paste into column C as values only (select column, right click, paste special, values, ok). Delete columns A and B as unnecessry.

So now you have a column that is just a text-formatted date. To convert the text into a date format (this is messy but it will work):

In cell A2 copy this formula:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(G1,"Jan ","01/"),"Feb ","02/"),"Mar ","03/"),"Apr ","04/"),"May ","05/"),"Jun ","06/"),"Jul ","07/"),"Aug ","08/"),"Sep ","09/"),"Oct ","10/"),"Nov ","11/"),"Dec ","12/")," ","/"))

This will result in a five digit number that is an Excel date code.

Copy Column B into Column C as values only (see above). Now change the date format for the column to whatever date format you want and you should be good.

That messy formula assumes the month abbreviations are

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

If they aren't the formula will need to be tweaked.

[Kevy Baby]

Originally Posted by Alex

If the second column is all in those format with an 11 digit date you can use a formula to trim the value down to just the first 11 digits. So if in A1 you had:

Doh! Why didn't I think of this simple solution. Thanks for pointing out what should have been obvious.

Originally Posted by Alex

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(G1,"Jan ","01/"),"Feb ","02/"),"Mar ","03/"),"Apr ","04/"),"May ","05/"),"Jun ","06/"),"Jul ","07/"),"Aug ","08/"),"Sep ","09/"),"Oct ","10/"),"Nov ","11/"),"Dec ","12/")," ","/"))

I wonder if the time to create the above formula is less than just doing 12 search-and-replaces? Thanks for the tip though.
______________________

On a different Excel question:

I am a keyboard fanatic, especially when it comes to Excel: I prefer to use my keyboard for as much as possible (and NOT using the mouse). If I am navigating a spreadsheet to a cell that already has data in it, is it possible to "click" in a cell (with information in it already) using just the keyboard. Obviously, I could just start typing, but that would overwrite what is already in the cell. Right now, the only way I know how to do this is to click with the mouse.

Am I stuck using the mouse for this one detail? For as much as I live in Excel, this would certainly help my day.

[Alex]

Originally Posted by Kevy Baby

I wonder if the time to create the above formula is less than just doing 12 search-and-replaces? Thanks for the tip though.

Well, it would be 13 search-and-replaces and then still one formula and column copy-and-paste (to convert the text to a date). And it took me about 90 seconds to do the formula.

The advantage of the formula is that you are less likely to make a mechanical error than doing a repetitive task 13 times.

But mostly it was with an eye towards this being something done more than once. If it is a one off then search and replace isn't much less efficient.