Split Bank Transaction Descriptions in Excel

TL;DR Bank descriptions often pack a transaction type, a payee name, a reference, a location and a card number into one long string. To make them useful, split them into columns. In Excel 365, TEXTBEFORE, TEXTAFTER and TEXTSPLIT handle most patterns; Flash Fill (Ctrl+E) is quick for one-off jobs; older versions can use LEFT, MID and FIND; and Power Query is best for anything you'll repeat monthly. Start by spotting the pattern for each transaction type, extract a clean payee first, keep the original description untouched, and use the cleaned payee for categorizing, pivot tables and accounting imports.

Look at a typical bank statement export and you'll see descriptions like these:

POS PURCHASE 03/14 COFFEE HOUSE #221 PORTLAND OR CARD 4821
ACH DEBIT ELECTRIC CO BILL PAY REF 88231904
ONLINE TRANSFER TO SAVINGS XXXXXX5678 REF #IB0K2Q
DIRECT DEP ACME PAYROLL PPD ID: 1234567890
CHECK 1042

These examples are illustrative; every bank formats descriptions differently.

They're readable for a person, but awkward for a spreadsheet. You can't easily group spending by merchant when the same coffee shop appears with ten different store numbers and dates. You can't match a payment to an invoice when the reference is buried in the middle of a string. And accounting software's payee matching works much better with a clean name.

This guide shows how to break descriptions into useful parts: the transaction type, the payee, references, card digits and embedded dates. It covers modern Excel functions, older formulas, Flash Fill, Power Query and a worked example, plus a lookup-table approach for merchants that never follow a pattern.

Start by finding the patterns

Before writing any formulas, sort or filter your descriptions and look for patterns. Most banks use a handful of structures, often one per transaction type. For example:

Write down what separates the parts. Common separators include:

• Double spaces between fields, which are easy to miss but very handy.
• Fixed keywords like "REF", "ID:", "CARD", "TO", "FROM".
• Special characters like *, # or /.
• Fixed positions, where each field starts at the same character on every row. This is more common in older systems.

A good first step is a Type column, because the right splitting rule usually depends on the type.

Step 1: preserve the original

Never overwrite the original description. Keep it in its own column and build new columns next to it. You'll want the original for searching, for audit trails and for when a formula gets something wrong.

Also clean it once in a helper column, so every later formula works on consistent text:

=TRIM(CLEAN(SUBSTITUTE(B2, CHAR(160), " ")))

That removes non-breaking spaces, non-printing characters and extra spaces. Note that TRIM collapses double spaces into single spaces, so if your bank uses double spaces as separators, split on them before trimming, or use a version without TRIM for that purpose. Our guide to cleaning bank statement data in Excel covers more clean-up techniques.

Step 2: extract the transaction type

If the type is always the first word or two, use a lookup list of known types and test which one the description starts with. Put your types in a table called Types, longest first:

Then in Excel 365:

=XLOOKUP(TRUE, LEFT(C2, LEN(Types[Type]))=Types[Type], Types[Type], "OTHER")

This returns the first type whose text matches the start of the description. Because longer types are listed first, "ONLINE TRANSFER TO" is found before a shorter partial match would be.

Step 3: extract the payee with TEXTBEFORE and TEXTAFTER

Excel 365's TEXTAFTER and TEXTBEFORE make most extractions short and readable.

Text after the type:

=TRIM(TEXTAFTER(C2, D2))

where D2 is the type from Step 2. For ACH DEBIT ELECTRIC CO BILL PAY REF 88231904, that gives ELECTRIC CO BILL PAY REF 88231904.

Text before a keyword:

=TRIM(TEXTBEFORE(E2, " REF", , , , E2))

The last argument is what to return if " REF" isn't found, so here it returns the whole string unchanged. Result: ELECTRIC CO BILL PAY.

You can chain them with LET:

=LET(rest, TRIM(TEXTAFTER(C2, D2, , , , C2)),
     TRIM(TEXTBEFORE(rest, {" REF"," ID:"," CARD"," #"}, , , , rest)))

Passing an array of delimiters like {" REF"," ID:"," CARD"," #"} tells TEXTBEFORE to stop at whichever appears first. That one formula handles several transaction types.

Card purchases

Card descriptions often include a date and a store number before the location. For POS PURCHASE 03/14 COFFEE HOUSE #221 PORTLAND OR CARD 4821:

=LET(rest, TRIM(TEXTAFTER(C2, "POS PURCHASE ")),
     afterDate, TRIM(TEXTAFTER(rest, " ")),
     TRIM(TEXTBEFORE(afterDate, {" #"," CARD"}, , , , afterDate)))

Result: COFFEE HOUSE. The store number and location are gone, so every visit to that chain groups together.

Step 4: extract references, IDs and card digits

References are useful for matching payments to invoices and for spotting duplicates.

Text after a keyword, up to the next space:

