SUBSTITUTE equivalent function for a range of cells

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
150
Office Version
  1. 365
Platform
  1. Windows
In short, I am looking for a way to apply a function like SUBSTITUTE but to a range of cells. Let me provide more detail.

I have a list of text strings on Sheet1 from cells A1 to A800. The length of the text strings in this list range from 12 characters to 3,383 characters and does include cr/lf characters (char(10).

On Sheet2, I have a range of cells from BO4 to BO1741. This list is also text strings that range in length from 139 to 5,092 characters.

I want to find each one of the 800 entries on Sheet1 in the text strings in the range on Sheet2 and replace or substitute with null or "".

It sure seems like there is an easy solution here but since SUBSTITUTE does not work on a range, I am stumped. I am guessing I am going to have to use a macro but was hoping to avoid macros if possible and instead use some native Excel functions.

I am stumped so any help would be GREATLY APPRECIATED!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What version of Excel are you using? You can update your account details with that so that it shows automatically in your posts. I ask because some recursion options are available in 365.
 
Upvote 0
I looked at the about section in Excel and it says:
Version 16.59 (22031300)
License: Microsoft 365 Subscription
I also updated my profile and marked both versions.

Thanks for your help!
 
Upvote 0
There is a good tutorial on using a recursive LAMBDA function for this type of application:
Applying that approach here, go to Formulas > Name Manager and create a new name (in this case, "ReplaceAll") and in the "Refers to:" field, paste this:
Excel Formula:
=LAMBDA(T,B,A, IF(B = "", T, ReplaceAll(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0) ) ) )
I'm not sure what Sheet1 looks like, but here is a small working example where the SearchFor terms are in column A and the ReplaceWith terms are in column B (in your case, I think these would simply be left blank...or anything else you prefer).
Book1
AB
1SearchForReplaceWith
2abc
3sky
4green999
5$
6999*****
Sheet1

On Sheet2, you have text in BO4 and down. If you invoke the LAMBDA function and pass the three arguments to the function (Text to operate on, the before substring that needs to be replaced, and the after substring that will replace the before substring), the ReplaceAll LAMBDA will recursively examine the main text string and look through it for instances of each of the SearchFor substrings and replace them with the ReplaceWith substrings. I'm not sure if Char(10) will be handled correctly. Do Char(10) characters exist in both sheets, and do you want those characters to remain, or are you interesting in removing them?
Book1
BMBNBO
1Removing: abc, sky, green, $, 999
2After<---Before
3
4blue The red roseblue skyThe red rose
5riuiriuiabc
6The red roseblue The red roseblue sky
7eriupcv 6754354 #yjfdertabceriupcv 6754354 #$yjfdert
8blue blue skyabc
9eriupcv 6754354 #yjfderteriupcv 6754354 #$yjfdertabc
10***** grassabcgreen grass
11riuiabcriui
12***** grasseriupcv 6754354 #yjfdertgreen grasseriupcv 6754354 #$yjfdert
Sheet2
Cell Formulas
RangeFormula
BN1BN1="Removing: "&TEXTJOIN(", ",FALSE,Table1[SearchFor])
BM4:BM12BM4=ReplaceAll(BO4,Sheet1!$A$2,Sheet1!$B$2)
 
Upvote 0
There is a good tutorial on using a recursive LAMBDA function for this type of application:
Applying that approach here, go to Formulas > Name Manager and create a new name (in this case, "ReplaceAll") and in the "Refers to:" field, paste this:
Excel Formula:
=LAMBDA(T,B,A, IF(B = "", T, ReplaceAll(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0) ) ) )
I'm not sure what Sheet1 looks like, but here is a small working example where the SearchFor terms are in column A and the ReplaceWith terms are in column B (in your case, I think these would simply be left blank...or anything else you prefer).
Book1
AB
1SearchForReplaceWith
2abc
3sky
4green999
5$
6999*****
Sheet1

On Sheet2, you have text in BO4 and down. If you invoke the LAMBDA function and pass the three arguments to the function (Text to operate on, the before substring that needs to be replaced, and the after substring that will replace the before substring), the ReplaceAll LAMBDA will recursively examine the main text string and look through it for instances of each of the SearchFor substrings and replace them with the ReplaceWith substrings. I'm not sure if Char(10) will be handled correctly. Do Char(10) characters exist in both sheets, and do you want those characters to remain, or are you interesting in removing them?
Book1
BMBNBO
1Removing: abc, sky, green, $, 999
2After<---Before
3
4blue The red roseblue skyThe red rose
5riuiriuiabc
6The red roseblue The red roseblue sky
7eriupcv 6754354 #yjfdertabceriupcv 6754354 #$yjfdert
8blue blue skyabc
9eriupcv 6754354 #yjfderteriupcv 6754354 #$yjfdertabc
10***** grassabcgreen grass
11riuiabcriui
12***** grasseriupcv 6754354 #yjfdertgreen grasseriupcv 6754354 #$yjfdert
Sheet2
Cell Formulas
RangeFormula
BN1BN1="Removing: "&TEXTJOIN(", ",FALSE,Table1[SearchFor])
BM4:BM12BM4=ReplaceAll(BO4,Sheet1!$A$2,Sheet1!$B$2)
KRice -

This is in the ballpark of what I am attempting to do but not sure it exactly what I am looking to do. Before I can show you what I am looking for, can you tell me how to do the embedded spreadsheet HTML code so can give a good example? I was not able to figure out how to add this to a post.

