Microsoft Excel Saved My Sanity: =B1&" "&A1

Turn this ... into THAT in seconds! 

Do you fear retyping students' names, due to potential errors? Does entering student names for various documents and events become time-consuming? Want to learn how to complete these tasks in seconds instead of hours? 

Follow these 5 Steps and NEVER retype a student's name again! 
(Cue excited yelling by audience members attending taping of Oprah's 'Favorite Things' show) 

Microsoft Excel is an educator's friend. Why? Because the program's capabilities provide time-saving functions. The function presented below is my personal favorite and saves me from hours of headaches and frustrations. Similarly, if your school's music educators, coaches, secretaries, office workers, etc. do not know about this trick, be sure to share it with them! Can be used for concerts, banquets, athletic programs, graduation ceremonies, etc. The true value of this function occurs when there are many names being dealt with. 

When I receive an Excel spreadsheet containing my students' contact information, it typically appears in alphabetical order in this format below (with additional student address data, etc.) ...

Names in this form (contained in a single cell within Column A) are absolutely useless. Sure - I can sort by last name, grade and whatever other data are present in the columns, but it mets very few actual needs.


STEP 1: INSERT EXTRA COLUMNS
The first step is insert two additional columns (three or four to be extra safe, in the event a student has multiple middle names) to the right of Column A. Highlight Column B, right-click and select "Insert". Each word currently in Column A will be shifted into its own column in the next step.

STEP 2: TEXT TO COLUMNS 
Highlight Column A and select Text to Columns (under the Data tab). When doing this, the following window will appear.

Be sure "Delimited" is chosen and click "Next" 

"Tab" should automatically be selected, but ensure it is - as well as "Space".  

"Semicolon", "Comma" or "Other" provides other delimiting features.

Click "Finish" to proceed, I never worry about the Excel Wizard's "Step 3" 

STEP 3: REMOVE COMMAS

Following STEP 2, your document will appear like this. 

Results of Delimiting

We need to remove all commas present in Column A. Go to "Edit" and select "Replace". 

Enter the character you want to remove. In this example, we want Excel to Find "," and replace it with nothing. Select "Replace All" 

Excel will locate EVERY comma and remove it. By choosing various "Within" and "Search" parameters, Excel will be more selective. 

STEP 4: SAVE AS - for future use of middle names.
This is how the spread sheet now looks. For my purposes, I rarely use middle names so I often remove them after I "SAVE AS" the document, so I can salvage all the work I have already accomplished. Right click and clear contents.

Here is where the good stuff is about to happen. 

STEP 5: =B1$" "&A1

First, delete the contents of Column C and type (or click the respective cells) into C1: 

=B1&" "&A1 (includes a space) This will format all the names to appear as FirstName LastName 

Hit the Return/Enter key, and the cell will appear as below. Excel combined cell B1 (John) and cell A1 (Adams) and included a space (" ") between.

Click the small blue square at the lower right of cell C1. This will cause Excel to continue this formula throughout the document, automatically! 

See! I told you!

Add some space to Column C, to see the entirety of the names. 

By having additional data in the subsequent rows (instrument section, team, position, GPA, etc.) you will be able to sort the names in ways that suit your purposes. For example, I can sort Column D by grade (freshman-senior) and then copy and paste each grouping of names into a new document. 

IMPORTANT: You must paste by selecting "Paste Special". Depending on your software, there will be various options to choose. For example "Values" or "Unformatted Text". If not, the result of =B1&" "&A1 will show an error and/or be placed in a Word document within a table.

Results pasted into a word document.

Thanks for reading! 

@kevindengel

Was this post helpful to you, or did you send it to someone else? 

Please let me know in the comments!