Search Text and Add a Blank Line

[kojack]

Hi All,

New to the forum and have been searching (here and Google) for an answe
with no luck. I am trying to automate the process of searching fo
specific text in a cell and adding a blank line before that text.
Following is an example...

Cells contain...
Text:This is the first bit of text which is different for all cells
NewText: This is the text that I am manually placing on a new lin
beginning with 'New Text'.

I am trying to get the cells to look like...
Text:This is the first bit of text which is different for all cells.

NewText: This is the text that I am manually placing on a new line.

** ** ** ** **

I have attempted creating a Macro and adjusting the code but hav
failed. Any ideas/help would be greatly appreciated

 

[Claus Busch]

Hi,

Am Mon, 12 Nov 2012 16:47:11 +0000 schrieb kojack:

Cells contain...
Text:This is the first bit of text which is different for all cells.
NewText: This is the text that I am manually placing on a new line
beginning with 'New Text'.

I am trying to get the cells to look like...
Text:This is the first bit of text which is different for all cells.

NewText: This is the text that I am manually placing on a new line.

try following code (modify range to suit):

Sub WrappedText()
Dim Start As Integer
Dim rngC As Range

For Each rngC In Range("A1:A100")
Start = InStr(1, rngC, "NewText")
rngC = Left(rngC, Start - 1) & vbNewLine & _
Mid(rngC, Start, 200)
Next
Range("A1:A100").EntireRow.AutoFit
End Sub

or do it in a helper column with following formula:
=LEFT(A1,FIND("NewText",A1)-1)&CHAR(10)&MID(A1,FIND("NewText",A1),200)
Then copy the helper column and paste special paste values


Regards
Claus Busch

 

[kojack]

Hi,

Am Mon, 12 Nov 2012 16:47:11 +0000 schrieb kojack:
-

try following code (modify range to suit):

Sub WrappedText()
Dim Start As Integer
Dim rngC As Range

For Each rngC In Range("A1:A100")
Start = InStr(1, rngC, "NewText")
rngC = Left(rngC, Start - 1) & vbNewLine & _
Mid(rngC, Start, 200)
Next
Range("A1:A100").EntireRow.AutoFit
End Sub

or do it in a helper column with following formula:
=LEFT(A1,FIND("NewText",A1)-1)&CHAR(10)&MID(A1,FIND("NewText",A1),200)
Then copy the helper column and paste special paste values


Regards
Claus Busch

Worked perfectly. Thanks a ton!

Where does one learn how to do this kind of work