Excel Essentials for Auditors: Part 2 Text Functions

Starting your career as an auditor can be daunting, with exams, lots of audit and accounting standards which keep evolving and the realisation that accountants really do use Excel for everything!

Given this reliance on Excel, there hasn’t always been focused Excel training available incorporating the latest functionality within Excel.

This short series of articles runs through core functionality which should help you in your audit role. The skills can obviously be applied elsewhere but the example exercise, which you can download here, is very much audit focused.

As a trainee chartered accountant, you’ll often encounter trial balance (TB) data where codes and descriptions are bundled together in a single column. Extracting, splitting, and recombining this information efficiently is a vital Excel skill for auditors. In this second post of the series, we’ll explore practical text-based Excel functions that will help you work with such data efficiently.

While the video is the primary format, you can follow along with the article below for a summary of the video.

1. Traditional Approach: From LEFT/MID to Text To Columns

The Old Way: LEFT and MID

You might have seen formulas like =LEFT(A1, 4) or =MID(A1, 6, 10) to extract codes or descriptions. These work well if your general ledger codes are always the same length, but quickly get very complicated when codes vary in length or when there are multiple delimiters (like dashes) in your data.

Text to Columns

Excel’s Text to Columns tool (on the Data ribbon) lets you split a column based on a delimiter (such as a dash, comma, or tab) or at a fixed width. This is useful for simple cases but again struggles when your data contains multiple delimiters or inconsistent patterns. 

2. The Modern Approach: TEXTSPLIT, TEXTBEFORE and TEXTAFTER

TEXTSPLIT

The TEXTSPLIT function is a powerful way to break apart text based on a chosen delimiter. For example, you can split “1001 – Fixed Assets” into separate columns for the code and description. You can also specify whether to split into columns or rows, which is handy for more advanced scenarios.

TEXTBEFORE and TEXTAFTER

These functions allow you to extract everything before or after a specific delimiter. For example, =TEXTBEFORE(A1, " - ") will pull out the code, while =TEXTAFTER(A1, " - ") will give you the description. You can even specify which instance of the delimiter to use or count from the end using negative numbers in the formula’s parameter.

Tip: If you’re unsure how a function works, click the help link in Excel’s formula bar. The help pages often include videos and clear examples and load in a convenient panel on the side.

3. Recombining Data: TEXTJOIN, CONCATENATE and &

TEXTJOIN

TEXTJOIN lets you combine multiple cells into one, using a chosen delimiter (like a space or comma). For example, you can join a code and description back together with a space in between. You can also choose to ignore empty cells.

Tip: TEXTJOIN is especially useful for creating lists of email addresses separated by a semicolon, which is often required in online forms.

CONCATENATE and &

Older methods like CONCATENATE or using the & symbol work but are less flexible and the formula can often be harder to understand at a glance because all the delimiters/spaces need hard typing into the formula.

4. Finding Unique and Sorted Values: UNIQUE and SORT

UNIQUE

The UNIQUE function extracts a list of unique values from a range. For example, if “Motor Vehicles” appears multiple times with different codes, UNIQUE will list it only once. This is much faster and simpler than creating a pivot table just to see unique items.

SORT

Combine SORT with UNIQUE to get a tidy, alphabetically ordered list of unique descriptions, ideal for quick checks or reporting. 

Conclusion

Mastering these text functions, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, TEXTJOIN, UNIQUE and SORT, will save you time and frustration when working with text-based data. They’re especially valuable for auditors dealing with trial balances and client data in varied formats.

These modern text formulae work really well but for many text-based scenarios I typically use Power Query instead. Power Query is part of Excel (and other Microsoft products) but isn’t covered in this series as I’d classify it as nice to have from an auditor’s perspective, that said if you know these functions already or can immediately see the value in these I’d suggest you look into some of the Excel community’s Power Query material too.