## Trim Function In Excel

TRIM in Excelis a text function that removes unwanted space, like extra space in-between the words or at the beginning and end of the sentences or words. This formula does not remove if a single space is present between two words.

TRIM function in Excelis an inbuilt text function, so we can insert the formula from the“Function Library”or enter it directly in the worksheet.

For example, suppose we have a name in cell **A1** containing some whitespace before and after the text, and more than one space between the words, as “ **John Wright** ”.

The output is “**John Wright**” after applying the **TRIM formula** as **=TRIM(A1)**, as shown above in cell **B1**.

### Key Takeaways

- The
**TRIM in Excel**removes extra spaces from the text, leaving no extra space between words and at the beginning and end of the string. - It can only remove the ASCII space character (32) from text/string.
- It cannot remove the Unicode text that often contains a non-breaking space character (160) that appears on web pages as the HTML entity.
- This function is useful for refining the text from other applications or environments.
- We can use
**TRIM**with other Excel functions such as**LEN(), SUBSTITUTE(),**etc.

### Syntax Of Excel TRIM Formula

The syntax of the **TRIM formula** is,

The TRIM formula in Excel has only one critical parameter: **text**.

**text:**it is a text from which you want to remove extra spaces.

### Trim Function in Excel Explained in Video

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

### How To Use TRIM Function In Excel?

We can use the **TRIM Excel function **in 2 ways, namely:

**Access from the Excel ribbon.****Enter in the worksheet manually.**

#### Method #1 – Access from the Excel ribbon

Choose an empty cell for the result à select the “**Formulas**” tab à go to the “**Function Library**” group à click the **“Text” **option drop-down à select the “**TRIM**” function, as shown below.

The **“Function Arguments” **window appears. Enter the arguments in the **“Text”** field, and click “**OK**”, as shown below.

#### Method #2 – Enter in the worksheet manually

- Select an empty cell for the output.
- Type =
**TRIM**( in the selected cell. [Alternatively, type**=T**or**=TR**and double-click the**TRIM function** - Enter the argument as cell value or cell reference and close the brackets.
- Press the “
**Enter**” key.

### Basic Example

Let us understand the working of the **TRIM function in Excel** using an example.

[**Note:** **Excel TRIM function** can be used as a worksheet and VBA function].

In this example, the **TRIM function in Excel** removes the extra spaces from the given texts/ words at the beginning and end.

Select cell **C5**, enter the formula **=TRIM(B5),** press “**Enter**”, and drag the formula from cell **C5 to C14** using the fill handle.

The output is shown above. So, for example, applying **TRIM** on “ **Tanuj** ”, the output will be “**Tanuj**” without the spaces.

### Download Template

This article must help understand the **TRIM in Excel **with its formulas and examples. You can download the template here to use it instantly.

### Examples

We will consider some scenarios using the **TRIM Excel function examples**.

#### Example #1

In this example, the trim removes the extra spaces from the middle of the given text string, as shown in the below table.

Select cell **C19**, enter the formula **=TRIM(B19),** press “**Enter**”, and drag the formula from cell **C19 to C26** using the fill handle.

The output is shown above. For example**, **in cell **C19**,** “Note that the Trim function is similar to**” becomes “**Note that the Trim function is similar**”.

#### Example #2

In the following example, to calculate the number of words in cell **B1** string – “**Note that the Trim function is similar**”.

- Here, we can use the formula
**=LEN(B31)-LEN(SUBSTITUTE(B31,”,”))+1.**However, it will count the number of spaces and add one to it. But if there are extra spaces between the string, it will not work, so we use trim to achieve it. - Therefore, select cell
**C32**, enter the formula**=LEN(TRIM(B32))-LEN(SUBSTITUTE(B32,” “,”))+1**function, press “Enter”, and drag the formula from cell**C32 to C39**using the fill handle.

The output is shown above. The formula will remove extra spaces, then count the number of spaces and add one to give the total number of words in the string.

#### Example #3

We can use Excel’s TRIM and substitute function in excelSubstitute Function In ExcelSubstitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.read more to join the multiple columns separated by a comma.

Select cell **J5**, enter the TRIM formula **=SUBSTITUTE(TRIM(F5&” “&G5&” “&H5&” “&I5),” “,”, “), **press “**Enter**”, and drag the formula from cell **J5 **to** J12** using the fill handle.

The output is shown above.

### Important Things To Note

- We can use the TRIM formula only for text strings in words, names, sentences, etc.
- If the cell value or cell reference is numeric or alpha-numeric, then the output will be the exact same value as the selected value.

### Frequently Asked Questions

**1) Where is the TRIM function in Excel?**

The Trim function is found as follows:

Choose an empty cell for the result **->** select the “**Formulas**” tab **->** go to the “**Function Librar**y” group **->** click the “**Text**” option drop-down **->** select the “**TRIM**” function, as shown below.

**2) How to insert the Trim function in worksheet?**

We can insert the TRIM function manually, as shown below,

1. Select an empty cell for the output.

2. Type =TRIM( in the selected cell. [Alternatively, type =T or =TR and double-click the TRIM function from the list of suggestions shown by Excel.

3. Enter the argument as cell value or cell reference and close the brackets.

4. Press the “Enter” key.

**3) Why is TRIM function not removing spaces in Excel?**

The Excel TRIM function may not work due to the following reasons:

1. The cell value doesn’t have unwanted space.

2. The cell value is numeric or alpha-numeric which returns the same value when entered in the formula.

### Recommended Articles

This article has been a guide to TRIM in Excel. Here we remove unwanted space in-between, or at start & end of the strings, example and downloadable excel templates. You may also look at these useful functions in Excel: –

- VBA TRIM in ExcelVBA TRIM In ExcelVBA TRIM is classified as a string and text function. This is a worksheet function in VBA that, like the worksheet reference, is used to trim or remove unwanted spaces from a string. It takes a single argument, which is an input string, and returns an output string.read more
- POWER Function
- NOT Excel Function
- Concatenate in Excel
- Percentage Change Formula in ExcelPercentage Change Formula In ExcelA percentage change formula in excel evaluates the rate of change from one value to another. It is computed as the percentage value of dividing the difference between current and previous values by the previous value.read more