Hi there,
I need to convert the following string into Excel dates:
Jan 1, 2009
Jan 12, 2009
Jan 21, 2009
Right clicking it and changing it to date format will not work.
I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.
Does anyone have a better solution?
Thanks
Last edited by tony.nz; 09-15-2009 at 10:27 AM.
Hi,
select the range of dates, go to Data > Text to Columns and in Step 3 select Dates and MDY
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Does this work?
Select the column and go to Data|Text to Columns and just click Finish
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I would like it so it can be done in a formula, so when I paste in more data - I don't need to run any more functions (so the formula will just need to be dragged down)
Try:
=A2+0 where A2 is your original date string.
and format as date.
No sorry, just tried that and it doesn't workOriginally Posted by NBVC
What was the actual result?
When you click on one of those cells, what do you see in the formula bar, exactly?
The actual result is Jan 1, 2009 (it is what appears in the formula bar)
No spaces in front of Jan or after 2009
Does this work then?
=--(LEFT(TRIM(A2),FIND(",",TRIM(A2)))&" "&RIGHT(TRIM(A2),4))
if not, please post sample worksheet.
Even, just this:
=--TRIM(A2)
should work
Last edited by NBVC; 09-15-2009 at 09:33 AM.
I will need to use the attached file
This may be a stupid question but the "--" after the "=" sign, what does it do?
Well.
I only applied formula as I had previously prescribed:
=A1+0 and formatted as date.... and it worked fine.
The -- coerces text to numbers if possible...
How bizarre - when I opened the file; it gives me #VALUE! error messages, is there something obvious that I am missing?
Excel 2007 here...
It may be the regional setting are different... for you than the text in column A implies...
Try:
=--(MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1)&" "&LEFT(A1,FIND(" ",A1)-1)&", "&RIGHT(A1,4))
Works like a charm! Many thanks!
Great.. we got there...
Please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks