Previous Topic Next Topic

Extract specific data and convert to a date

Solved/Closed
Frustrated@work - Jan 23, 2010 at 05:15 PM
 jk - Apr 16, 2010 at 09:13 AM
Hello,


I have a column full of cells that all have the same string with a few different numbers. Here is an example from cell K319:

53T044 11/20-12/06/09


What I need to do is extract the last 8 characters (eg. 12/06/09) and turn those into a proper date displaying as "December 06, 2009"

What I tried to do is use the function: =RIGHT(K319,8) which gives me the 8 digits, but I can't get Excel to display that date as December, 06, 2009 because it keeps wanting to think that the middle digits is the month and it always spits out "June 12, 2009". I've tried formatting the cells every which way and can't figure it out.

Would appreciate any help. Thanks in advance!!

2 replies

Answer 1 / 2
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 810
Jan 23, 2010 at 07:42 PM
Your are in that regions where the date is entered as dd/mm/yy whereas the data you have got is from some other regional format(for example my format) where excel date is entered as mm/dd/yy

so you try to use this formula
=(MID(A1,17,2)&"/"&MID(A1,14,2)&"/"&RIGHT(A1,2))+0

and format this as any one of date formats.
1
Answer 2 / 2
jk
Apr 16, 2010 at 09:13 AM
I had one excel file in which all data in one raw with differant head so i want to convert in to difference coloum
0