Find Text in String and Replace it with the other Text in the same String

• I need to find a Text, set in cell e.g. ("A1"), within various strings in a certain Range, e.g. ("B1:K20") /there are different strings by long in cells in the Range, but always contain one or even more times text set by A1/, and Replace this Text with the other Text, set by cell, e.g ("A2").
I would like to do so using VBA macro, despite the fact that I could simply select the range and use Ctrl+H with Find Text and Replace Text. Many thanks.

• Re: Find Text in String and Replace it with the other Text in the same String

This should do what you want - it will replace all instances of the the value in A1 with the values in A2 - so if the text in A1 occurs more than once in a specific cell it will replace all the instances of this. If I have not understood what you want just let me know

Hope this helps
Anthony

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: Find Text in String and Replace it with the other Text in the same String

Anthony,
many thanks for this, it works perfectly. But, one more question. I had some columns hidden and those weren't replaced with the new value set in A2 cell.
Is it possible to re-set the below macro somehow enabling also hidden columns/cells/range to be replaced with the required text in A2?
Cheers:-)

• Re: Find Text in String and Replace it with the other Text in the same String

You can unhide all the columns prior to executing the text substitution. The new code is as follows. As you see it only needs one more line of code.

You can the hide the columns after doing the substitution if you want to using something like

Code
``````Columns("C:C").Select
Selection.EntireColumn.Hidden = True``````

Hope this is what you want
Anthony

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!