Since I originally started this blog to help me keep track of coding and computer-related info, I thought I would write down some of my favourite Excel formulas for posterity. Most of these reside in an excel spreadsheet I have carried with me for over a dozen years but here they are appearing on the internet for the the very first time!

Last Name First Name

Simple and prevalent but a lot of people just don’t understand how handy spreadsheets can be for manipulating textual data and do it the manual way.

Bill to Name	First	Last
Blow, Joe	Joe	Blow
Simmons, Mike	Mike	Simmons

 

Bill to Name	First	Last
Blow, Joe	=RIGHT(A2,(LEN(A2)-SEARCH(“,”,A2,1))-1)	=LEFT(A2,SEARCH(“,”,A2,1)-1)
Simmons, Mike	=RIGHT(A3,(LEN(A3)-SEARCH(“,”,A3,1))-1)	=LEFT(A3,SEARCH(“,”,A3,1)-1)

A few things to know about calculations

• they all start with the “=” sign.
• calculations are often made up of many, nested, formulae.
• you refer to specific cells by their coordinates in the grid. “A2” refers to the cell in column A, row 2
• spreadsheet calculations are hierarchical, just like you learned in grade school. So brackets and nesting brackets are important. The spreadsheet will solve for the deepest level first and then work outwards.
• Things in quotes are treated as text; things not in quotes are treated as elements of the formula.
• Data elements within a formula are separated by commas

So, in the above, you first need to visualize the problem. Essentially I want everything after the comma to go in column 2 and everything before the comma to go in column 2. So that means first finding the position of the comma.

=SEARCH(“,”,A2,1)
search (for a comma “,”, in cell A2, and look for the first one to appear)

The syntax for this formula and all the others that Excel uses can be found by hitting the fx button right by the formula bar—there are a bazillion of them. Once you’ve found the comma you can then grab everything to the right (or left) of it. To do that you need to subtract the position of the comma from the overall length of cell A2.

=LEN(A20)-COMMA POSITION

To compensate for the space between the  comma and the first name you add a -1 — be sure to remember the brackets because you are subtracting the 1 from the whole calculation.

=(LEN(A2)-COMMA POSITION)-1

So now you have the elements necessary to use the right formula whose syntax calls is Right(text, number of characters).

=right(A2, (length-comma position)-1) or
=RIGHT(A2,(LEN(A2)-SEARCH(“,”,A2,1))-1)

To find the last name is almost the same but using the Left formula, which has similar syntax to RIGHT. It’s a bit easier because you don’t have to subtract from the length to find the number of characters. You still have to subtract 1 to account for the comma itself.

=left(A2,position of the comma-1) or
=LEFT(A32,SEARCH(“,”,A2,1)-1)

Simple huh? I know, I know, it is complex and mind numbing. That’s why I generally figure these calcs once, store them in a master spreadsheet and then reuse them when necessary. Remember that Excel’s fill down command will allow you to repeat the calculation for as many rows as you need and will automatically update the cell numbers to accommodate you.

Putting it Back

Changing it back is even easier.

First	Last	Bill To Name
Frank	Jones	Jones, Frank

=B2 & “, ” & A2

Cell C2 is equal to cell B2 plus a comma and a space (in quotes because they are text not data) and then cell A2. Simple!