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!
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!