Split A Cell At A Blank Space In Excel

Splitting cells at an empty space Useful for separating first and last names

Fred Savage David Ough Brian Adams Ken Mercurio Jim Leslie Darrol Groven Brian Aanestad Donald Dusette Steve Bacon Steve Thompson Jeffrey Bernards Steve Allen Sloan Bailey Tom Schwan Steve Fredette Donny Zitterow Meir Amsalam Ed Cyr Jim Lash Michelle Rickard Erbie Daw Greg Marustan Ray Larsen Bob Main Tim Vititoe Houston Blair Brian Aanestad Tim Ahern Jeff Bernards Alan Brandt David Buchanan Jim Cady Gregory Clayton Shane Cooper George Delano Scott Denton Gerry Dorfman Todd Edwards Kevin Elliss Chuck Freinht Don Gainer Curtis Gilley Ron Grey Don Grock Darrol Groven Anna Hewitt Bob Hollingsworth Jerry Jensen Denniss Jites Jeff Jones Bernard Kamine Terry King Mark Kosin

Fred

Savage

LEFT(A1,FIND(" ",A1)-1) Formula finds the first space, returning everything before it.

RIGHT(A1,LEN(A1)-FIND(" ",A1)) Formula finds the first space, returning everything after it.

In order to use the formula for multiple rows, just drag the cell down to those rows (place the cursor over the bottom right hand corner, when a "+" appears, left click and pull the cell down the desired number of rows). Next, highlight the rows copy them, then do a paste special|values. This is done buy clicking on edit|paste special. In the dialog box which appears, select values and click ok.

Split Cells

Join Cells

I've also created a VBA program to do the same thing, just highlight the cells in which you want to split the names. Then click on the "Split Cells" button, above. To rejoin cells, highlight the cells you wish to rejoin and click the "Join Cells" button, above.

**note: If these macros are not working, you may need to change the macro security setting. Do this by going to Tools|Macro|Security. Select the medium radio button in the dialog box. You will then need to close the workbook and re-open it. If you'd like to use these macros in any workbook you may have open, let me know and I'll show you how to put them in your personal workbook.

Al Kraemer Jim Lash Mark Love Justin Malloy Bill Marcotte Bob Martz Tom Mendenhall Ken Mercurio Tom Nolan Tom Pavlich Roger Peters Michael Prlich Kurt Rexius Tim Rivard Mitch Roberts Chris Roncoe Tom Schwah Vicki Sewell Mitch Tomovich Michael Urbanski Joe Valverde Tony Vedova Eric Wilson Paul Zollezzi Randy Douglas Maynard Milva Staci Baratti Gus Bruner John Shannon