Excel Question #46

[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.