Welcome Excellers to another #FormulaFriday. Today I am going to show you a great way to check if you have extra spaces in your Excel data that you need to clean up. Sometimes the data in cells looks identical like my barcodes in my sales extract. But, when you try to analyse the data you do not get the expected results. I will show you an example. I have a set of data below that I want to use in a VLOOKUP formula. A simple exercise to use VLOOKUP formula to lookup the barcodes of products from a sales extract and return the price description from my Product Master File.

So let’s create a VLOOKUP formula to extract the price from the pricing table and insert it into our sales extract that has been forwarded to us. All looks good on the first line it has picked up the correct pricing, but as the formula is dragged down there appear to be some issues with the results.

The barcodes in the sales extract look identical. But there may be some extract invisible characters or spaces. So, here is the solution using a great little formula to identify if we have extra characters or spaces.

### The Formulas Used In The Solution.

**LEN, TRIM and the IF Function**to check the string length of the barcode, and compare this to the length of the same barcode once any extra spaces have been removed. The formula looks like this.

### The IF Function

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.

The Syntax of the IF Function is

**=IF (logical_test, [value_if_true], [value_if_false]) **

Where

**logical_test** – A value or logical expression that can be evaluated as TRUE or FALSE.

**value_if_true** – [optional] The value to return when logical_test evaluates to TRUE.

**value_if_false** – [optional] The value to return when logical_test evaluates to FALSE.

If you want some examples of how to use the IF Function then you check out my blog posts below.

**Formula Friday – Easily Add Symbols To Your Excel To Do List Using The IF Function**

**Formula Friday – Conditionally Format An Excel Chart With IF Function**

### The LEN Function

The Excel LEN function returns the length of a given text string as the number of characters. LEN also counts characters in numbers, but number formatting is not included.

The Syntax of the LEN Function is

**=LEN (text)**

Where

**text** – The text for which to calculate length.

If you want some examples of how to use the LEN Function then you check out my blog posts below.

**Formula Friday – Using Excel to help with your SEO preparation using the LEN formula**

**Formula Friday- How To Use LEN in Excel To Highlight Issues With My Data Import**

### The TRIM Function

TRIM removes all spaces from text except for single spaces between your words. It removes all spaces from text except for single spaces between words.

The Syntax of the TRIM Function is

**=TRIM(text)**

Where

**text- **Required. The text from which you want spaces removed.

If you want to watch my YouTube Video on how to use the TRIM function, check out the link below.

#### If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.

**How To Excel At Excel – Formula Friday Blog Posts.**

### Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST. Why not check it out?.