#Excel for mac delete multiple cells how to
#Excel for mac delete multiple cells full
#Excel for mac delete multiple cells code

Before each recursive call, the IF function checks the remaining chars. In essence, the RemoveChars function loops through the list of chars and removes one character at a time. To eliminate special characters from A2, the formula is:

=LAMBDA(string, RemoveChars(string, '0123456789'))īoth of the above functions are super-easy to use as they require just one argument - the original string. =LAMBDA(string, RemoveChars(string, remove numbers from text strings, we've created one more function named RemoveNumbers: To delete special characters, we've created a custom function named RemoveSpecialChars: To delete a predefined set of characters from multiple cells, you can create another LAMBDA that calls the main RemoveChars function and specify the undesirable characters in the 2 nd parameter. And due to support for dynamic arrays, the formula spills automatically into all the referenced cells: Since the formula is entered only in the top-most cell, you needn't worry about locking the cell coordinates - a relative reference (D2) works fine in this case. To clean multiple cells with a single formula, supply the range A2:A6 for the 1st argument: The address of the cell containing the special characters is locked with the $ sign ($D$2) to prevent the reference from changing when coping the formula to the below cells.Īnd then, we simply drag the formula down and have all the characters listed in D2 deleted from cells A2 through A6:.In D2, characters are listed without spaces, unless you wish to eliminate spaces too.To remove those characters from A2, the formula is:įor the formula to work correctly, please take notice of the following things: Can be represented by a text string or a cell reference.įor convenience, we input unwanted characters in some cell, say D2. String - is the original string, or a reference to the cell/range containing the string(s).Once the function gets a name, you can refer to it like any native formula.įrom the user's viewpoint, the syntax of our custom function is as simple as this:

#Excel for mac delete multiple cells how to

The parameters will be displayed when you type a formula in a cell.įor the detailed instructions, please see How to name a custom LAMBDA function.

Optionally, enter the description of the parameters in the Comments box.

In the Refers to box, paste the above formula.

In the Name box, enter the function's name: RemoveChars.

For this, press Ctrl + 3 to open the Name Manager, and then define a New Name in this way: To be able to use this function in your worksheets, you need to name it first. =LAMBDA(string, chars, IF(chars'', RemoveChars(SUBSTITUTE(string, LEFT(chars, 1), ''), RIGHT(chars, LEN(chars) -1)), string)) Below, I'll illustrate the concept with a couple of practical examples.Ī custom LAMBDA function to remove unwanted characters is as follows:

#Excel for mac delete multiple cells full

This new function is named LAMBDA, and you can find full details about it in the above-linked tutorial. The solution only works in Excel for Microsoft 365Īs you probably know, Excel 365 has a special function that enables you to create your own functions, including those that calculate recursively. The next example demonstrates a more compact and elegant solution. Nested SUBSTITUTE functions work fine for a reasonable number of characters, but if you have dozens of characters to remove, the formula becomes too long and difficult to manage.

#Excel for mac delete multiple cells code

The same can be done with the help of the CHAR function, where 161 is the character code for '¡' and 191 is the character code for '¿': SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( cell, char1, ''), char2, ''), char3, '')įor example, to eradicate normal exclamation and question marks as well as the inverted ones from a text string in A2, use this formula: