Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Break cell into multiple lines by line break

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default Break cell into multiple lines by line break

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
display multiple lines of text within a merged cell automatically sssizemore Excel Worksheet Functions 1 June 15th 05 02:30 AM
HOW CAN I TYPE MULTIPLE LINES IN A CELL brewster Setting up and Configuration of Excel 1 February 11th 05 12:46 PM
how do I enter multiple lines in a cell in Excel asowles Excel Discussion (Misc queries) 2 February 3rd 05 08:47 PM


All times are GMT +1. The time now is 04:06 PM.

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

About Us

"It's about Microsoft Excel"