Tech Yoda’s Tip of the Week: Splitting & Combining Cells in Excel

Sometimes you might be given an Excel document where either a) the information you want in two (or more) separate columns is all contained in one column, or b) the information you want in one column is split up into two (or more).  The most common example of this is names--either the first and last name are combined in one column and you want them in two or they're split into two and you want them in one.  Never fear, Microsoft Excel is here!

Problem: All the names are entered as one name into one column
Example: "Jay Money" instead of "Jay" and "Money" in separate columns
Solution: Text to Columns command

1. Insert a blank column (or several--one for each additional column you want the single column split into) to the right of the one you are dividing.
2. Highlight the column where the names are in the spreadsheet.
3. Go to the Data tab at the top of your page.
4. Choose Text to Columns.
5. Make sure Delimited is selected and click NEXT.
6. If the names are entered with just a space between then check only SPACE; if they are separated by a comma and a space, then check both COMMA and SPACE.
7. Click NEXT.
8. Then FINISH.

Problem: Combining multiple columns into a single column
Example: "Jay" and "Money" instead of "Jay Money" in a single column
Solution: Concatenate function

1. Insert a blank column to the right of those you are combining.
2. Highlight the top cell in that empty column that is in the first row where data is contained.
3. Type (just the bolded part--no outside quote marks) "=concatenate(".
4. When your cursor is just to the right of the open parenthesis, use your mouse to select the first cell (first name) to be joined into one large text cell.
5. Excel will automatically put the location of the cell into the parentheses.
6. Type (just the bolded part--no outside quote marks) "," ",".
7. Use your mouse to select the second cell (last name) to be joined into one large text cell.
8. Hit enter and the formula with produce an output of "First name Last name"
9. Click and drag down the lower right hand corner of the box to repeat the function for any other cells.