Mohammad Shah Miran
Excel provides a wide-ranging utility to its customers. Sometimes your work demands to truncate spaces in our data. In such situations, what will be the method by which you shall work on? Well, so today we will learn how to truncate spaces in Excel.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
5 Methods to Truncate Spaces in Excel
Let’s assume we have a dataset, namely “Employee List Dunder Mifflin Company”. You can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Using TRIM Function
The first function that we are going to use to accomplish our task is TRIM function. It eliminates all white space from text except the single gap between words.
📌 Steps:
- To begin with this method, enter the following formula in cell D5.
=TRIM(C5)- Press the Enter button and see the output below.
Interestingly, we can use the TRIM function from the Function Library in the Formula tab. Thus, we can insert functions, specify names, establish name ranges, and review formulas. However, you can also use this to call your TRIM function from there.
📌 Steps:
- Firstly, go to Formula tab from Menu Bar. Then proceed to Text > TRIM.
- Subsequently, a dialog box will appear name Function Arguments.
- Now, select the cell in which your data contains, cell C5 for instance.
- Press on OK afterward.
- Consequently, you’ll get the same output as you did when writing the TRIM function directly.
- To get the other value, drag the Fill Handle tool from D5 to D10.
Read More: How to Truncate Text from Left in Excel (5 Suitable Ways)
2. Applying SUBSTITUTE Function
Another way to truncate spaces in excel is to incorporate the SUBSTITUTE function in your formula editor. Follow the instructions, which we are going to describe below.
📌 Steps:
- Write the following formula in cell D5.
=SUBSTITUTE(C5," ","")In this formula, C5 is the cell in which our intended data resides. Multiple spaces with inverted commas (“ ”) are the look-up value that the SUBSTITUTE function will search for in the C5 cell. Lastly, void space (“”) will take the place of those multiple spaces, as we mentioned previously.
- Now, hit the Enter button and see the output as like below.
- As you have noticed, there is no single space in our output data as our argument instructed so.
- Drag the Fill Handle tool to get the other values.
Read More: How to Use Truncate in Excel VBA (3 Effective Ways)
3. Incorporating CLEAN and TRIM Functions Simultaneously
Now, we can also truncate space data by using the CLEAN and TRIM functions simultaneously. All you need to know is how to manipulate those functions to accomplish your task.
📌 Steps:
- To begin with this method, enter the following formula in cell D5.
=TRIM(CLEAN(C5))- Press the Enter button afterwards.
- To get the other value, drag the Fill Handle tool from D5 to D10.
Read More: How to Truncate Text in Excel (6 Suitable Methods)
4. Employing TRIM, CLEAN and SUBSTITUTE Functions
Similarly, you can incorporate TRIM, CLEAN, and SUBSTITUTE functions to do your job. Just follow the process that we are going to articulate below.
📌 Steps:
- Write the following formula in cell D5.
=TRIM(CLEAN(SUBSTITUTE(C5,CHAR(160)," ")))- Next, press Enter.
- Lower the cursor to the C10 cell to use the AutoFill tool.
Read More: How to Truncate Text After Character in Excel (4 Suitable Ways)
5. Using Find & Replace Feature
Because of its simple process, the Find & Replace feature is another useful tool for doing your job. Stick to the process which we are going to describe below.
📌 Steps:
- Firstly, select your intended data range as shown below.
- Now press Ctrl + H shortcut key to bring the Find and Replace dialog box.
- Next, enter two spaces in Find what box as we want to replace two space with one space.
- Then enter one space in Replace with box as our case suggests.
- Click on Replace All.
- Immediately, a dialog box will pop up. Click OK.
- Now see the output, as shown below.
Read More: How to Truncate Text from Right in Excel (5 Easy Methods)
Things to Remember
- It is to be mentioned that while using those functions and features, you have to make sure that align with your work type as those methods give you a homogenous output. Thus, you may eliminate some important space as those methods treat all the space uniformly to give a specific output which is one space most of the time.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.
Conclusion
This is how you can truncate spaces in Excel. We hope you have learned something new today through this tutorial. This is an easy task, and we think we should learn this method to make our life a little bit easier. Further, if you have any queries, feel free to comment below and we will get back to you soon.
