Replacing part of a string with a value of cell for each row

[diamanthian]

I've been trying to work out how to do this for some time on my own and thought it was about time I asked for some help

I've not worked with Excel for years and it is not all coming back.
I have a several columns with a few rows containing strings such as

	a	b	c
1	product is xxxx thanks	cat	is xxxx alive
2	product is xxxx thanks	dog	is xxxx alive
3	product is xxxx thanks	mouse	is xxxx alive
4	product is xxxx thanks	horse	is xxxx alive

The data I am working on is not about if cats and dogs are alive, but I thought this would be a much simpler example as it is the same thing
I have thousands of rows with multiple columns some of which have "xxxx" within the cell,
I would like to replace "xxxx" with the contents of column B from the same row but leave the rest of the cell's string untouched

but can't for the life of me figure out how to do it
I was initially writing concatenate statements in each column and then copying them for each row, but its taking a long time and I'm sure there must be a way of saying

FOR each cell in current worksheet
IF string in(Current cell) contains "xxxx"
replace "xxxx" with B(current row)
Next cell
END IF


Any Guidance would be appreciated

[stanleydgromjr]

diamanthian,

Welcome to the Excel Forum.


With your raw data in range A1:C4, try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Replace_xxxx_with()
' stanleydgromj, 01/18/2013
' http://www.excelforum.com/excel-programming-vba-macros/892306-replacing-part-of-a-string-with-a-value-of-cell-for-each-row.html
Dim c As Range

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If InStr(c, "xxxx") > 0 Then
    c = Replace(c, "xxxx", c.Offset(, 1))
  End If
Next c

For Each c In Range("C1", Range("C" & Rows.Count).End(xlUp))
  If InStr(c, "xxxx") > 0 Then
    c = Replace(c, "xxxx", c.Offset(, -1))
  End If
Next c

End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Replace_xxxx_with macro.

[diamanthian]

Fantastic!

Very fast response and it worked a treat,
I hope this thread can be of use to some other poor soul trawling Google One day
Thanks Very much!

[stanleydgromjr]

diamanthian,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.