Join Dennis Taylor for an in-depth discussion in this video Using Text to Column for selected date formats, part of Cleaning Up Your Excel 2010 Data.
A powerful organizational tool and one of great help in cleaning up your data,…is the Text to Columns command. It's found on the Data tab in the Ribbon, and in this…particular worksheet we've got two examples of dates in columns A and C. And they…are different, and yet similar in one respect, they're trying to show, for example,…in cell A2, year, month and day data.…And at a glance, they might look the same, but we'll focus on column A first, and…recognize that in all the entries here, the four leftmost characters…represent a year; 2011, 2010, 2009,…and so on. And after looking at it for a little bit you'll quickly see that the two rightmost…digits as here, represent day of the month, and the two characters to the…left of that represent and actual month number.…
So for example, this is going to be May 31, 2011. And in all the examples here, or atleast…the ones we can see, and we assume the data is accurate and correct. We hope it is.…But we would like to convert this into a date format that we could really use in…
- Moving or inserting rows and columns of data with a simple drag
- Using Text to Columns
- Harnessing the Find and Replace command to replace data at the character level
- Dealing with special characters and wildcards during search
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.