Thanks!!!
 
Upvote 0
Sure, click on the XL2BB hyperlink in my signature block to go to the Excel add-in. You'll find an orange download button on that page and instructions for installing it. In a post where you see the mini sheets created by this add-in, you should be able to click on the clipboard icon that appears in the upper left at the intersection of the row and column headers. That copies the content to your clipboard, which can then be pasted directly into your worksheet. Typically I navigate to the same upper left cell shown in the mini sheet and paste there to avoid any formula issues with cell references/ranges.
 
Upvote 0
Sure, click on the XL2BB hyperlink in my signature block to go to the Excel add-in. You'll find an orange download button on that page and instructions for installing it. In a post where you see the mini sheets created by this add-in, you should be able to click on the clipboard icon that appears in the upper left at the intersection of the row and column headers. That copies the content to your clipboard, which can then be pasted directly into your worksheet. Typically I navigate to the same upper left cell shown in the mini sheet and paste there to avoid any formula issues with cell references/ranges.
Well, I did not see that!! Here is what I am looking to do:

2022-04-14 11-21 TO 3-22 Customer Comments Cleanup for TA.xlsx
ABCD
1List Of Text Strings To Be Removed From Text Strings In Col BText Strings With Text From Col A That Are To Be RemovedResult Text String After ALL Strings In Col A Are Removed From Col B StringsNotes
2There is such a function, and its name is LAMBDA.There is such a function, and its name is LAMBDA. Understanding the LAMBDA function is important. Although you may not create your own custom functions using LAMBDA, you are likely to encounter LAMBDA functions created by others. It’s a good idea to have a basic understanding of this amazing function. Let’s dive right in and see how it all works. This way, you can speak about LAMBDA functions with authority at your next Excel party. (You know those parties are amazing!) Recycling LAMBDA Logic The LAMBDA function is currently available to Office 365 BETA Channel subscribers only, and it’s limited to only a fraction of those users.Understanding the LAMBDA function is important. Although you may not create your own custom functions using LAMBDA, you are likely to encounter LAMBDA functions created by others. It’s a good idea to have a basic understanding of this amazing function. This way, you can speak about LAMBDA functions with authority at your next Excel party. (You know those parties are amazing!)Note that all text strings in Col A have been removed from Col B text string to get the resulting text in Col C
3The LAMBDA function is currently available to Office 365 BETA Channel subscribers only, and it’s limited to only a fraction of those users.An interesting thing that can be done when creating a LAMBDA is to supply a test scenario during the LAMBDA creation process. This helps us know if our LAMBDA has been created and operating properly. By adding open parentheses to the end of the formula then providing an example of the needed parameter(s), we can see if everything is functioning as expected.An interesting thing that can be done when creating a LAMBDA is to supply a test scenario during the LAMBDA creation process. This helps us know if our LAMBDA has been created and operating properly. By adding open parentheses to the end of the formula then providing an example of the needed parameter(s), we can see if everything is functioning as expected.This one did not change because it did not have any of the text strings in column A
4Let’s dive right in and see how it all works.We will accomplish the same thing with a formula. We have asked users to list their software skills, but the way they have listed these skills is not the way we wish to have them appear in the report. Recycling LAMBDA Logic We want to locate specific words/phrases and replace them with corrected items from a list. We will accomplish the same thing with a formula.We have asked users to list their software skills, but the way they have listed these skills is not the way we wish to have them appear in the report. We want to locate specific words/phrases and replace them with corrected items from a list.Note that all text strings in Col A have been removed from Col B text string to get the resulting text in Col C
5We will accomplish the same thing with a formula.By keeping the logic simple, we can focus on the mechanics of the LAMBDA creation process and not worry so much about the logic of the operation being performed. Our data looks like the following. We have a list of prices and we wish to calculate a 30% discount for each price. We click next to the first price and enter the following formula. Recycling LAMBDA Logic We’re going to tell this story in reverse. The results are shown below. By keeping the logic simple, we can focus on the mechanics of the LAMBDA creation process and not worry so much about the logic of the operation being performed. We have a list of prices and we wish to calculate a 30% discount for each price. We click next to the first price and enter the following formula.Note that all text strings in Col A have been removed from Col B text string to get the resulting text in Col C
6We’re going to tell this story in reverse.
7The results are shown below.
8Our data looks like the following.
9Recycling LAMBDA Logic
Sheet1
 
Upvote 0
I think my previous post does this--almost. I took your column A and pasted it into Sheet1 column A (I think that's where your substrings to be removed are located?). And I took your column B and pasted it into Sheet2 cell BO4 and down...and the ReplaceAll LAMBDA function generated the same apparent text. Be careful in your list of strings to be removed on Sheet1. Once the LAMBDA encounters a blank in column A Sheet1, that is interpreted as the end of the list, so the LAMBDA will exit the recursion. I say the same "apparent" text because there are some leading/trailing spaces and I believe some non-printing characters that may be within the text. When I wrap your column C text with TRIM and CLEAN functions and do the same for the ReplaceAll LAMBDA results, I get matching strings for 3 of the 4 rows. The CLEAN is stripping out the paragraph breaks. or line feed characters, so that may not be a preferred approach.

Which non-printing characters do you want to keep...and do you know which ones, if any, you'd like to remove?

Here is the latest version that comes close to the mark, except for the special characters issue
Excel Formula:
=TRIM(CLEAN(ReplaceAll(BO3,Sheet1!$A$2,Sheet1!$B$2)))
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top