Delete Trailing Spaces In Excel Vba
Remove Trailing Spaces In Excel Numbers
Trim Trailing Spaces In Excel Vba
Remove Trailing Spaces In Excel Sheet

Using the Trim function, you have learned how to remove trailing spaces in Excel and leading spaces too. There can be non-breaking spaces in a text as well. To remove them, simply use Substitute Function and replace the non-breaking space with a normal space. To remove only the leading and trailing spaces from text in a cell we need to use the VBA TRIM function. The VBA TRIM function only removes the extra space at the start and end of the text in a cell. Therefore, any extra space between words in a cell will remain. The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces. The TRIM function below removes 2 leading spaces, 3 extra spaces and 2 trailing spaces. Note: the TRIM function does not remove single spaces between words. Select a cell next to the cell you want to remove extra spaces from string, type this formula =TRIM (B2) B2 is the cell you want to remove spaces from, See screenshot: 2. RE: Remove trailing spaces from multiple columns in Excel. You can use the 'replace' command. Select the fields that you want to perform this operation on. Edit (on the toolbar) Select 'Replace'. Put a space in the find field. Put nothing in the replace field.

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

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

Delete Trailing Spaces In Excel Vba

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

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.

Remove Trailing Spaces In Excel Numbers

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.

Need for speed underground 2 pc. You may not be a fan of his puerile and malicious brand of humour, but it's a perfect fit for the game's anarchic ethos, and adds a strong sense of character where the clean-cut Hawk could not. Ridiculous trick combos are still the order of the day, now bolstered with sticker slaps, post-crash tantrums and slow-mo 'focus' mode. For a start, the inclusion of Bam Margera (of Jackass notoriety) as guest star is a masterstroke.

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

Trim Trailing Spaces In Excel Vba

Remove Trailing Spaces In Excel Sheet

Hope this helped.