I have, as some people call it, an unhealthy obsession with Microsoft Excel. I get excited about anything that allows me to be more efficient. With Excel, I keep finding new and better ways to do just that.
While much of Excel revolves around numbers (all right, most of Excel revolves around numbers), there are some pretty fantastic formulas and functions that allow you to quickly manipulate text. So even if you are more focused on Bible studies and mail merges than income statements, Excel can help you.
The following are my favorite five formulas that have nothing to do with numbers. If you’re new to using formulas in Excel, check out Microsoft’s Excel help page.
I put this as #1 because it can be used in so many situations, especially once you export data into a single row CSV or XLSX document. CONCATENATE allows you to merge separate values together into a single cell. You can merge cells like contact values, or you can add your own text to the formula using double quotes.
To start this Excel formula, choose an empty cell. Type in an equal sign, then the formula word in all caps. After the word, type an open parenthesis with no space after the formula word. Separate values with commas, and close the parenthesis when you’ve come to the end. Keep in mind that you have to put spaces in the formula if you want them in the end result!
In the example below, I wanted to take each family name and turn it into The Household (which is how I might address a mailing label). So Abbott becomes The Abbott Household with the formula =CONCATENATE("The ",A2," Household"). To continue using this formula on the other names, just click and drag the bottom right corner of the cell where you typed the formula (see below).
As another example, if I had first names in column A and last names in column B, my formula to combine them would be =CONCATENATE(A2," ",B2).
These two position formulas are pretty straightforward. The RIGHT / LEFT formulas allow you to extract a certain number of characters from the right or left side of a cell. One way I often use it is to find the ZIP code of an address. Then I can sort or filter by the data in that column.
In the example below, I wanted to extract the last five characters of the cells in column A. The formula read =RIGHT(A2,5) with A2 being the field with data and 5 being the numbers of characters. To continue using this formula on the other addresses, I clicked and dragged the bottom right corner of the cell where I typed the formula.
Have you ever exported data and found that it exported in all caps, when you wanted proper case? What about the reverse? The PROPER / UPPER / LOWER formulas allow you to quickly and easily change the case of any text string.
The formula for these in incredibly easy, as the only thing to put between the parethesis is the cell with the data: =PROPER(A2).
This one is easily missed due to how simple it is. If you have a text string with extra spaces in front of or after the text or numbers, the TRIM formula will remove the spaces at the beginning or end of the string.
Just like above, this formula is simple, with data cell being the only information between the parethesis: =TRIM(A2).
Ok, this one is related to numbers a little bit. Excel comes with a variety of formats for things like date, time, or currency, and the formula TEXT allows you to set the format for the value. The example below shows you how easy it is to convert a date to full text.
To change the date form the MM/DD/YYYY format to the full text, I used this formula: =TEXT(A2,"MMMM DD, YYYY"). If I had changed the format to "MM DD YY", it would have shown the first date as May 16 74, the second as Oct 15 77, and so on. Microsoft has provided full list of TEXT function formats for you to review.
While there are plenty more options to choose from, I am going to limit myself to five options today. What are your favorite Excel formulas and how do you use them in the church?
Want to receive notifications about more content like this? Subscribe to our blog, Technology & Your Ministry!