Text Manipulation
Posted by Matt on July 17, 2001 3:07 PM
I want to use excel to manipulate data in a text file. One record consists of 4 lines of text data. If I paste it into Excel, I need the data to be in one line before I use text to columns to break down the data. Can that be done in Excel?
Posted by Mark W. on July 17, 2001 3:55 PM
Sure. Suppose that A1:A8 contains...
{"Line 1"
;"Line 2"
;"Line 3"
;"Line 4"
;"Line 5"
;"Line 6"
;"Line 7"
;"Line 8"}
Enter the formula, =A1&" "&A2&" "&A3&" "&A4, into
cell B1, select cells B1:B4, and the double-click
the fill handle -- the little black square in the
lower right-hand corner of the selection.
You'll notice that cell B1 and B8 now contain the
combination of 4 rows each. Do a Copy/Paste Special
values on column B to preserve these values,
Sort column B, and Delete the rows that have no
value in column B.
