The sample dataset contains 5 Product Names and Product Codes,where the product codes contain extra spaces before the numbers.
Method 1 – Using the TRIM Function to Remove Space Before Numbers in Excel
- Select cell D5 and enter the following formula.
=TRIM(C5)
- Press Enter and utilize the AutoFill tool to fill the entire column.
Read More: How to Remove Tab Space from Excel
Method 2 – Inserting Excel SUBSTITUTE Function to Delete Space Before Numbers
- Select cell D5 and insert the following formula.
=SUBSTITUTE(C5," ","")
- Press Enter and utilize the AutoFill tool to fill the entire column.
Method 3 – Find and Replace Option to Remove Space Before Numbers
- Select the data where you want to remove space before numbers.
NOTE: Here, I have copied the Product Codes into the Correct Code column for better representation.
- Press Ctrl + H on your keyboard to open the Find and Replace option.
- In the Find What option, enter a Space.
- In the Replace With box, make sure there is no space or extraneous characters.
- Click on Replace All.
- The codes are returned as below.
Read More: How to Remove Space after Number in Excel
Things to Remember
- You have to create an extra column in order to get results for methods 1 and 2.
- You can not only remove the spaces before numbers but also remove spaces between and after numbers using these methods.
- You need to copy the data into another column if you want to see the changes side-by-side for method 3.
- You can apply the Find and Replace option to remove spaces for multiple rows and columns at a time. To do so, do not select any particular column and follow the rest of the steps.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Related Articles
- How to Remove Extra Spaces in Excel
- Remove Space Before Text in Excel
- How to Remove the Trailing Spaces in Excel
- How to Remove White Space in Excel
- How to Find and Replace Space in Excel
- How to Remove Space between Rows in Excel
- Remove Space in Excel after Text
- How to Remove Blank Spaces in Excel
- How to Remove Leading Spaces in Excel
<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
