Formatting Odd Number Strings

J

jtmousel

I have imported a text report into Excel. For some reason the date
string on the text file reads as 20/05/0728. Is there a quick way to
reformat the string so it reads 7/28/2005 or even 2005/07/28, for that
matter. I really don't want to manually edit 300+ lines! Thanks.
 
D

Dave O

Assuming your fubared date is in column A, insert a new column
immediately to the right of that, and enter this formula:
=DATE(LEFT(A1,2)&MID(A1,4,2),MID(A1,7,2),RIGHT(A1,2))

This parses the existing text string into arguments used by the DATE
function, which generates a date useable by Excel.
 
S

Sloth

What is the cell value, and what is the format type? Is it a number like
20050728 with a custom format, or is it text 20/05/0728? You have a couple
options depending on what the cell values are.
 
G

Gary''s Student

For example, if you value is in A1, then in a helper cell put:

=MID(A1,7,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,2) & MID(A1,4,2)
 
Top