Replace multiple specific word with specific cell reference

[Sathish G]

Hi Everyone,

Hope all going good!!!!

I just need your support to replace multiple words with specific cell reference value.

I have attached IMAGE and excel sheet with my requirement.

Please help me to get the code for this.


All your supports are always appreciated.

 

[mumps]

Try:

Sub test()
    With Columns("A")
        .Replace "sort", Range("D2").Value, xlPart
        .Replace "range", Range("D2").Value, xlPart
    End With
End Sub

 

[Sathish G]

Works perfectly thanks

 

[mumps]

You are very welcome.

 

[Sathish G]

Hi Mumps,

Thanks for the previous code. That's working great but when I replacing the letter sort it replacing the all the sort in columns. Is it possible to replace like this way.

Replace the "sort" and merge with behind letters.



Sub test()
With Columns("A")
.Replace "sort" & "-value-servername", Range("D2").Value, xlPart
.Replace "range" & -"range-value", Range("D2").Value, xlPart
End With
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. You have your data in column A. Include the expected results in column B for each value in column A. (de-sensitized if necessary).

 

[Sathish G]

I have uploaded on the below Dropbox

value replace.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

I'm sorry but column B does not contain the expected results as I had requested in Post #6.

Include the expected results in column B for each value in column A.

 

[navic]

Try in the 'B2' cell (copy down)

=IF(AND(ISERROR(SEARCH("sort",A2,1))=FALSE,ISERROR(SEARCH("range",A2,1))=TRUE,ISERROR(SEARCH("value",A2,1))=FALSE)=TRUE,SUBSTITUTE(A2,("Sort"),$D$2,1),IF(AND(ISERROR(SEARCH("sort",A2,1))=FALSE,ISERROR(SEARCH("range",A2,1))=FALSE,ISERROR(SEARCH("value",A2,1))=FALSE)=TRUE,SUBSTITUTE(A2,("range"),$D$2,1),IF(AND(ISERROR(SEARCH("sort",A2,1))=TRUE,ISERROR(SEARCH("range",A2,1))=TRUE,ISERROR(SEARCH("value",A2,1))=FALSE)=TRUE,SUBSTITUTE(A2,("value"),$D$2,1),"")))

 

[Sathish G]

Works perfectly thanks