=LET(r, TEXTAFTER(C2, "REF ", , , , ""),
     IF(r="", "", TEXTBEFORE(r & " ", " ")))

For the ACH example, that returns 88231904. Appending a space before TEXTBEFORE means it still works when the reference is the last thing in the string.

Last four card digits:

=IFERROR(RIGHT(TEXTAFTER(C2, "CARD "), 4), "")

That's useful if several cards share one account, so you can tell whose spending is whose.

Check numbers:

=IF(LEFT(C2, 6)="CHECK ", VALUE(TEXTAFTER(C2, "CHECK ")), "")

Account masks on transfers: for XXXXXX5678, the last four digits identify the other account:

=IFERROR(RIGHT(TEXTBEFORE(TEXTAFTER(C2, "XXXXXX") & " ", " "), 4), "")

Regular expressions

Recent Microsoft 365 versions of Excel include regular expression functions such as REGEXEXTRACT. If yours has them, patterns like "a run of 6 or more digits" become simple:

=IFERROR(REGEXEXTRACT(C2, "\d{6,}"), "")

Google Sheets also has REGEXEXTRACT. If your Excel doesn't, stick with the text functions above or use Power Query.

Step 5: split everything with TEXTSPLIT

When fields are separated by a consistent delimiter, TEXTSPLIT spills them into columns in one go:

=TEXTSPLIT(B2, "  ")

Here the delimiter is a double space, which some banks use between fields. For a *-separated description such as SQ *BAKERY ON MAIN, splitting on * separates the payment processor prefix from the merchant name.

TEXTSPLIT spills into neighboring cells, so leave room to the right. It works row by row; to apply it to a whole column at once you'll typically combine it with other functions, or simply fill it down.

Older Excel: LEFT, MID, FIND and SEARCH

If you don't have Excel 365, the classic functions still work. They're just longer.

Text before a keyword:

=IFERROR(LEFT(C2, SEARCH(" REF", C2)-1), C2)

Text after a keyword:

=IFERROR(MID(C2, SEARCH("REF ", C2)+4, 100), "")

Text between two keywords (for example between the type and " REF"):

=IFERROR(TRIM(MID(C2, LEN("ACH DEBIT ")+1, SEARCH(" REF", C2)-LEN("ACH DEBIT ")-1)), "")

SEARCH is case-insensitive; FIND is case-sensitive. Use whichever fits your data.

Flash Fill for quick one-off jobs

Flash Fill guesses a pattern from examples. In a new column next to your descriptions, type the payee you want for the first row, then the second, then press Ctrl+E. Excel fills the rest based on the pattern it detects.

It's fast and needs no formulas, but:

• It produces static values, not formulas, so it won't update when data changes.
• It can guess wrong on rows that don't fit the pattern. Always scan the results.
• It works best when you run it separately on each transaction type, after filtering.

Power Query for monthly work

If you process statements every month, Power Query lets you build the steps once and refresh.

1. Load the data into Power Query (Data, From Table/Range).
2. Add a conditional column for Type, based on "begins with" tests.
3. Use Split Column by Delimiter on the description with a custom delimiter (such as " REF " or a double space), choosing to split at the left-most or right-most occurrence.
4. Use Extract, Text Before Delimiter and Text After Delimiter for single fields.
5. Use Replace Values to strip store numbers or prefixes.
6. Close and Load.

Next month, replace the source data and click Refresh. Our Power Query bank statement guide shows the wider workflow of bringing statement data in.

When there's no pattern: a merchant lookup table

Some merchants never follow a pattern. Online retailers, ride-share apps and app stores often append different codes every time. For those, a keyword lookup table works better than splitting:

Then find the first keyword contained in each description:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Merchants[Keyword], C2)), Merchants[Payee], "")

Use the split payee as a fallback when no keyword matches:

=LET(k, XLOOKUP(TRUE, ISNUMBER(SEARCH(Merchants[Keyword], C2)), Merchants[Payee], ""),
     IF(k="", F2, k))

where F2 is your formula-based payee. The same table can drive categories. Our guide to Excel formulas to categorize bank transactions builds this out further.

Payment processor prefixes

Card purchases at small businesses often go through a payment processor, and the processor's short code appears before the merchant name. You may see prefixes along the lines of SQ *, PAYPAL * or TST*, followed by the business name. The exact codes vary by processor and bank, so look at your own data.

These prefixes cause two problems. They make the same kind of merchant look different depending on how they take payments, and they push the real name further into the string. Strip them early, right after the clean-up step:

=LET(t, C2,
     p, {"SQ *","PAYPAL *","TST*"},
     hit, XLOOKUP(TRUE, LEFT(t, LEN(p))=p, p, ""),
     IF(hit="", t, TRIM(MID(t, LEN(hit)+1, 200))))

Keep the prefix list in a table rather than in the formula once it grows, so you can add new codes without editing formulas. If it's useful to know which processor was involved, extract the prefix into its own column before stripping it.

