Contents of entire cell copied, replaced with a specific string of text in another cell

[capo4410]

Hello,

I am having some difficulties in achieving the following. I would like the entire contents located in say cell A1 to replace a certain string of text located in another cell, K1. The contents of column A will always be different, with varying lengths of characters and numbers. The text I want to replace in K1 with A1, is always in the same location towards the beginning of the cell wrapped in some formatting html. (11 characters before first word and 4 characters after last)

At first I thought the copy, replace or paste functions would be the ones to use, but after researching, I cannot find anything where the replacement is a specific string of text within a cell. Unless, I missed it in my searching. String functions don’t seem to be my solution either,as shown in my attempt at one below. Any help is greatly appreciated. Thanks in advance!


Sub replace()

Dim myCell As Range
Dim myStringToReplace As String
Dim myReplacementString As String

Set myCell =ThisWorkbook.Worksheets("Items CSV Test").Range("K1")

myStringToReplace = "Make Model Item"
myReplacementString = "ABC 123Controller"

myCell.Value =Replace(Expression:=myCell.Value, Find:=myStringToReplace,Replace:=myReplacementString)

End Sub

 

[DanteAmor]

Try this

Sub Replace_Specific_String()
    Range("K1").Value = Left(Range("K1").Value, 11) & Range("A1").Value & Right(Range("K1").Value, 4)
End Sub

A1= "New data"
K1 = "Make Model X Item"

Result

K1= "Make Model new data Item"

 

[capo4410]

Thank you Dante. This worked perfectly although I neglected to mention the string of text in K1 is a description populated from a macro. K1 can have 1 of 14 different descriptions dependent on specific situations. The descriptions are several hundred characters long, however they are the same up until the insertion point. After running your macro - the 4 characters displayed at the end of the string are the 4 very last characters in the description.

Thank you very much, Dante.

 

[DanteAmor]

Could you give some examples of what you have and what you expect of the result?
something like this

A1= "New data"
K1 = "Make Model X Item"


Result


K1= "Make Model new data Item"

 

[capo4410]

A1 = MKS 123A Valve

K1 = Make Model Item Product is new from old surplus stock and is not tested.

Result

K1 = MKS 123A Valve Product is new from old surplus stock and is not tested.


Note that I have html center and line breaks tags around Make Model Item but cannot add them here as they center the data on the page.

Also, I did not add the entire block of text as it is very long. This is one situation. Another situation for example would read after new data from A1 Product is used and is not tested.

I hope this helps.... Thank you.

 

[DanteAmor]

Try this

Sub Macro1()
    Range("K1").Replace What:="Make Model Item", Replacement:=Range("A1").Value, LookAt:=xlPart
End Sub

If it's not what you need, then 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. If the workbook contains confidential information, you could replace it with generic data.

 

[capo4410]

That did work, Dante. Thank you. One last question...how would this macro automatically apply to the next row? As of now I have to edit the macro each time to change the row number .i.e. K1, A1 to K2, A2.

Below is a link to the xlsm file I'm working with. I hope you can access it. (I have a header row. I used row 1 in question thinking it'd be generic) Thank you.

https://www.dropbox.com/s/m8mbzrgb67gor3m/2_14_19_Test.xlsm?dl=0

 

[DanteAmor]

Use the following to each cell in column A

Sub Macro1()
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("K" & i).Replace What:="Make Model Item", Replacement:=Range("A" & i).Value, LookAt:=xlPart
    Next
End Sub

 

[capo4410]

This worked perfectly! Thank you so much, Dante! You really helped me and my colleagues with a project we are planning. Have a wonderful weekend!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.