Substitute a text string with multiple values

Hi folks.

I've been up and down every post I cannot find way to solve the issue that I've been tasked to resolve.
Here is what I've got so far...
I have data in Column A that contains cells where STRING1 can be found.
The idea is to be able to search for every cell in Column A that contains STRING1 and insert new cell bellow with substituted value taken from Column C Titled STRING1.
In other words I have Column A where is the data and Column C where is the values for STRING1
The result is Column B where a new cell/row has been added for each substituted value from Column C. The result can be either created in separated row or the the new cells can be added within the already existing Column A by adding the cells or rows


Column A Column B Column C
This is what I have this is how the result should look line

My name is <STRING1> and I'm strong. My name is <STRING1> and I'm strong. <STRING1>
Some text My name is George and I'm strong. George
Some other text My name is Peter and I'm strong. Peter
Some other text My name is Thomas and I'm strong. Thomas
This is <STRING1> is tall Some text
Some other text Some other text
Some other text
This is <STRING1>, he is tall.
This is George, he is tall .
This is Peter, he is tall.
This is Thomas, he is tall.
Some other text

I'm not very good at VBA scripting but so far searching in the net I found a script where I can add new row which would copy the formula from the row above it - I'm not sure if this can be helpful at all Credit is to a person named Mark Hill <[email protected]>.

Please Login or Register  to view this content.

Hi dihris,

Try this:

Please Login or Register  to view this content.

Hello xladept,

I have tested your code and it does not seem to be doing what I’m look to do. I tested it in the attached test.xlsx file and the result is that the code would replace every <STRING1> from column A with corresponding value from cell located in the same row in column C.
What I’m trying to do is the following:
I have data in column A which contains cells where <STRING1> can be found. I need to be able to insert new cells under every cell in Column A that contains <STRING1> where each new cell will have the result from substituting <STRING1> with each new value found in Column C.
I think the bellow example will clarify what I’m trying to do.

Column A
1 My name is <STRING1> and I’m strong.
2 Some Text
3 Some other text
4 This is <STRING1> he is tall.
5 Some other text

Column C
1 George
2 Peter
3 Thomas

Result after inserting new cells with substituted <STRING1> with all values found under Column C

Column A
1 My name is <STRING1> and I’m strong.
2 My name is George and I’m strong.
3 My name is Peter and I’m strong.
4 My name is Thomas and I’m strong.
5 Some Text
6 Some other text
7 This is <STRING1>, he is tall.
8 This is George, he is tall.
9 This is Peter, he is tall.
10 This is Thomas, he is tall.
11 Some other text

As you can see 3 new cells (A2, A3, A4 and A7, A8, A9) have been inserted for each cell where <STRING1> can be found and these new cells contain the values from column C.

Thank you for your time.

Try this

Please Login or Register  to view this content.

dihris, welcome to the forum.

2 suggestions for you.

1. use code tags when you post code (see my notes below)
2. To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Try this:

Please Login or Register  to view this content.

You're welcome! - Thanks for the rep!