#1 (permalink)

Hi,

Thanks in advance for your help.

I need an Excel macro (I guess in VB) to perform the following function.

Sample Text: "I like to eat (%s) when I am bored." in cell A5 and "I sometimes like to drink (%p) when I am thirsty." in cell A9

This sample text would be in a user-specified cell.

Ideally the following would happen:

1) Look through the cells in the specified row for strings within a cell that contain a set of characters that match "(%s)" or "(%p)."
2) Select/highlight text in string preceding the "(" so in this example "I like to eat "
3) Copy this text
4) Create a new row right below (row B) and paste the text in a cell right below where the original text was (cell B5)
5) Repeat steps 2-4 for additional cells in the row that contain that text so it would also find "I sometimes like to drink " (cell B9)
6) Select/highlight text in string after the (parenthesis and a space) ") " so in this example "when I am bored."
7) Copy this text
8) Create a new row below the other one just created (row C) and paste the text in a cell below where the original text was (cell C5)
9) Repeat steps 6-8 for additional cells in the row that contain that text so in this example "when I am thirsty." (cell C9)
10) Clear the clipboard

Hope this makes sense. Will reply immediately to requests for more information.

Thanks again