Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#6
|
|||
|
|||
Formatting Odd Number Strings
I used Dave O's solution. Thanks again.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Function with Different Formatting for Number | Excel Discussion (Misc queries) | |||
Number formatting when importing from Access to Excel | Excel Discussion (Misc queries) | |||
number formatting question | Excel Worksheet Functions | |||
Pivot Table Drill Down Number Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting (Date vs Number) | Excel Discussion (Misc queries) |