Excel formula | Excel SUBSTITUTE multiple characters
![Excel formula](https://i.imgur.com/axBPWDg.jpg)
Thereisnobuilt-infunctiontoperformaseriesoffindandreplaceoperationsinExcel,butthisarticleexplainshowyoucancobbletogetheraformulathatdoesthesamething.Thetexttolookforandreplacewithisstoreddirectlyontheworksheetinatable,andretrievedwiththeINDEXfunction.Thismakesthesolution"dynamic"–ifanyofthesevaluesarechanged,resultsupdateimmediately.Ofcourse,thereisnorequirementtouseINDEX;youcanhard-codevaluesintotheformulaifyouprefer.Atthecore,theformulausestheSUBSTITUTEfunctiontoperformeachsubstitution,wit...
![Excel formula](http://pix1.agoda.net/hotelimages/300/3008492/3008492_17121510550060424840.jpg?s=800x)
There is no built-in function to perform a series of find and replace operations in Excel, but this article explains how you can cobble together a formula that does the same thing. The text to look for and replace with is stored directly on the worksheet in a table, and retrieved with the INDEX function. This makes the solution "dynamic" – if any of these values are changed, results update immediately. Of course, there is no requirement to use INDEX; you can hard-code values into the formula if you prefer.
At the core, the formula uses the SUBSTITUTE function to perform each substitution, with this basic pattern:
=SUBSTITUTE(text,find,replace)"Text" is the incoming value, "find" is the text to look for, and "replace" is the text to replace with. The text to look for and replace with is stored in the table to the right, in the range E5:F8, one pair per row. The values on the left are in the named range[1]"find" and the values on the right are in the named range "r...
取得本站獨家住宿推薦 15%OFF 訂房優惠
本站住宿推薦 20%OFF 訂房優惠,親子優惠,住宿折扣,限時回饋,平日促銷
Excel substitute function usage(8 examples | Excel SUBSTITUTE multiple characters
Using Excel REPLACE and SUBSTITUTE functions | Excel SUBSTITUTE multiple characters
Excel | Excel SUBSTITUTE multiple characters
Substitute Multiple Characters | Excel SUBSTITUTE multiple characters
Can I Substitute multiple characters in one ... | Excel SUBSTITUTE multiple characters
Excel formula | Excel SUBSTITUTE multiple characters
How to use SUBSTITUE function in Excel? | Excel SUBSTITUTE multiple characters
How To Find And Replace Multiple Text Strings Within A Text ... | Excel SUBSTITUTE multiple characters
Substitute Multiple Values | Excel SUBSTITUTE multiple characters
Excel SUBSTITUTE Function – How to Use | Excel SUBSTITUTE multiple characters
![](https://i.imgur.com/axBPWDg.jpg)