A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to split wrapped text in a single cell into multiple lines



 
 
Thread Tools Display Modes
  #1  
Old February 27th 08, 03:45 AM posted to microsoft.public.excel.misc
Dr Fumanchu
external usenet poster
 
Posts: 2
Default How to split wrapped text in a single cell into multiple lines

I really need help on "How to split wrapped text in a single cell into
multiple lines" e.g

Text (wrapped) appear in one cell

Prepare and allow "Rentokil" anti termite treatment as described to sides
and bottom of foundation pit and basement beams trenches and all top surfaces
of lean concrete to basement floor area including apron and verandah.
Provide 5 years warranty.

Text splitted into four different cells

Prepare and allow "Rentokil" anti termite treatment as described to sides and

bottom of foundation pit and basement beams trenches and all top surfaces of
lean

concrete to basement floor area including apron and verandah. Provide 5
years

warranty.
Ads
  #2  
Old February 27th 08, 07:42 AM posted to microsoft.public.excel.misc
Héctor Miguel
external usenet poster
 
Posts: 434
Default How to split wrapped text in a single cell into multiple lines

hi, !

with the given example, this procedure splits the 4 lines as required:

assuming wrapped text in A2...
[A3] =substitute(a2,char(10),"")
[A4] =substitute(a3,b3&" ","")
[A5] (copy-down the above)

=== array formula CSE ===
[B3] =left(a3,lookup(84,if(mid(a3,row(indirect("1:255") ),1)=" ",row(indirect("1:255"))))-1)
-> copy-down up to [B5]
[B6] =substitute(a5,b5&" ","")

but "something tells me" that's not the elegant way you look for... so,
you might need to write some code to "split" every 84th space-character in wrapped cells -?-

hth,
hector.

__ origina post __
> I really need help on "How to split wrapped text in a single cell into multiple lines" e.g
> Text (wrapped) appear in one cell
>
> Prepare and allow "Rentokil" anti termite treatment as described to sides
> and bottom of foundation pit and basement beams trenches and all top surfaces
> of lean concrete to basement floor area including apron and verandah.
> Provide 5 years warranty.
>
> Text splitted into four different cells
>
> Prepare and allow "Rentokil" anti termite treatment as described to sides and
> bottom of foundation pit and basement beams trenches and all top surfaces of lean
> concrete to basement floor area including apron and verandah. Provide 5 years
> warranty.



  #3  
Old February 27th 08, 01:30 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default How to split wrapped text in a single cell into multiple lines

On Tue, 26 Feb 2008 19:45:01 -0800, Dr Fumanchu
> wrote:

>I really need help on "How to split wrapped text in a single cell into
>multiple lines" e.g
>
>Text (wrapped) appear in one cell
>
>Prepare and allow "Rentokil" anti termite treatment as described to sides
>and bottom of foundation pit and basement beams trenches and all top surfaces
>of lean concrete to basement floor area including apron and verandah.
>Provide 5 years warranty.
>


A little better description of your data and requirements would be useful.

For example. Is the text actually "wrapped" with no included linefeeds or
carriage returns?

Will you always want the last sentence on its own line?

For example, if the wrapped text is really caused by embedded CR/LF, then you
can select the text in the formula bar; COPY it; SELECT your target cell; PASTE
and the lines will go into individual rows.

Or it can be done with a VBA macro, but if you require the last sentence in its
own cell, the routine will be different.

Here is a Macro that will split the contents of cells into the rows below. As
written it will place the split data into the rows below. Read the comments to
see how to change it to replace the original data with the first line.

If you want to have the last sentence treated separately, you will need more
code.

Note that the routine requires that you set a reference (see Tools/References
from the VBA Menu Bar) to Microsoft VBScript Regular Expressions 5.5

To enter this macro, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Then set the reference (Tools/References) as mentioned above.

To use this, <alt-F8> opens the macro dialog box. Select "ww" and <RUN>.
Select one or more cells in the same row to be split. Enter your preferred
line length.

The split will be at spaces between words.

================================================== =
Option Explicit
Sub ww()
'requires reference to Microsoft VBScript Regular Expressions 5.5
'Wraps at W characters, but will allow overflow if a line is longer than W
Dim re As RegExp, mc As MatchCollection, m As Match
Dim Str As String
Dim W As Long
Dim rSrc As Range, c As Range
Dim mBox As Long
Dim i As Long
'with offset as 1, split data will be below original data
'with offset = 0, split data will replace original data
Const lDestOffset As Long = 1

Set rSrc = Selection
If rSrc.Rows.Count <> 1 Then
MsgBox ("You may only select" & vbLf & " Data in One (1) Row")
Exit Sub
End If
Set re = New RegExp
re.Global = True
W = InputBox("Maximum characters in a Line: ", , 79)
If W < 1 Then W = 79
For Each c In rSrc
Str = c.Value
'remove all line feeds and nbsp
re.Pattern = "[\xA0\r\n]"
Str = re.Replace(Str, " ")
re.Pattern = "\s?((\S[\s\S]{1," & W - 2 & "}\S)|(\S[\s\S]{" _
& W - 1 & ",}?\S))(\s|$)"
If re.Test(Str) = True Then
Set mc = re.Execute(Str)
'see if there is enough room
i = lDestOffset + 1
Do Until i > mc.Count + lDestOffset
If Len(c(i, 1)) <> 0 Then
mBox = MsgBox("Data in " & c(i, 1).Address & " will be erased if you
contine", vbOKCancel)
If mBox = vbCancel Then Exit Sub
End If
i = i + 1
Loop

i = lDestOffset
For Each m In mc
c.Offset(i, 0).Value = m.SubMatches(0)
i = i + 1
Next m
End If
Next c
Set re = Nothing
End Sub
==================================
--ron
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrapped text stops wrapping after 8 lines in MS Exel. How to fix? Paul Excel Discussion (Misc queries) 3 May 16th 07 11:14 PM
Duplicate Text within single cell. How to identify & split TeRex82 Excel Worksheet Functions 4 June 21st 06 08:30 AM
split text in one cell into multiple cells without breaking the wo Prashant Excel Worksheet Functions 3 March 6th 06 08:48 AM
How can I get excel to show ALL lines of wrapped text? bmg Excel Worksheet Functions 0 December 27th 05 05:39 PM
How to adjust spacing between lines of wrapped text ? SUZL Excel Discussion (Misc queries) 1 December 15th 05 10:51 PM


All times are GMT +1. The time now is 03:25 AM.


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