Search Text and Add a Blank Line
 

Hi All,

New to the forum and have been searching (here and Google) for an answer with no luck. I am trying to automate the process of searching for 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 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.

** ** ** ** **

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

Search Text and Add a Blank Line
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Worked perfectly. Thanks a ton!

Where does one learn how to do this kind of work?