Clean Excel Data With TRIM and SUBSTITUTE

Clean Excel Data With TRIM and SUBSTITUTE

You have two Excel lists, and you’re trying to find the items that are in both lists. You know there are matching items, but if your VLOOKUP formulas can't find any matches, you might need to clean Excel data with TRIM and SUBSTITUTE.

VLOOKUP Cannot Find Matches

Here's an example of this problem. In this screenshot, the tiptech.html page is in both lists, but the VLOOKUP formula in cell C2 can’t find it. It returns and #N/A error.

TRIM01

Spot the Differences

Working with Excel data can be like one of those “Spot the Difference” puzzles. What’s different between list A and list B?

If you’re lucky, the differences are obvious, like the forward slash in column B, and no leading slash in column E. Other times, it’s tougher to find the differences.

A common problem, when trying to match data, is items with leading or trailing spaces. You can’t see them on the screen, but after you’ve encountered them a few times, you learn to check for them.

Check the Length

The LEN function is a great help if you suspect there are hidden space characters in a cell.

TRIM02

If you use the LEN function to compare the length of the text in cell B2 and E4, you’d see that there are 2 additional characters in cell B2. One character is the forward slash, and the other character is a trailing space.

Remove Leading and Trailing Spaces

If you want to use a VLOOKUP or MATCH to find column B items, in column E, you’ll have to get rid of any extra characters.

First, you can deal with the spaces, by using the TRIM function.

To return the text from cell B2, without any leading or trailing characters, you’d use this formula:

=TRIM(B2)

If you use a formula in cell B12 to check the length of the trimmed text, it’s now 13 characters, instead of 14. The trailing space has been removed.

TRIM03

Remove a Specific Character

Next, you can use the SUBSTITUTE function to remove the forward slash from the text in cell B2.

=SUBSTITUTE(B2,”/”,””)

  • The first argument, B2, is the cell that contains the text value.
  • The second argument, “/”, is the old text, that you want to replace.
  • The third argument, “”, is the new text, that replaces the old text. If you want to remove the old text, without inserting new text, use “” as an empty string, as we did here.

TRIM04

Combine the Functions

The TRIM and SUBSTITUTE functions work well separately, and you can combine them, to remove the extra spaces and the forward slash. The order doesn’t matter, so you can use either:

=TRIM(SUBSTITUTE(B2,"/",""))

or:

=SUBSTITUTE(TRIM(B2),"/","")

TRIM05

Add to the VLOOKUP

Now that you know the TRIM and SUBSTITUTE functions will clean up the text in column B, you can add those functions to the VLOOKUP formula.

Instead of using B2 in the VLOOKUP:

=VLOOKUP(B2,$E$2:$F$8,2,FALSE)

use the TRIM and SUBSTITUTE functions:

=VLOOKUP(TRIM(SUBSTITUTE(B2,"/","")),$E$2:$F$8,2,FALSE)

A Match is Found

After you change the VLOOKUP formula, to include TRIM and SUBSTITUTE, it works correctly.

A match for the cleaned up text is found in column E, and in the Update column, cell C2 is filled in with the correct date.

TRIM06

Troubleshoot a VLOOKUP Formula

If TRIM and SUBSTITUTE don’t solve your VLOOKUP problems, there are a few more suggestions on the Contextures website: Troubleshoot the VLOOKUP formula.

On that page, you'll also find examples of using the IF function or IFERROR function to deal with VLOOKUP function errors.

_____________

0 thoughts on “Clean Excel Data With TRIM and SUBSTITUTE”

  1. Pingback: VLOOKUP Function Examples And Videos | loan payment calculator
  2. Hi Debra:
    I invite you to take a look to a piece of work I developed to improve and simplify MS-Excel strenghts. It's name is QuickRows. I would love to receive a critic from an expert as You are.
    Thanks in advance
    José Corona
    Spain

  3. Thanks for the above explanation. actually it doesn't work with me as I have column of series numbers and at the end of the number I have two trial spaces.
    I tried to TRIM feature it removes only one space =TRIM(B2), I tried as well the TRIM plus Substitute it does not work as well and it removes only one space =TRIM(Substitute(B2," ","")), and I tried Subtitle it does not work as well =Substitute(B2," ",""), and finally tried Replace option.
    Cell format is General
    please help me to solve this issue

    1. You can simply use text-to-columns. If there are no other spaces in your series of numbers, create a column to the right of the the data, highlight the data that has the extra spaces, click on data, text-to-columns, delimited, next, check "Space", uncheck any other delimiter and hit finish. This should move the spaces to the column you created. Simply delete that column and your data should be free of spaces.

  4. Hi,
    In the below text there is an hidden character. I have found it and removed manually.
    Anyone please help me with some formula, how to detect or resolve this kind of things.
    (Board -HONET made ​​before P3-H612RATF- maintain 32 relay adapter plate -1 * 2)
    Thanks

  5. Instead of substitute, we can use trim and clean function. in clean function we need not mention the characters to be replaced.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.