Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Truncate Spaces in Excel (5 Easy Methods)

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.

truncate spaces in excel

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.

Using TRIM Function

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.

Using TRIM Function

  • 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.

Using TRIM Function

  • Consequently, you’ll get the same output as you did when writing the TRIM function directly.

Using TRIM Function

  • To get the other value, drag the Fill Handle tool from D5 to D10.

Using TRIM Function truncate spaces in excel

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.

Applying SUBSTITUTE Function truncate spaces in excel

  • 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.

Applying SUBSTITUTE Function

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.

Incorporating CLEAN and TRIM Functions Simultaneously

  • To get the other value, drag the Fill Handle tool from D5 to D10.

Incorporating CLEAN and TRIM Functions Simultaneously truncate spaces in excel

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.

Employing TRIM, CLEAN and SUBSTITUTE Functions truncate spaces in excel

  • Lower the cursor to the C10 cell to use the AutoFill tool.

Employing TRIM, CLEAN and SUBSTITUTE Functions

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.

Using Find & Replace Feature

  • 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.

5. Using Find & Replace Feature

  • Immediately, a dialog box will pop up. Click OK.

Using Find & Replace Feature

  • Now see the output, as shown below.

Using Find & Replace Feature

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.

Do it yourself truncate spaces in excel


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.


Related Articles

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. Miran is dedicated to staying current with the latest trends and technologies and is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo