There are many of us who have large complicated database lists in Excel where the information has been brought in by others or by different software. Sometimes this information is not clearly defined enough for our purposes. Some data may be of the form of two or more words in one cell and we would rather them be separated into separate cells.
There is an easy solution to this problem.
The list that you have, may look something like this.
You really need the names to be in separate columns.
- Make sure you have one empty column to the right of your data to be split if you have two words in the column to be split; more empty columns of you have more words.
- Select the all the cells in the same column to be split.
- Select Text to Columns tool from the Data Tab
which results in this dialog box 
- Choose the file type that best describes your data: in this case the first is appropriate.
- Select the Next Button
- Select the appropriate separator.
In this dialog box the “Treat consecutive delimiters as one” is checked as sometimes when people input data they hit the space bar a few too many times resulting in more spaces. If you should not have this checked you would not get the required result but a few empty columns in-between your first and last names.
- Select Next Button to go to Step two
- Select Finish Button if the settings here are correct. If you have left enough room then select OK in the next dialog box.
Your result will look like this 
If you have more words in your column to be split, then your result would look like this 
In this particular example there is a slight problem that some items actually need to remain together, for example Clam Chowder or Pepper Sauce.
In this tip we have only uncovered one small part of the tool. Do try out the rest.
