Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel (or other) macro for cleaning date data
Does anyone have a good macro for taking date data that are in a
variety of formats and making them consistent? By format, I don't mean that the cells are formatted differently, but that the data are all recorded as, say, a string, but they all look different, e.g. "01/05/92" "01-07-84" "Jan 12, 1991" "January 1, 2000" "10 January '05" Is there any macro that is written to recognize that, say, the first two are in mm/dd/yy format and to put them into a consistent style, that the second, third, and fourth are in different formats, and to put them into mm/dd/yy, etc.? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel (or other) macro for cleaning date data
Record a macro and you will have one. It should include... Replacing the quote marks and apostrophe with nothing (Edit | Replace) Multiplying all of the dates by 1 (Edit | Paste Special | Multiply) Changing the number format to mm/dd/yy (Format | Cells | Number (tab) | Custom -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) wrote in message Does anyone have a good macro for taking date data that are in a variety of formats and making them consistent? By format, I don't mean that the cells are formatted differently, but that the data are all recorded as, say, a string, but they all look different, e.g. "01/05/92" "01-07-84" "Jan 12, 1991" "January 1, 2000" "10 January '05" Is there any macro that is written to recognize that, say, the first two are in mm/dd/yy format and to put them into a consistent style, that the second, third, and fourth are in different formats, and to put them into mm/dd/yy, etc.? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel (or other) macro for cleaning date data
Bliss/Ryan,
You could also use worksheet formulas. IF you actually have the double quote marks in each cell, then try: =DATEVALUE(SUBSTITUTE(A1,CHAR(34),"")) with the cell formatted for date, then copy down to match. If you don't actually have the double quotes and were just using them in your post, you could use =DATEVALUE(A1) Then copy the cells with your formulas, and paste special values, then delete your original column of strings. HTH, Bernie MS Excel MVP wrote in message oups.com... Does anyone have a good macro for taking date data that are in a variety of formats and making them consistent? By format, I don't mean that the cells are formatted differently, but that the data are all recorded as, say, a string, but they all look different, e.g. "01/05/92" "01-07-84" "Jan 12, 1991" "January 1, 2000" "10 January '05" Is there any macro that is written to recognize that, say, the first two are in mm/dd/yy format and to put them into a consistent style, that the second, third, and fourth are in different formats, and to put them into mm/dd/yy, etc.? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel (or other) macro for cleaning date data
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cleaning data | Excel Worksheet Functions | |||
Cleaning up data -- any way to globally fix this?? | Excel Discussion (Misc queries) | |||
Cleaning Data | Excel Discussion (Misc queries) | |||
Cleaning Up Data | Excel Discussion (Misc queries) | |||
Inconsistant Data Cleaning | Excel Discussion (Misc queries) |