The SUBSTITUTE function lets you replace text by matching content.
In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an empty string ("") for replacement text.
SUBSTITUTE will replace all instances of "-" with nothing.
Note that SUBSTITUTE is a case-sensitive function.
Removing more than one thing
If you need to remove more than one thing, you can nest multiple SUBSTITUTE functions. For example, to remove square brackets from text, you can use:
To remove text from a cell by position, you can use the REPLACE function. In the example shown, the formula in C6 is: = REPLACE ( B6 , 1 , 24 , "" ) How this formula works The replace function lets you replace text based on its location...
If you need to strip leading and trailing spaces from text in one or more cells, you can use the TRIM function. In the example show, the formula in cell C3 is: = TRIM ( B3 ) Once you've removed extra spaces, you can copy the cells with formulas and...
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.
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.