View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Héctor Miguel Héctor Miguel is offline
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.