Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by Claus Busch View Post
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?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatentate text and insert blank line Bethany Excel Worksheet Functions 5 November 10th 09 07:38 PM
Search for specific text within last non blank cell in a range Bevo Excel Worksheet Functions 2 September 5th 09 08:15 AM
Wrapping text leaves blank line when printing [email protected] Excel Discussion (Misc queries) 1 November 19th 08 05:06 PM
Cell set to wrap text and blank line -- fix? Fred Holmes Excel Discussion (Misc queries) 0 January 25th 05 08:10 PM
Blank Line in Header Text S. S. Excel Programming 1 December 2nd 03 08:45 PM


All times are GMT +1. The time now is 01:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"