Split names from one column into two
How many times have you received a long list of names in one column you needed split into two columns, so the first and last name are separated? This macro does it in seconds plus sorts the list, adjusts the column widths, and gives a total list count. Open the Names worksheet, name and define your macro, then record these keystrokes.
1. Press CTRL+Home, CTRL+A. Select Data>Text to Columns. In the first Wizard dialog box, click Delimited>Next. In the second box, choose the character that delimits (separates) your text. Our list is separated by spaces, so check Space>Next. In the last box, click Text>Finish.
2. Press CTRL+Home, then CTRL+A. Select Home>Sort & Filter>Custom Sort. In the Sort dialog, select column B in the Sort By field. Click Add Level, then select column A in the Then By field. For Sort On and Order, leave the defaults Values and A-Z, then click OK.
3. Press CTRL+Home. Press Shift-Right-Arrow to highlight A1 thru B1. Click Format>Column Width>15>OK.
4. Press CTRL+Home. Select Home>Insert Sheet Rows, twice. In A1, type Total Names. Use the right arrow key to navigate to B1, then enter this formula: =COUNTA( and press CTRL+Down-Arrow, End, Shift-Down-Arrow, Enter, CTRL+Home. The total appears in B1.
5. Stop Recording, save the worksheet as Names2. Open the Names file again and run the macro.
Split column and adjust for middle names
In Excel 2013, it's easy to divide one column of names into two columns, but what if half the list contains middle names/initials and half does not? This macro extracts the middle names/initials entries, rejoins them with the first names, then produces one list with first/middle name in first column and last name in second column. Open a three-names file, name and define your macro, then record these keystrokes.
1. Press CTRL+Home, CTRL+A. Select Data>Text to Columns. In the Wizard boxes, click Delimited>Next, Space>Next, and Text >Finish. One column becomes three.
2. Press CTRL+Home, CTRL+A. Select Home>Sort & Filter>Custom Sort>column C. Press Shift-Right-Arrow. Click Format>Column Width>15>OK.
3. Press CTRL+Home, Right Arrow twice. Press End once, Down-Arrow twice, Right Arrow once — this moves the cursor to the first empty cell in column C, then to the adjacent cell in column D. Type: STOP, press Up Arrow, End, Up Arrow. Type this formula: =A1&" "&B1, then press Enter, Up Arrow.
4. Press CTRL+C, Down-Arrow. Hold down Shift, then press End, Down Arrow, Up Arrow, Enter (copies formula). Press Up Arrow once, hold down Shift, press End, Down Arrow, Up Arrow (this highlights the range without STOP).
5. Press CTRL+C, CTRL+Home, select Paste>Paste Special>Values>OK. Press Escape, CTRL+Home, Right Arrow twice. Hold down Shift, press End, Down Arrow, CTRL+C, Left Arrow, Enter to copy last names. Press Right Arrow, Shift-Right-Arrow.
Sign up for CIO Asia eNewsletters.