Split account number and name in Excel

Accounting systems often export each account as one piece of text, such as "6100 - Rent expense". That is easy to read and hard to work with. Before you can sort by code, look up an account, or total by account, you need the code and the name in separate columns, and the code stored as a real number. A few text functions do the job, and they keep working when the export changes.

Why exports put code and name in one column

A chart of accounts is the list of every account a business posts to, each with a code and a name. Inside the accounting system those are separate fields. When the system exports a report, though, it often joins them into one label so a person can read the report at a glance. Trial balances and ledger reports often arrive this way.

Joined labels cause three problems in a spreadsheet:

• Sorting. You want accounts in code order: assets, then liabilities, equity, revenue and expenses. A text label sorts character by character, which is not the same thing once codes have different lengths.
• Lookups. A mapping table, a budget or last year's balances will usually be keyed on the code alone. A lookup for 6100 will not find "6100 - Rent expense".
• Totals by account. SUMIFS and pivot tables group on exact values. If one export says "Rent expense" and another says "Rent Expense ", the groups split. Matching on a clean numeric code is far more reliable.

So the first step in preparing the data is to split the label into a code column and a name column.

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) includes transforming data: splitting a column into several and using text functions to extract or combine values [Official]. It is a 50-minute, performance-based exam, and its tasks describe the purpose instead of naming the function [Official]. You might read "put each account's code in column B as a number" and have to decide for yourself that LEFT, FIND and VALUE are the tools. The exercises in this lesson are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

How it works

FIND tells you where the separator is

Every split starts with one question: where does the separator begin? FIND(" - ",A5) returns the position of the first character of " - " inside A5. In "6100 - Rent expense" that is position 5, because the code takes positions 1 to 4. Everything else is arithmetic on that position.

SEARCH does the same job. The differences are that SEARCH ignores upper and lower case and accepts wildcards, while FIND is exact. For a fixed separator like a dash or a bracket, either works. Use FIND when case matters, and SEARCH when you are hunting for a word that might be capitalized differently.

LEFT takes the code

LEFT returns a number of characters from the start of the text. The code ends one character before the separator, so:

=LEFT(A5,FIND(" - ",A5)-1)

For "6100 - Rent expense" that is LEFT(A5,4), which gives "6100". For "308 - Owner capital" the separator is at position 4, so the same formula takes 3 characters. That is the point of using FIND: one formula handles every code length.

MID takes the name

MID returns characters from the middle of the text: a starting position and a count. The name starts 3 characters after the separator begins, because " - " is a space, a dash and a space:

=MID(A5,FIND(" - ",A5)+3,100)

The 100 is simply "more than enough". If you ask MID for more characters than are left, it returns the rest of the text without an error. That makes a generous count the simplest way to say "everything to the end".

VALUE turns the code into a number

Text functions always return text, even when the result looks like a number. "6100" made by LEFT is text, and Excel treats it as different from the number 6100. A lookup for the number will not match it, and a sort will place it with the text values. Wrap the extraction in VALUE:

=VALUE(LEFT(A5,FIND(" - ",A5)-1))

A quick way to spot the difference: by default, numbers align to the right of a cell and text aligns to the left. If your codes hug the left edge, they are still text.

TEXTBEFORE and TEXTAFTER in current Excel

Newer versions of Excel, including Microsoft 365, add two functions that do the position arithmetic for you:

=VALUE(TEXTBEFORE(A5," - ")) for the code, and =TEXTAFTER(A5," - ") for the name.

They are shorter and easier to read, but older versions do not have them. Learn the LEFT, MID and FIND pattern first: it works everywhere, and it handles a code that sits between two characters, as in the third example below.

Worked examples

Example 1: extract the name from four-digit codes

Juniper Landscaping's export has twelve accounts in A5:A16, each a four-digit code, a spaced dash and a name. The task is to put just the name in B5:B16, with one formula filled down. Here are four of the rows:

In B5, =MID(A5,FIND(" - ",A5)+3,100) starts at position 8 and returns "Prepaid insurance". Filled down, it returns "Sales" in one row and "Office supplies expense" in another, because the count of 100 covers short and long names alike.

Example 2: codes of different lengths, converted to numbers

Maple Lane Catering's codes run from 3 to 6 digits. The task is the code as a number in B5:B16 and the name in C5:C16.

In B5, =VALUE(LEFT(A5,FIND(" - ",A5)-1)). For "168288 - Inventory" the separator is at 7, so LEFT takes 6 characters and VALUE returns 168288. The name formula is the same MID as in example 1.

Look at the order of the export: 1570, 168288, 1766, 271343, 308. That is a text sort, which compares the first character, then the second, and so on. Once the codes are numbers, sorting column B gives 308, 450, 1570, 1766 and up, which is the order an accountant expects.

Example 3: code in brackets after the name

Northside Print Shop's export reverses the layout: "Cash (169)". The task is the name in B5:B16 and the code as a number in C5:C16.

The name is everything before " (": =LEFT(A5,FIND(" (",A5)-1).

The code sits between "(" and ")". It starts one character after "(", and its length is the position of ")" minus the position of "(" minus 1:

=VALUE(MID(A5,FIND("(",A5)+1,FIND(")",A5)-FIND("(",A5)-1))

For "Accounts receivable (15747)", MID starts at 22 and takes 27 − 21 − 1 = 5 characters: "15747". VALUE turns it into the number 15747.

Text to Columns and Flash Fill

Excel also has two tools that split a column without formulas.

Text to Columns (on the Data tab) splits each cell at a delimiter you choose, such as the dash, and writes the pieces into the next columns. It is fast for a one-off cleanup. You may need to trim the leftover spaces, and a name that contains its own dash would be split in the wrong place.

Flash Fill watches you type one or two results, spots the pattern and fills in the rest. It is handy when the pattern is obvious and the list is clean.

Both tools write fixed values. If next month's export is pasted into column A, the split columns do not change; you have to run the tool again. A formula recalculates as soon as the data changes, and it shows exactly how each result was made, so a reviewer can check it. For a workbook you will reuse every month, formulas are usually the better choice.

Common mistakes

Keeping the dash

Starting the name at the first space, with =MID(A5,FIND(" ",A5),100), returns " - Prepaid insurance". The dash and two spaces come along. Start three characters after the position of " - ", which is where the name really begins.

Cutting names to a fixed length

=MID(A5,8,10) looks right for "Sales" and "Freight in", but it turns "Prepaid insurance" into "Prepaid in" and "Office supplies expense" into "Office sup". Account names vary in length. Take everything after the separator with a generous count, not a fixed one.

Leaving codes as text

=LEFT(A5,FIND(" - ",A5)-1) returns "1570", which looks like a code but is text. It will not match 1570 in a numeric chart of accounts, and it sorts in text order. Wrap it in VALUE.

Assuming every code is four characters

=VALUE(LEFT(A5,4)) works for 1570 and even for 308, because "308 " with a trailing space still converts. It fails quietly on longer codes: 168288 becomes 1682 and 58420 becomes 5842. The result is a real number, so nothing looks broken, but it is the wrong account. Let FIND measure the code.

Keeping the closing bracket

In the bracket layout, =MID(A5,FIND("(",A5)+1,100) returns "169)" and "15747)". The bracket stops VALUE from converting, so the code can never be a number. Count only the characters between the brackets.