Macro to replace part of a cell with contents of another cell

[Oggy0610]

I am trying to get a macro that replaces xxx with what I enter in a specific cell. I have a template file made up where all the numbers just have xxx where the actual number will be so it says MSN XXX PORT and then normally I replace the XXX with the number so MSN 232 PORT.

I'm looking for a macro that can do it so that I type what the number will be in a cell and then it replaces XXX with the contents of the specified cell.

I know it may seem easier to just use find replace but I work with other users who's computer knowledge is less than basic.

When I have tried to record a macro it saves whatever number I put in the specified cell and replaces all the XXX's with the same number every time.

Hope someone can help.

Thanks.

 

[Michael M]

maybe something like this....assuming "MSN XXX PORT" is in cell A1

Sub MM1()
Dim ans As String
ans = InputBox("What number do you want to insert ?")
Range("a1").Value = Replace(Range("A1"), "XXX", ans, 1)
End Sub

 

[mikerickson]

Might it work to just format the cells with "MSN "000" PORT" so that all the user has to do is type 232 into the cell?

That sounds a lot more intuitive than the user 1)specify the target cell 2) go to the entry cell 3) type 232 4) push a button.