How to replace part of formula in all cells
Table of Contents
1. How to replace part of formula in all cells
This section explains how to substitute part of a formula across all cells in a worksheet. It is easier than you think, no VBA programming or formulas are needed.
The picture above demonstrates a simple example, the formula in cell B3 gets values below 5 from sheet 2 cell range B3:B12.
Array formula in cell B3
I will now show you how to replace Sheet2 with Sheet3 in formulas, in all cells in Sheet1. Simply press CTRL and H to open the Find and Replace dialog box.
Press with left mouse button on the "Options" button to see all settings.
Here you have the option to
- Search the entire workbook or just the active worksheet. I want to search the active worksheet so I change nothing.
- Match entire cell contents. Deselect the check box, I want to match specific strings in formulas.
Press with left mouse button on in field "Find what:" and type Sheet2. Now press with left mouse button on in field "Replace with:" and type Sheet3, then press with left mouse button on "Replace All" button.
This will find all instances of Sheet2 in all cells and replace them with Sheet3.
Press with left mouse button on the "OK" button and then the "Close" button.
The array formula in cell B3 (Sheet1) changes to:
Recommended article
Recommended articles
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
1.1 Replace part of formula in a specific cell range
Simply select the cell range, press CTRL + H to open the Find and Replace dialog box.
The "Find and Replace" action will now be applied to cell range B3:B4.
1.2 Replace n:th instance
Array formula in cell B3 (Sheet1)
To replace only the first instance of a specific search string in the formula simply include more characters so it makes the search string unique.
Example, you want to replace Sheet2 with Sheet3 but only the first instance found in the formula.
Press CTRL + H to open the Find and Replace dialog box.
Don't forget to add the included characters in the "Replace with: " field as well.
String IF(Sheet2 is found in only one location in each cell, this will replace only the first instance of Sheet2.
2. Substitute multiple text strings - Excel 365 recursive LAMBDA function
The SUBSTITUTE and REPLACE functions can only handle one string, the next two sections demonstrate two ways to handle more than one pair of values.
- An Excel 365 recursive LAMBDA function.
- A User-Defined Function (UDF) allows you to substitute multiple text strings with new text strings.
This example demonstrates a formula that iterates through all values in cell range E3:F4 in order to substitute specific values in cell B3 with new values.
The SUBSTITUTE function allows you to substitute one value with another value, however, this formula lets you substitute (almost) any number of values.
Excel 365 LAMBDA function in Name Manager:
The LAMBDA function is named SUBSTR in the Name Manager.
Excel 365 formula in cell C3:
2.1 Explaining LAMBDA formula
Step 1 - Get old string
The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Function syntax: INDEX(array, [row_num], [column_num])
INDEX(sub,n,1)
sub is a variable, in this example cell reference $E$3:$F$4
n is a number, representing the number of rows in cell reference $E$3:$F$4
1 is the first column in cell reference $E$3:$F$4
Step 2 - Get new string
The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Function syntax: INDEX(array, [row_num], [column_num])
INDEX(sub,n,2)
sub is a variable, in this example cell reference $E$3:$F$4
n is a number, representing the number of rows in cell reference $E$3:$F$4
2 represents the second column in cell reference $E$3:$F$4
Step 3 - Substitute given values
The SUBSTITUTE function replaces a specific text string in a value. Case sensitive.
Function syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(str,INDEX(sub,n,1),INDEX(sub,n,2))
Step 4 - Make this formula recursive
The LAMBDA function is named SUBSTR in the name manager. We can call this named formula again until all values have been used.
SUBSTR(str,sub,n)
The SUBSTR function has three arguments:
- str - the string
- sub - the values to substitute
- n - the number of rows (or substitute pairs in cell ref $E$3:$F$4
SUBSTR(SUBSTITUTE(str,INDEX(sub,n,1),INDEX(sub,n,2)),sub,n-1)
str => SUBSTITUTE(str,INDEX(sub,n,1),INDEX(sub,n,2))
sub => sub (no change)
n => n - 1
n is keeping track of the row number. By subtracting one for each iteration the changes and a new pair of substitute values are processed.
Step 5 - Control the recursive formula
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
Function syntax: IF(logical_test, [value_if_true], [value_if_false])
The IF function returns the string str if n is equal to 0 (zero), however, it calls the SUBSTR function again if not. This step is repeated like a loop until n is equal to 0 (zero).
IF(n=0,str,SUBSTR(SUBSTITUTE(str,INDEX(sub,n,1),INDEX(sub,n,2)),sub,n-1))
Step 6 - Create the LAMBDA function and define the parameters
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
LAMBDA(str,sub,n,IF(n=0,str,SUBSTR(SUBSTITUTE(str,INDEX(sub,n,1),INDEX(sub,n,2)),sub,n-1)))
2.2 Name LAMBDA function in the Name Manager
- Go to tab "Formulas" on the ribbon.
- Press the left mouse button on the "Name Manager" button.
A dialog box opens. - Press with left mouse button on the "New..." button. A new dialog box appears.
- Name the function.
- Paste the LAMBDA formula in the "Refers to:" field.
- Press with left mouse button on OK button.
You are now ready to use the named LAMBDA function. Select an empty cell. Type =SUBSTR( and the arguments, don't forget the ending parentheses. Press Enter.
3. How to use the User Defined Function
You may have as many strings as you like, there is really no limit. The image above shows the UDF in cell C3 using strings from E3:E4 and F3:F4.
Formula in cell C3:
The UDF will not appear and work yet until you have copied the code below to a module in your workbook. There are instructions below.
Got it working? Now copy cell C3 and paste to cells below, the first argument contains relative cell references meaning they will change automatically when you copy and paste cell C3 to cells below.
The second and third argument are absolute cell references, they contain dollar signs meaning they are locked to cell range $D$2:$D$3 and $E$2:$E$3. These cell references will not change when you copy cell C3 and paste to the cells below.
3.1 User Defined Syntax
SubstituteMultiple(text As String, old_text As Range, new_text As Range)
3.2 Arguments
| text | Required. A cell reference to a cell containing the text you want to manipulate. |
| old_text | Required. A cell reference to one or many cells containing strings you want to replace. |
| new_text | Required. A cell reference to one or many cells containing strings you want instead. |
3.3 VBA code
'Name function and dimension argument variables and declare their data types
Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)
'Dimension variable and declare data type
Dim i As Single
'Iterate through cells in argument old_text
For i = 1 To old_text.Cells.Count
'Replace strings in value based on variable i
Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))
'Save manipulated value to variable text
text = Result
Next i
'Return value stored in variable Result to worksheet
SubstituteMultiple = Result
End Function
3.4 Where to put the code?
- Copy above VBA code.
- Go to the Visual Basic Editor (Shortcut keys Alt + F11).
- Press with left mouse button on Insert on the top menu.
- Press with left mouse button on Module to create a module in your workbook. A module named module1 appears in the Project Explorer.
- Paste code to module.
- Exit VB Editor
3.5 How to save a macro-enabled workbook
Excel basics category
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
Text string manipulation category
More than 1300 Excel formulasExcel categories
9 Responses to “How to replace part of formula in all cells”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Do you have a version that is case sensitive?
Ari,
Function SubstituteMultiple(text As String, old_text As Range, new_text As Range) Dim i As Single For i = 1 To old_text.Cells.Count Result = Replace(text, old_text.Cells(i), new_text.Cells(i)) text = Result Next i SubstituteMultiple = Result End FunctionGreat solution, thanks!
this is best solution from whole google search results , Thanks very much.
but there is little difference within Substitute and SubstituteMultiple:
Substitude Multiple Changes Texts UperCase symbols To LowCase, can someone help how to fix this?
Zurab Lomidze,
Thank you. Try this udf, it is case sensitive.
Function SubstituteMultipleCS(text As String, old_text As Range, new_text As Range) Dim i As Single For i = 1 To old_text.Cells.Count Result = Replace(text, old_text.Cells(i), new_text.Cells(i)) text = Result Next i SubstituteMultipleCS = Result End FunctionYou saved my day, Thanks
omgness, thank you so much
Great post, Oscar; I use this function all the time. A problem I need help with is that the replacement keeps looping through my replaced text. For example, I have a "Remarks" column with a cell containing "A,B". Each character represents an index item from a multi-column table elsewhere.
A = ALMOND BERRY
B = BLUE COCONUT
Here is the formula I'm running:
=SubstituteMultiple("A,B",{"A";"B"},{"ALMOND BERRY";"BLUE COCONUT"})
I want to produce the following result:
"ALMOND BERRY,BLUE COCONUT"
However, the following results from the SubstituteMultiple UDF:
"ALMOND BLUE COCONUTERRY,BLUE COCONUT"
The problem is that the index character is an alphabetical character that appears in the second of the replacement phrase, thus causing a replacement loop on the second term in the "new_text".
What are your thoughts? Thanks for your effort.
This is great. I don't know why excel doesn't have inbuilt functionality for multiple substitutes (in the vein of IF vs IFS, or the above)
Is anyone able to help modify the above so it can handle an array as the Text argument? I would like the function to remove all instances of Old_text from an entire column.
Thanks in advance!