Formatting Odd Number Strings
 

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.

Formatting Odd Number Strings
 

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.

Formatting Odd Number Strings
 

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.

"jtmousel" wrote:

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.

Formatting Odd Number Strings
 

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)
--
Gary''s Student


"jtmousel" wrote:

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.

Formatting Odd Number Strings
 

This work perfectly. Thank you!

Formatting Odd Number Strings
 

I used Dave O's solution. Thanks again.