Clean text data in Excel: tidy names, build memos, fix imported amounts

Accounting data rarely arrives clean. Vendor names are typed by different people, bank files bring amounts in as text, and a memo line has to be stitched together from three columns. A handful of text functions fix all of this, and they fix it the same way every time, so the next import takes seconds instead of an afternoon.

Why messy text breaks accounting work

To a person, "Harbor Insurance" and "harbor  insurance " are the same vendor. To a spreadsheet they are two different strings. That one difference quietly breaks three things accountants rely on:

• Matching. When you compare a vendor list with an invoice register, a name with a double space or a trailing space does not match. The invoice looks unpaid, or the vendor looks new.
• Lookups. A lookup searches for an exact match. If the key in one table has an extra space, the lookup returns an error, and every formula that depends on it fails too.
• Totals. When an import stores amounts as text, they look like numbers but are not. SUM skips text, so a column of amounts can total zero while every cell appears to hold a value.

Capitals cause a related problem. Most matching in a spreadsheet ignores case, but reports do not. A report with "BRIGHT ads" on one line and "Bright Ads" on another looks careless. Cleaning the text first gives you one consistent style before you match, look up or report.

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) includes preparing accounting data for analysis, and under it a skill for transforming data, including using text functions to extract and 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 "make every vendor name use one space between words and a capital at the start of each word" and have to decide which functions do that. The exercises in this lesson are written the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

How the cleaning functions work

TRIM: remove extra spaces

=TRIM(A5) removes spaces at the start and end of the text and shrinks every run of spaces between words to a single space. It never removes the one space a name needs between its words. Use it on anything typed by hand or pasted from another system.

PROPER, UPPER and LOWER: fix capitals

Three functions set the case of every letter:

• =PROPER(A5) capitalizes the first letter of each word and makes the rest lowercase: "pInE Street DENTAL" becomes "Pine Street Dental". This is the usual choice for names.
• =UPPER(A5) makes every letter a capital. It suits codes such as account or state abbreviations.
• =LOWER(A5) makes every letter lowercase, which is handy for email addresses.

Check PROPER's output on names with unusual capitals. It turns "ABC Supply" into "Abc Supply", because it cannot know the letters are an abbreviation.

Nesting: two fixes in one formula

Most messy names need both fixes at once. Put one function inside the other: =PROPER(TRIM(A5)). The spreadsheet works from the inside out. TRIM removes the spaces first, then PROPER sets the capitals on the trimmed result.

SUBSTITUTE: replace or remove characters

=SUBSTITUTE(A5,"$","") finds every dollar sign in A5 and replaces it with nothing, which removes it. SUBSTITUTE handles one character or phrase at a time, so to remove both dollar signs and commas you nest it: =SUBSTITUTE(SUBSTITUTE(A5,"$",""),",","").

VALUE: turn number-like text into a number

=VALUE("9191.37") returns the number 9191.37, which you can add, format and compare. VALUE needs clean input: strip anything that is not part of the number first, then convert what is left.

CLEAN: remove characters you cannot see

Text copied from web pages, PDFs or older systems sometimes carries non-printing characters, such as line breaks or control codes. They take up no visible space but still stop a match. =CLEAN(A5) removes them. A common belt-and-braces pattern is =TRIM(CLEAN(A5)).

How combining text works

The & operator

The ampersand joins pieces of text end to end. Fixed words go in double quotes, and cell references go outside them: =B5&" invoice "&C5. The spreadsheet adds nothing between the pieces. Every space you want in the result must be inside the quotes.

CONCAT and TEXTJOIN

=CONCAT(B5," invoice ",C5) does the same job as the ampersand, written as a function. It also accepts a range, such as =CONCAT(A5:C5), which joins the cells with nothing between them.

=TEXTJOIN(" ",TRUE,A5:C5) joins a range with a delimiter of your choice (here a space) between each item. The second argument, TRUE, tells it to skip empty cells, so you do not get doubled delimiters where a value is missing. TEXTJOIN is the cleanest way to build an address line or a list of names separated by commas.

TEXT: keep currency formatting inside a sentence

A number's format belongs to the cell, not to the number. When you join an amount to text, the format is dropped and you get the raw value: 3791.8 instead of $3,791.80. The TEXT function fixes this by turning the number into formatted text first, using a format code:

=TEXT(D11,"$#,##0.00")

In the code, $ prints a dollar sign, #,##0 adds thousands separators and always shows at least one digit, and .00 forces two decimals.

Worked example 1: tidy a vendor list

Juniper Landscaping's vendor list was typed by several people. Before matching it to invoices, the names need one style. Here are a few of the twelve rows (spaces shown as typed), with the result of =PROPER(TRIM(A5)) filled down column B:

Notice that the rows need different fixes. Row 7 has only wrong capitals. Row 5 has only extra spaces. Rows 8, 11 and 16 have both. One nested formula handles every case, which is why you fill the same formula down the whole column rather than fixing rows by eye.

Worked example 2: build an invoice memo

Copperleaf Studio needs a one-line memo for each invoice: the customer, the word "invoice", the invoice number, the word "for" and the amount as currency. The customer is in column B, the invoice number in C and the amount in D. In E5:

=B5&" invoice "&C5&" for "&TEXT(D5,"$#,##0.00")

Row 11 shows why TEXT matters. The amount 3,791.80 is stored as 3791.8. Joined without TEXT, the memo would end "for 3791.8", with no dollar sign, no separator and only one decimal. With TEXT, it reads "for $3,791.80".

Worked example 3: turn imported text into numbers

Copperleaf Studio imported a bank file, and the twelve amounts arrived in column A as text, some with dollar signs, commas and stray spaces. (In the sheet, a leading apostrophe marks each cell as text; it is not displayed.) In B5, one formula strips the extras and converts:

=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A5),"$",""),",",""))

Read it from the inside out: TRIM removes the spaces, the inner SUBSTITUTE removes the dollar sign, the outer one removes the comma, and VALUE turns what is left into a number.

The total in B17 is =SUM(B5:B16), which gives $82,667.04 for all twelve rows. Run =SUM(A5:A16) on the imported column instead and you get 0, because SUM ignores text.

Common mistakes

Fixing the capitals but not the double spaces

=PROPER(A8) on its own turns "  SWIFT   CoUrieRs " into "  Swift   Couriers ". It looks better, but the extra spaces are still there, so it still will not match "Swift Couriers". Wrap the text in TRIM as well.

Fixing the spaces but not the capitals

=TRIM(A11) gives "GrEEN valley FARMS": the spacing is right, the style is not. Add PROPER so every name reads the same way in reports.

Missing spaces in joined text

Writing "invoice" instead of " invoice " produces "Fairview Dentalinvoice6123for$13,779.66". The ampersand never adds spaces for you. Put a space inside the quotes on each side of every fixed word.

Amounts losing their currency format when joined

Joining D11 directly gives "Elm Street Cafe invoice 6129 for 3791.8". The cell's format does not travel into the text. Wrap the amount in TEXT(D11,"$#,##0.00") before you join it.

Leaving converted amounts as text

A formula like =TRIM(A5) or =SUBSTITUTE(A5,"$","") returns text, even when the result looks like a number. Text results are often left-aligned, which is a useful clue. Finish the conversion with VALUE so the amount can be totaled.

Totaling the original text column

If the total formula points at column A, the answer is 0, even though every cell shows an amount. Always total the converted column. A zero total on a column that is clearly full of amounts is the classic sign that the cells hold text.