Add string of text to a specific point in every cells

Hey everyone.

Say I have a column A with 100 cells containing a paragraph of text, lets suppose something like this:

Remember when you were young, you shone like the sun.
Now there's a look in your eyes, like black holes in the sky.

Say I want to ad in every cells a string of text, a variable that will change on every addition, between first and second sentences, like this:

Remember when you were young, you shone like the sun.
string
Now there's a look in your eyes, like black holes in the sky.

I suppose I have to search for "now there's" text and add the value of the string before it.
How can I solve this with VBA?

re: between first and second sentences
Remembering my primary school teacher who said something along the lines of A sentence starts with a capital letter and ends with a full stop (a period if you're in the States) then it might work if you look for the first full stop, and replace it with a full stop and the string you want to add. Your example shows a new line too, so I've thrown that in (vblf):

Code:

---

Sub blah()
myStringToAdd = "string"
For Each cll In Range("A1:A100").Cells
  cll.Value = Replace(cll.Value, ".", "." & vbLf & myStringToAdd, , 1, vbTextCompare)
Next cll
End Sub

---

or you could incorporate that new line within the string variable:

Code:

---

Sub blah2()
myStringToAdd = vbLf & "string"
For Each cll In Range("A1:A100").Cells
  cll.Value = Replace(cll.Value, ".", "." & myStringToAdd, , 1, vbTextCompare)
Next cll
End Sub

---

Quote:

---

Originally Posted by kiltro

Say I have a column A with 100 cells containing a paragraph of text, lets suppose something like this:

Remember when you were young, you shone like the sun.
Now there's a look in your eyes, like black holes in the sky.

Say I want to ad in every cells a string of text, a variable that will change on every addition, between first and second sentences, like this:

---

Do you mean 'sentence' ( starts with a capital letter, and ends with a period, question mark, etc. )

OR

Just insert a new second line between the first and second?

Looking at your example, it appears that a vbLF (char(10) is after the 'sun.'

So you could have something like

As I was walking<LF>
down the street<LF>
etc.

If that's the case, you'd want to search for the first Chr(10) and insert 'string' after that giving

As I was walking<LF>
string<LF>
down the street<LF>
etc.