Breakout text to new rows

  • Thread starter Thread starter dcnc
  • Start date Start date
D

dcnc

Hello all.....

I have founds this board to be very helpful in the past but this is one
question I cannot seem to find.

Bellow is a Screenshot of what I am working on. If you see in cells G
& H 104 there are muliple lines of data. What I was hoping is that
there is a function that will allow me to send each one of those values
to its own cell. So rather than have it all in G&H 104 it would now be
G&H 104,105,106 and 107.

Anyone know if this is possible. And if you were wondering, I cant
change the way this data is popluated into the excel sheet. It's a dump
from a Lotus Notes DB and I just recieve the report . I do not pull it
myself.

Any help or suggestions would be great! Thanks!

[image: http://www.dc-nc.com/images/rows.gif]
 
Use data / text to column / delimited

use the linefeed as a 'breaker'. this is chr(10)
to enter the linefeed in the 'other' box :
hold down the alt key and type 0010 on the numpad....

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
ok but is there anyway to do it so its Text to Rows?

rather than have the data spread out to the right. I want the data t
fall by row
 
You'd overwrite what is below.., and NO there's no standard way to do
this.

When you'd write your own VBA procedure it's very important to know
if you ONLY want to split on 1 column, or also split on other columns,
in the latter case thinkgs MAY start to get real messy...




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Try following macro

it will check each row on activesheet

determine nr of rows to insert
then go thru the cells again and split m...

uses split function which will NOT work in excel97

Not truly tested but it seems to work :)


have fun...


Option Explicit

Sub SplitToRows()
Dim i%, n%, m%, r&, v, c As Range

With ActiveSheet.UsedRange
For r = .Rows.Count To 1 Step -1
'determine nr of rows
m = 0
For Each c In .Rows(r).Cells
If InStr(c.Text, vbLf) > 0 Then
i = 0
n = 0
Do
i = InStr(i + 1, c.Text, vbLf)
If i > 0 Then n = n + 1
Loop While i > 0
m = IIf(n > m, n, m)
End If
Next

If m > 0 Then
.Rows(r + 1).Resize(m).EntireRow.Insert
For Each c In .Rows(r).Cells
If InStr(c.Text, vbLf) > 0 Then
v = Application.Transpose(Split(c.Text, vbLf))
c.Resize(UBound(v)).Value = v
End If
Next
End If

Next
End With

End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
dnc

Select the cell with the multiple lines and four more below(insert rows so you
don't overwrite).

In formula bar highlight the text and copy.

Right-click and Paste.

The four lines will be placed into separate rows.

Gord Dibben Excel MVP
 
Do text to column, then copy, paste special, transpose -- this will change rows
to columns


: ok but is there anyway to do it so its Text to Rows?
:
: rather than have the data spread out to the right. I want the data to
: fall by rows
:
:
: ---
: Message posted
:
 
Back
Top