These trailing spaces cause the VLOOKUP to not classify the cells as a match. Although you can tell that ' BG33-8 ' is the same as ' BG33-9', Excel cannot. Column A has trailing spaces. You can use the TRIM function to remove leading and trailing spaces from a value. If you need to strip leading and trailing spaces from text in one or more cells, you can use the TRIM function. In the example show, the formula in cell C3 is: = TRIM(B3) Once you've removed extra spaces, you can copy the cells with formulas and paste special elsewhere as 'values' to get the final text. Click in the first cell of the Trim column. On the Formulas tab, click the Text dropdown menu in the Functions group and select TRIM. After the dialog box appears, click on the cell that contains the text you would like to remove spaces from to complete the function.

• Delete Trailing Spaces In Excel Cell
• Trim Trailing Spaces In Excel
• Remove Trailing Spaces In Excel Numbers

Delete Trailing Spaces In Excel Cell

There are situation when we try to look up some value in excel but we do not get the desired result because of some unseen data that is available in the cell.

Trailing spaces are one of them,they are not visible generally and may lead to undesirable results if you are doing lookup or comparing strings in excel.Finding trailing spaces is simple and we will see how to find and remove trailing spaces by using excel formula

We will see a simple formula by which we can easily find out if the trailing spaces are there in string or not. We will combine RIGHT and IF formula in excel to determine the trailing spaces.

As we could see in the example above, there are trailing spaces present in the data which could not be found out just by looking the data so enter the formula below in cell B2 and drag down till the data is available in column A.

=IF(RIGHT(A2,1)=” “,”YES”,”NO”)

Hope this helped.

Trim Trailing Spaces In Excel

This post discusses one way to enable our lookup functions, such as VLOOKUP, to work even when the lookup values contain extra spaces. We’ll use the TRIM function to dynamically remove padding from the lookup values.

Overview

Remove Trailing Spaces In Excel Numbers

We’ll use a specific example to demonstrate this function. Let’s say we’ve exported a partial income statement from our accounting system. For some technical reason, it appears that all report labels are padded with trailing spaces so that the total label length is 40 characters. As we inspect each report label, we observe that there are a different number of trailing spaces for each label, as shown below:

These extra trailing spaces create problems when we try to perform lookups. Here’s why.

The accounts in the budget table do not have trailing spaces. The budget table (tbl_bud) is shown below:

When we write a formula using a lookup function such as VLOOKUP, the lookup value is not found in the lookup range. This is because the lookup value contains the trailing spaces, and the accounts stored in the lookup range do not. To Excel’s lookup functions, these are two different values.

The formulas thus produce errors, as demonstrated below:

In order to ensure that the lookup value can be found in the lookup range, we need to remove the trailing spaces. Although we could do this manually, we like to delegate manual tasks to Excel, especially in our recurring use workbooks. So, we enlist the help of the TRIM function. The TRIM function removes any extra spaces, including leading spaces, trailing spaces, and double spaces. The TRIM function has a single argument, the text to trim.

By wrapping a TRIM function around the lookup value, we remove the extra spaces and enable the VLOOKUP function to make its match.

We could use the following formula to perform the lookup:

Where:

• TRIM(B3) is the lookup value, stripped of extra spaces
• tbl_bud is the lookup range, the budget table
• 2 the column that has the value to return, the amount column
• 0 means exact match

When we use this formula, the budget values are properly returned to our report, as shown below:

The TRIM function removes the extra spaces and enables the VLOOKUP function to make its match.

If you have other practical uses for the TRIM function we’d love to hear more…please post a comment below.

Additional Resources

• Download sample file: Trim
• For other blog posts that reference VLOOKUP
• To handle trailing spaces in the lookup range (instead of the lookup value), check out the wildcard post