Welcome Excellers to another #FormulaFriday blog post in my 2021 Excel series. Today I am going to show you some Excel formulas. These are 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 Excel Formula Used In The Solution.
The formula uses the LEN, TRIM and the IF Function to check the string length of the bar code. It is then compared to the length of the same bar code once any extra spaces have been removed. The formula looks like this.
Excel takes the length of the string in B2, subtracts it from the same string that has been trimmed. If the result is over zero (or there were extra spaces) then the TRUE argument returns “EXTRA SPACES”. If the result is not over zero and false the cell value is left blank.
All I need to do is use the TRIM function. This will remove the extra spaces in your Excel data. Finally, the VLOOKUP will return the correct pricing.
If you need more information on the individual formulas used in this Excel solution then check out my walk through below.
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 argument. This is the text from which you want spaces removed.
If you want to watch my YouTube Video on how to use the TRIM function click the link below. This will help you to removes extra spaces in your Excel data.
So, 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.
More Excel Tips.
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?.
