Convert a Column List Into a Row of Headers

  • Thread starter Thread starter Frosty
  • Start date Start date
F

Frosty

Hello Advance Excel Users:

I exported data from a program that exports in the form of something
called "Tabulated Text." When I open this text file, it show the
following (information changed obviously):

First: Marty
Last: Feldman
Address: 1234 Boxers St.
City: Anywhere
State: AW
Zip: 01010
Company: Boulder Lifting
Number of Refrigerators: 5
Whatelsetoputhere: 5 Thank you


First: Glad
Last: Bag
Address: 2312 Any Street
City: Overthere
State: OV
Zip: 22220
Company: Flattener
Number of Refrigerators: Seventeen
Whatelsetoputhere: 20 No Thanks

(Repeats same pattern for the next 500 entries)

Importing into excel makes this list as one long column, with no
headers across in a row. How can I tell excel to take the first word
of the first line before the ":" and make it a heading.

So instead of this:

First:
Last:
Address:
City:
State:
Zip:
Company:

How do I turn it into this:

First | Last | Address | City | State | Zip | Company

If you have any other ideas on pre-formatting this such as using a text
editor, like BBEdit or Grep formulas, or Excel's find and replace
feature, please help.

There was one article that a search engine found that I thought might
talk about this, but it is missing or gone. The title of the forum
article was Convert Column To Row With Variable Data.

Again, any help would help. Even mentioning other programs other than
excel that can juggle text around.

Thank you for your time

Frosty F.
 
Bit of manual manipulation will crack this for you.

First import the data so that you have it in a single column. Select the entire
range of data and do Data / Text To Columns / Delimited / In the other filed put
a : and check it. Next, hit next and then with the first column highlighted
click on the 'Do not import column' option.

This should take out all the headings.

Now assuming your first piece of data is in A1, in B1 put the following
formula:-

=OFFSET($A$1,ROW()*9-(11-COLUMN(B1)),0)

Copy it and paste it to B1:J500

Next, assuming you have what you want excluding headings, select B2:J500, do
edit / copy, then edit / paste special / values, insert your headers, delete Col
A and you are done.

If by any chance you have a blank line between each entry as per your example,
such that each set of daat takes up 10 rows, then you need to change the formula
to

=OFFSET($A$1,ROW()*10-(12-COLUMN(B1)),0)
 
Back
Top