Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
Where does one learn how to do this kind of work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatentate text and insert blank line | Excel Worksheet Functions | |||
Search for specific text within last non blank cell in a range | Excel Worksheet Functions | |||
Wrapping text leaves blank line when printing | Excel Discussion (Misc queries) | |||
Cell set to wrap text and blank line -- fix? | Excel Discussion (Misc queries) | |||
Blank Line in Header Text | Excel Programming |