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.

 How to split wrapped text in a single cell into multiple lines
 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

#1
February 27th 08, 03:45 AM posted to microsoft.public.excel.misc
 Dr Fumanchu external usenet poster Posts: 2
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.
#2
February 27th 08, 07:42 AM posted to microsoft.public.excel.misc
 Héctor Miguel external usenet poster Posts: 434
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
February 27th 08, 01:30 PM posted to microsoft.public.excel.misc
 Ron Rosenfeld external usenet poster Posts: 5,651
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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.