Tidy names for reports

Once you have a payee column, a few finishing touches make reports easier to read:

• Proper case. =PROPER(F2) turns "COFFEE HOUSE" into "Coffee House". It occasionally gets things wrong, such as "Mcdonald's" or "Abc Ltd", which you can fix through the merchant table.
• Remove trailing noise. Words like "PURCHASE", "PAYMENT", "BILL PAY" or "PPD" often add nothing. A short list of words to strip, applied with nested SUBSTITUTE or a Power Query step, cleans them up.
• Strip trailing numbers. Store numbers or terminal IDs at the end of names can be removed in Excel 365 with a formula that drops the last word when it's numeric:

=LET(t, F2, last, TEXTAFTER(t, " ", -1, , , ""),
     IF(AND(last<>"", ISNUMBER(--last)), TEXTBEFORE(t, " ", -1), t))

• Consistent spelling. Decide on one name per merchant and enforce it with the lookup table.

Clean names pay off every time you build a pivot table, filter by payee or set up rules in accounting software.

Worked example

Starting descriptions (illustrative):

After the steps above:

The payroll ID comes from the same pattern as the REF formula, with "ID: " as the keyword instead of "REF ".

A few refinements make it cleaner:

• Strip trailing codes like "BILL PAY" or "PPD" with SUBSTITUTE or a list of words to remove.
• Use the merchant table to turn "ELECTRIC CO BILL PAY" into "Electric Co".
• Put the check number in its own column and look up the payee from your check register.
• Convert payees to proper case with PROPER for readability.

Now a pivot table by Payee shows both coffee shop visits together, transfers can be filtered out by Type, and the electric bill's reference is ready to match against an invoice. See our bank statement pivot table guide for the next step.

Uses for split descriptions

• Categorizing spending by payee rather than by raw text.
• Finding subscriptions and recurring payments by grouping on payee. See finding recurring payments in a bank statement.
• Matching payments to invoices or bills by reference. See matching bank transactions in Excel.
• Spotting duplicates, since the same reference appearing twice is a strong clue.
• Cleaner imports into accounting and budgeting software, where a clean payee name improves matching rules.

Test your rules before trusting them

Splitting formulas fail quietly. A rule that works on fifty rows can mangle the fifty-first because a merchant name happens to contain "REF" or a city name looks like a keyword. Before relying on the results:

• Filter for blanks in the Payee column. Each blank is a row your rules didn't handle.
• Sort the Payee column and scan it. Odd fragments, cut-off names and leftover codes stand out quickly in a sorted list.
• Count distinct payees with =COUNTA(UNIQUE(Payee)) in Excel 365. If the number drops sharply after a change, check that you haven't merged merchants that should be separate.
• Spot-check each transaction type against the original description.

Do this again whenever you add a new month, because banks occasionally change their description formats.

Common problems

Formulas return errors on some rows. That row doesn't contain the delimiter you're looking for. Use the "if not found" argument in TEXTBEFORE and TEXTAFTER, or wrap in IFERROR.

Double spaces disappeared. TRIM collapses them. Split on double spaces before trimming.

The same merchant still appears under several names. Add more keywords to your merchant table, or strip store numbers and locations more aggressively.

Descriptions are cut off. Some PDFs wrap long descriptions onto a second line. Make sure the wrapped text was joined to the main row during conversion. SheetStatement keeps wrapped descriptions on the same row as the amount when it converts PDF statements, and checks the statement balance before export. The free plan includes 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans cover more.

Flash Fill got some rows wrong. Filter by transaction type and run it on each type separately, then scan the results.

Start with clean rows

Upload your statement PDFs to SheetStatement, check the rows and the balance, and download Excel or CSV. Then use these techniques to split descriptions into payees and references you can actually work with.

FAQ

How do I extract the merchant name from a bank description in Excel?

Find the pattern for that transaction type, then use TEXTAFTER to remove the prefix and TEXTBEFORE to cut off store numbers, references or locations. For merchants without a pattern, use a keyword lookup table.

What if I don't have TEXTBEFORE and TEXTAFTER?

Use LEFT, MID, SEARCH and FIND, or Power Query's split and extract tools, which are available in older versions of Excel for Windows.

Is Flash Fill reliable for bank descriptions?

It's quick for one-off jobs but can guess wrong. Run it separately on each transaction type and always review the results.

How do I pull out reference numbers?

Use TEXTAFTER on a keyword like "REF " and then TEXTBEFORE on the next space. If your Excel has regular expression functions, extract runs of digits directly.

Should I overwrite the original description?

No. Keep the original description and build new columns next to it. You'll need it for searching and for fixing mistakes.

Can I do this in Google Sheets?

Yes. Google Sheets has SPLIT, REGEXEXTRACT and REGEXREPLACE, which handle most of the same patterns.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.