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!