convert a complicated text to date format in ms excel 2007 -
i have text in following (many lines):
may 1, 2014, 11:26 ,second:51
ideas convert recognize-able (and can ordered) like: yyyy-mm-dd hh:mm:ss nice if can have column describing day (e.g. monday-sunday).
appreciate help. best regards, ben
it not clear question whether month abbreviated of written in full. may bad example. not clear if single digit hours have leading zero. next time, please provide bigger data sample.
my suggestion formula create date/time value can formatted , sorted.
=date(mid(a1,find(",",a1)+2,4)+0,match(left(a1,find(" ",a1)-1),{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0),mid(a1,find(",",a1)-2,2)+0)+timevalue(mid(a1,find(":",a1)-2,8))+time(0,0,right(a1,2))+0
format result format prefer. screenshot has custom format yyyy-mm-dd hh:mm:ss
Comments
Post a Comment