How to remove in excel

Delete a formula but keep the results

To do this, you copy the formula and then paste in the same cell by using the Paste Values option.

1. Select the cell or range of cells that contains the formula.
   If the formula is an array formula, you must first select all cells in the range of cells that contains the array formula:

   1. Click a cell in the array formula.

   2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.

   3. Click Special.

   4. Click Current array.

2. On the Home tab, in the Clipboard group, click Copy

   
   .

3. On the Home tab, in the Clipboard group, click the arrow below Paste

   , and then click Paste Values.

Delete an array formula

To delete an array formula, make sure you select all cells in the range of cells that contains the array formula. To do that:

1. Click a cell in the array formula.

2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.

3. Click Special.

4. Click Current array.

5. Press DELETE.

Delete a formula but keep the results

1. Select the cell or range of cells that contains the formula.

2. Click Home > Copy (or press Ctrl + C).

3. Click Home > arrow below Paste > Paste Values.

1. Select the cell with the drop-down list.

   If you have multiple cells with drop-down lists that you want to delete, you can use Ctrl+Left click to select them.

2. Click Data >Data Validation.

3. On the Settings tab, click Clear All.

4. Click OK

If you need to remove all Data Validation from a worksheet, including drop-down lists, but you don't know where they are, then you can use the Go To Special dialog. Press Ctrl+G > Special, then Data Validation > All or Same, and repeat the steps above.

If, instead of deleting it, you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.

1. Select the cell with the drop-down list.

   If you have multiple cells with drop-down lists that you want to delete, you can use Ctrl+Left click to select them.

2. Click Data >Data Validation.

3. On the Settings tab, click Clear All.

4. Click OK

If you need to remove all Data Validation from a worksheet, including drop-down lists, but you don't know where they are, then you can use the Go To Special dialog. Press Ctrl+G > Special, then Data Validation > All or Same, and repeat the steps above.

If, instead of deleting it, you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.

1. Select the cells with the drop-down list.

2. Click Data >Data Validation.

3. On the Settings tab, click Clear All.

4. Click OK.

If, instead of deleting the drop-down list, you decide you want to change the options, see Add or remove items from a drop-down list.

Summary 

To remove specific unwanted characters in Excel, you can use a formula based on the SUBSTITUTE function. In the example shown, the formula in C4 is:

Which removes a series of 4 invisible characters at the start of each cell in column B.

Explanation 

The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which effectively removes the character completely.

How can you figure out which character(s) need to be removed, when they are invisible? To get the unique code number for the first character in a cell, you can use a formula based on the CODE and LEFT functions:

Here, the LEFT function, without the optional second argument, returns the first character on the left. This goes into the CODE function, which reports the characters code value, which is 202 in the example shown.

For more general cleaning, see the TRIM function and the CLEAN function.

All in one formula

In this case, since we are stripping leading characters, we could combine both formulas in one, like so:

Here, instead of providing character 202 explicitly to SUBSTITUTE, we are using CODE and CHAR to provide a code dynamically, using the first character in the cell.

Related formulas 

The formula runs from the inside out, with each SUBSTITUTE removing one character. The inner most SUBSTITUTE removes the left parentheses, and the result is handed to the next SUBSTITUTE, which removes the right parentheses, and so on. Whenever you...

In this example, the goal is to remove non-numeric characters from a text string with a formula. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW...

The MID function returns characters using a fixed starting point and ending point. In this case, the markup consists of the html bold tag, which appears at the start of each cell and the associated closing tag, which appears at the end. The MID...

The TRIM function is fully automatic. It removes removes both leading and trailing spaces from text, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a reference to a cell. TRIM with CLEAN...

Related functions 

The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.

The Excel CHAR function returns a character when given a valid character code. CHAR can be used to specify characters that are hard to enter in a formula. For example, CHAR(10) returns a line break, and can be used to add a line break to text in...

The Excel CODE function returns a numeric code for a given character.  For example, CODE("a") returns the code 97.

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".

Related videos 

Each character you see displayed in Excel has a number. In this video we look at two functions that work with these numbers directly: CODE and CHAR.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel video training

Quick, clean, and to the point.

Learn more