Hi,
I have a data feed which merges all the information into one cell. I can
divide the data by using text column but then I will have no ideas which
information does the lines belong to. For example column A contains drug
number and B has the 10 line information I need and there are totally 20
entries. ( The original files are 20 rows)How can I divide this information
into 200 rows and still keep the information match to each other in each line?

Thanks!

CC

This code, possibly with minor change, should do it for you.

Sub SplitLines()
Dim txtTemp1 As String
Dim RowOffset As Integer
Dim LineBreak As String
Dim Adjustment As Integer

LineBreak = Chr(10) ' change as needed e.g. = Chr(10) & Chr(12)
Worksheets("Sheet1test").Select ' change as needed

Adjustment = Len(LineBreak) - 1
Range("A1").Select
Do Until IsEmpty(ActiveCell)
txtTemp1 = ActiveCell.Offset(0, 1).Value & LineBreak
Do While InStr(txtTemp1, LineBreak)
ActiveCell.Offset(0, 1) = Left(txtTemp1, InStr(txtTemp1, LineBreak)
- 1)
'next is all one line
txtTemp1 = Right(txtTemp1, Len(txtTemp1) - (InStr(txtTemp1,
LineBreak) + Adjustment))
'begin another line
If Len(txtTemp1) (Adjustment + 1) Then
ActiveCell.Offset(1, 0).Activate
Selection.EntireRow.Insert
Else
txtTemp1 = "" ' remove that last LineBreak we added
End If
Loop
ActiveCell.Offset(1, 0).Activate
Loop
End Sub


The difficult part is determining what character is marking the end of each
of the ten lines of information in the information in column B. This code
presumes it is a character with the value of 10 (a line feed). You need to
find out what that character is (it may even be two characters).

To find out what they are, make a copy copy one of a sheet with some data
that you can safely destroy. Delete all the information except what is in
cell B1
in cell A1 enter this formula:
=Code(Mid(B1,Row(B1),1))
and extend that code down the sheet. It will show you the ASCII code for
each character in the text in B1 in turn. Look where you know line breaks
should occur and see what character(s) is/are after the last visible normal
character. It will probably be either 10 or 12 or possibly both in a row.

When you determine what it is, then go to the code and where LineBreak is
defined, change that to match what you've found. If you find just a 10, then
the code will work as is. If it is a 12, change
LineBreak = Chr(10)
to
LineBreak = Chr(12)
if it is two characters as a pair, like 10 12 then change that code to
LineBreak = Chr(10) & Chr(12)
make sure you get the sequence right.

Also, change the sheet name in the code to the name of the sheet in your
workbook.


"Chia" wrote:

Hi,
I have a data feed which merges all the information into one cell. I can
divide the data by using text column but then I will have no ideas which
information does the lines belong to. For example column A contains drug
number and B has the 10 line information I need and there are totally 20
entries. ( The original files are 20 rows)How can I divide this information
into 200 rows and still keep the information match to each other in each line?

Thanks!

CC