Working with text formulas can help to polish Excel’s rough edges throughout your workflow

Text formulas can be super handy, both on the front and back ends of an application. On the front end, these formulas significantly expand your control of how data labels are displayed and augment your ability to output lists from a database. While Excel is not really a text-friendly editing environment, the automating power that it brings with these formulas can eliminate much of the need for awkward manual work.

Text formulas allow you query and directly manipulate strings

Text formulas allow you to manipulate the contents of strings. Some return information about the length, or position of a character, and others can operate on the string itself. All together, these formulas can be used to automate systematic text editing tasks. There are two types of use cases that lend themselves to text formulas:

1. Data Input and Cleaning: When organizing many sources of data, there is often a need to prepare the data in a format that will be accessible based on lookups elsewhere in the model. If you’re pasting values from a PDF, for example, there are often rows and/or columns that are pasted as a single text value, and sometimes data includes units that Excel interprets as text. Text formulas can be set up to automate most of your manual editing.
2. Labels and Lists: When designing outputs for your spreadsheet application, it can be useful to extract lists from a database, and you may want to have custom axis and data labels in charts. Clever text operations give you more flexibility when working with data that contains letters.

I recommend getting familiar with these formulas on simple tasks, so as to be ready for the situation where they can save you hours of demoralizing manual data editing.

Click the following buttons to read more about each formula.

The excel file embedded below demonstrates the use of these formulas. Note that these formulas are expansive in scope and may prove to be useful in a variety of circumstances.

All of the example formulas listed here look up data from the named table at the top of the sheet.