Excel

Excel for Accountants: Tips and Tricks for Saving Time and Boosting Efficiency

May 26, 2020 John Siegel

Many accountants have grown up using Excel, and continue to use it every day for calculations, graphing, pivots, and more. But even experienced Excel users can discover new tips and tricks to save hours each day.

Recently, FloQast hosted a webinar with renowned Excel guru Jim Cline. Today, we’re going to show you a useful trick for using text functions without editing.

How to Use the Text Functions Without Editing

Let’s say you have a spreadsheet with a bunch of names. You’ve likely seen something like this, whether it’s a list of clients, employees, or vendors. 

As you can see, the list is in a “last name, first name” format. But you want to write a formula that will convert this list into a “first name, last name” format. 

You may be thinking, “I know how to do that. All you have to do is click on column A and use the Text-to-Columns functionality to split apart the first name and last name.”

While you can do that, if this data is linked to a database or another spreadsheet/CSV file, you might break the link. Here’s a better way.

Step 1: Using the LEFT Function

You start by naming column B of your spreadsheet “Last Name” because that’s the easiest one to do. In column B, you’re going to break out the last name by writing a formula using a few text functions, starting with the LEFT function.

You start by typing the following formula into column B:

=left(A2,6)

The text is in cell A2, that’s the first part of your argument. The second part of your argument is the number of characters. Since the first name on your list is Casson. There are six characters in that name, so, for now, you just type the number six and enter a closing parenthesis. When you hit enter, it returns the last name, Casson. 

Now, to copy that formula down to the other rows, hold your cursor over the little black box in the lower right-hand corner of cell B2, and your cursor will turn into a bold plus sign. If you click it, drag it down a few cells, and release it, you will copy the cells from B2 down to the point where you release it.

Now, you’ll notice that the formula worked fine for Casson because Casson has six letters. However, Hewel has only five characters in the last name, so it’s including the comma. And it’s cutting of the last N in the name Huntzen. You don’t want that — you want your formula to be able to return the entire last name. 

Step 2: Using the FIND Function

What you need to do is identify a common character in all of these names and base the LEFT function off of that common character. That common character is the comma. 

You’ll start by inserting a column to the left of the Last Name column and name it Find because that’s the name of the function you’re going to use.

In cell B2, you type the following formula:

=find(“,”,A2)

Since you want to find the comma, and the comma is not a number or a date, you have to enclose it in quotes. When you hit enter, it gives you a result of 7, because the comma in “Casson, Daymond” is the seventh character.

Now, if you copy that formula down, you see that the comma appears in different positions based on the name. 

Now, you go over to your formula in the Last Name column. Instead of using the number six in your formula, you can change that part of the formula to give you whatever is in cell B2 minus one.  Your new formula in the Last Name column will be:

=LEFT(A2,B2-1)

When you copy that down, you’ll see it returns all the correct last names.

Step 3: Using the Data Fill Function

Now, we’ve been using the Data Fill functionality and just dragging it down over the necessary rows, which is fine if we only have a few dozen rows. While you can copy it down for thousands of rows, that would take a while. There’s a better way.

Instead of clicking and dragging, hold your cursor over the Data Fill functionality and double click it. Excel copies that formula all the way down to the very last contiguous row.

Now, you can hit the End key on your keyboard or scroll down to the bottom of your list and see that all the last names turned out just fine. 

The next thing we need to do is break out the first names on our list. To find out how to do that, check out part two of our Excel Tips and Tricks blog series or watch our free webinar: Excel-lent Tips & Tricks!