How to Subtract a Text String in Excel

Subtracting text in Excel is a tad different from subtracting numbers, as the minus sign (-) won't be able to do the trick. To subtract text in Excel, you'll need to add some other functions to your formula.

Subtracting text in Excel doesn't have a dedicated function, and you have to come up with a compound formula for it. Depending on which functions you use to subtract text in a cell from another, your formula could end up being either case-sensitive or case-insensitive.

In this article, we'll cover two methods for subtracting text in Excel. Of course, these aren't the only methods for subtracting text in Excel, and you could forge a new formula for subtracting text in Excel to subtract text through different means.

Case Sensitive Subtraction Formula

A good method for subtracting text in Excel is to SUBSTITUTE the target string with blank. The SUBSTITUTE function will look through the first cell and search for the target string, and then replace the target string with blank, effectively deleting it from the text.

If you're dealing with sentences, you can couple this with the TRIM function to get rid of trailing and/or leading spaces.

        =TRIM(SUBSTITUTE(target cell, text to be removed,""))

Using the SUBISTITUTE function to subtract text in Excel.

Let's put this formula to use. In this example, we've got a false statement in cell A1 which can be corrected by subtracting cell B1 from it. We're going to display the result in cell D1.

Select the cell you want to display the formula result. As mentioned above, we're going to use cell D1 in this example.
Enter the formula below:
```
        =TRIM(SUBSTITUTE(A1,B1,""))
    
```
The SUBSTITUTE function will study cell A1, and check if the text in cell B1 is included in it. Then, it takes that text in cell A1 and replaces it with blank. This essentially subtracts B1 from A1. Finally, the TRIM function checks for extra spaces and trims them.
Press Enter. Excel will now subtract one cell from the other and return the results.

The disadvantage or advantage of this formula, depending on your perspective, is that the SUBSTITUTE function's case sensitivity is carried on to the entire formula.

This makes the formula case-sensitive, and the formula won't subtract text from the target cell if the two have different cases. If you want to learn more about the SUBSTITUTE function, read our article on how to use the SUBSTITUTE function in Excel.

Case Insensitive Subtraction Formula

You can use a more complex formula to subtract text in Excel without case sensitivity. To do this, you'll need to use a case-insensitive function in the heart of your formula. One good function you can use is the REPLACE function.

        =REPLACE(old_text, start_num, num_chars, new_text)

Much like the SUBSTITUTE function, the REPLACE function finds a target string and then replaces it with a given string. However, the REPLACE function is more precise as you have to feed it the exact position of the characters you want to replace.

First, you have to indicate the position of the first character, then you have to input into the REPLACE function how many characters from that point on it should replace with the new string.

This sounds like a lot of work because if you were to count the starting character and the total amount of characters you want to subtract, you could just do the subtraction manually instead of using this formula.

Luckily, this is Excel we're talking about and everything can be automated. We're going to use two handy functions and have them do the indicating and counting for us: SEARCH, and LEN.

The SEARCH function looks for a string inside another string and then returns the position of the starting character. For instance, the formula =SEARCH(A1, B1) will look for the content of cell B1 within cell A1 and then return a number, indicating the position where the string in B1 begins in cell A1.

The LEN function counts the length of a string in character numbers and returns it. For instance, if we have a string such as MakeUseOf in cell D4, the formula =LEN(D4) will return the number 9, indicating that the length of this string is 9 characters. Note that this function also counts spaces as characters.

A combination of these two functions can tell the REPLACE function what to exactly look for, and what to replace it with. Since we're looking to subtract text, once we find the target string, we're going to replace it with a blank.

Subtracting text in Excel using the REPLACE function.

With all that being said, let's put this compound into use with an example similar to the previous one. Suppose we have an incorrect statement, and we want to correct it by subtracting a three-letter word from it.

Select a cell where you want to show the results of your formula. In this example, we're going to use cell D1.
Go to the formula bar and enter the formula below:
```
        =TRIM(REPLACE(A1, SEARCH(B1, A1), LEN(B1), ""))
    
```
The REPLACE function will look through cell A1 and select a string, starting at the position where the first character of the B1 string is present (using the SEARCH function) and ending after the length of the B1 string (using the LEN function). This means that combining LEN and SEARCH functions, the REPLACE function will pinpoint the B1 string in cell A1. Finally, it will replace it with “ ” or blank, effectively subtracting B1 from A1. The TRIM function is there to trim the extra spaces and make the final sentence look all tidy up.
Press Enter. Excel will now subtract the B1 string from cell A1.

Subtract Everything and Anything With Excel

Subtraction might be a mathematical function, but that doesn't mean you can't subtract strings of non-mathematical text from each other. Excel lets you get creative with different core functions, combine them and come up with a formula that does exactly what you want it to do.

Now that you have learned to subtract text in Excel using two different methods, go on and learn to subtract numbers, and you're well on your way to becoming the Excel subtraction master.