This post is for personal reference but may be of some use to some people. Occasionally I export databases into Excel for work related business. One of the things which personally irks me about this process is that data which would normally be expected to appear in different columns ends up all jumbled up into one.
This tends to happen when exporting info associated with student names from the SITS database at work. So instead of getting a set of columns clearly separated by title, forename and surname I end up getting "Mr Random Person" all in one column. Not ideal if you want to arrange the data alphabetiacally by surname. Anyway, there is an easy solution to sort this mess out. I'm working with Microsoft Excel 2008 for Mac by the way.
The solution:
The solution is to delimit the selected text.
- Select the cell or column you want to split into several sections, then choose Data from the top of the screen, followed by Text to Columns to open the Convert Text to Columns Wizard.
- Excel will now ask you what kind of separation you'd like (Delimited or Fixed Width). Select Delimited, followed by Next.
- The Wizard will then present a number of division tools which may be suitable depending on the format of the text in the cells. Choose the Space or Tab options and select Next or Finish
NB: make sure you have enough space to for you columns to expand out into as Excel will overright material in the adjacent columns. Simpley insert a blank column by right clicking the cell range and selecting Insert
Not a particularly exciting or even interesting post but at least I know where to find the info as I forget every time I need to do this...
