Do you need to remove extra spaces in an Excel cell? Extra spaces can appear in your worksheet for many different reasons, such as someone accidentally typing them in or extra characters travelling with data exported from another software product. These spaces can cause data processing and calculation issues, making removing them essential.
In this blog, we’ll cover two options for removing those extra spaces and how extra spaces can impact Excel calculations, such as VLOOKUP and XLOOKUP. By understanding these methods, you can clean up your data efficiently and ensure your formulas work correctly.
Maintaining clean data in Excel improves your overall data analysis experience. Clean data not only makes your worksheets look more professional but also helps in avoiding errors and inconsistencies in your calculations. So, let’s dive in and learn how to keep your Excel sheets free from unwanted spaces!

Excel Practice Files
Remove extra spaces using Find and Replace
One common method for removing extra spaces is using Find and Replace. However, this method can be pretty brutal. For example, using Find and Replace to remove spaces might smash together words within the cell. Names like ‘Sarah Brooke’ and ‘Robert Brown’ will lose the space between their first and last names, becoming ‘SarahBrook’ and ‘RobertBrown’. Not ideal.
However, finding and replacing is a good option if you need to remove empty spaces before and after a single word.
Step-by-Step Guide to Using Find and Replace:
- Select the Cells: First, select the cells that contain the extra spaces you want to remove.
- Open Find and Replace: Press Ctrl + H to open the Find and Replace dialogue box.
- Enter Space in the ‘Find What’ box: Click into the “Find what” text box and press your Spacebar once to add a space.
- Leave ‘Replace With’ Empty: Leave the “Replace with” text box empty.
- Replace All: Click “Replace All”. Excel will notify you how many replacements have been made.
- Check Results: Click OK and check the results.
Note: You may find that cells with multiple words, like names, have lost the space between them, making them one word. Clicking Undo will resolve this. Once you have returned your data to the original form, follow the steps below using the Trim function to remove spaces before, between and after the text.
Remove Spaces With The Trim Function In Excel
The TRIM function can be used instead of deleting extra spaces one by one or using Find and Replace without getting the desired outcome.
TRIM saves time and avoids the issue of losing the spacing between text. The TRIM function looks at the information on your worksheet, checks for extra spaces before, in between, and after the data, and then removes these.
If spaces are found between the data, TRIM removes all but one space to ensure the words don’t run together.
Step-by-Step Guide to Using the TRIM Function:
- Select a Cell for the Cleaned Data: Click into a cell where you want the cleaned data to appear (e.g., cell B4).
- Enter the TRIM Formula: Type =TRIM(.
- Select the Cell to Clean: Click on the cell that you want the spaces removed from (e.g., A4).
- Complete the Formula: Press your close bracket to complete the formula =TRIM(A4).
- Press ENTER: You should now have a cleaned version in the selected cell (e.g., B4).
- Replace Original Data: If required, select the cleaned data and press Ctrl + C to copy it. Right-click the original cell and select “Paste Values” to replace the original data with the cleaned data.
Tip: to remove extra spaces in a column, use the TRIM function at the top cell and copy it down the column.
Extra Spaces Impact Calculations
Extra spaces can also impact calculations. For instance, if there’s an extra space at the end of a code, a VLOOKUP or XLOOKUP formula will return an #N/A error (a ‘not available’ error).
The TRIM function can help clean the data and ensure your calculations work correctly.
In the example below, cell I4 holds a VLOOKUP formula. The VLOOKUP is referencing the code AB1234 entered in cell H4., Excel has returned the #N/A (not available) error into cell I4. This is because the code typed into cell H4 does not match the code in E7, as it does not have a space at the end of it. If the codes don’t exactly match, VLOOKUP will be unable to find it in the Code column and, therefore, won’t be able to return the corresponding price.
Step-by-Step Guide to Using the TRIM Function with VLOOKUP:
- Use the TRIM Function: Insert a temporary cleaning column (e.g., column D) and use the TRIM function to clean the data. If you are cleaning an entire column select the top cell.
- Enter the TRIM Formula: Type =TRIM(E4) in the cleaning column next to the cell with the extra space.
3. Copy the Formula: Copy the formula down the cleaning column to clean all relevant cells.
4. Replace Original Data: Select the cleaned data and press Ctrl + C to copy it. Right-click the original column and select “Paste Values” to replace the original data with the cleaned data.
5. Check VLOOKUP: Ensure the VLOOKUP formula works correctly without returning errors. Once the data is correct, you can delete the cleaning column.
Note: If you still have remaining spaces, check out my video on ‘How to remove spaces TRIM misses’.
Conclusion
In summary, extra spaces in your data can cause significant issues with functions like VLOOKUP or XLOOKUP, leading to errors that can be frustrating to troubleshoot. Using the TRIM function, as outlined above, you can clean your data and ensure that your formulas return accurate results. This simple yet effective step can save you time and headaches, allowing you to focus on more critical aspects of your work. Remember, clean data is key to successful calculations in Excel.
Watch The Excel Video Tutorial
Excel Practice File Download
Certified Microsoft Office Specialist
Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.
Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.
And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!
Happy Excel-ling!!