Tutorial 2: How to swap First & Last names in excel

So, I work on campus in a very important office and I see a TON on important names, dollar amounts, ranges of dollar amounts, etc. Me, the "student assistant", sometimes have to format excel sheets with a hundreds of raw data entries into easier to read sheets. In my office, people swap files like kids swap germs in 1st grade. Therefore, there are plenty of versions of each file with different formats and inconsistencies. Some of the problems I run through often (in sheets with enough entries to NOT do it by hand)and taught myself how to fix are the following: Swap "First, Last" to "Last, First" and Switch caps "Last, First" to "LAST, FIRST". Since the quick functions on "excel help" are known to cause problems for these issues. Here is a short tutorial on how to switch "Last name, First name" into "First name, Last name".

You will be using this function "=Mid(A2&" "&A2,Find(" ",A2)+1,Len(A2)-1)". Basically, what it does is find the " " (space) in the selected cell and switch the text of before and after the " " (space) in the cells selected. Let's say you start of like this...

Photo taken by: Tobar, Cesar.

You want to make space by adding two columns between "A" & "B". Such as...

Photo taken by: Tobar, Cesar.

Paste this formula in B1 "=Mid(A2&" "&A2,Find(" ",A2)+1,Len(A2)-1)". Then drag all the way to the bottom using the "+" symbol at the bottom of "B1". Note, if you DO NOT have headings as I do, you will be switching the "A2" into "A1".

Picture Taken by: Tobar, Cesar.

Next, You may think that you're done and you can use (copy/paste) our new column for new sheets. Column "B" is currently just formulas that output the text displayed. You must selected the "output" in column "B", copy and "paste special" "values only" in column "C" like this...

Photo Taken By: Tobar, Cesar.

Now you just delete column "B" and you may use the text for your editing pleasure. Then, you're done! Feel free to blog about your own quick solutions to everyday excel struggles to help others. If you get stuck, be sure to use the "Excel help" for a quick and painless fix. Have a great day, see you next blog.