Converting Vertical Records to Horizontal

**Wibs** · 01-29-2010, 11:24 AM

Not sure if I have asked this question correctly, so let me explain:

I would like to convert data that I have been given, that is in this kind of format:

Forename: John
Lastname: Smith
Employee #: 62537

Forename: Fred
Lastname: Bloggs
Employee#: 63244

Forename: Pete
Lastname: Brown
Employee#: 61123

etc

Into:

Forename Lastname Employee#
John Smith 62537
Fred Bloggs 63244
Pete Brown 61123

ie the three pieces of information in row format, into a regular Excel column format

Should I get rid of all the colons after each heading first, and maybe the spaces between the records?

Any help appreciated

Wibs

**NBVC** · 01-29-2010, 11:35 AM

Assuming the original list is in A1:A1000, and If you put the headings, say in B1:D1

Forename, Lastname and Employee# (as they appear in the original list)

Then in B2 enter:

=TRIM(MID(INDEX($A$1:$A$1000,4*(ROWS($A$1:$A1)-1)+1),LEN(B$1)+3,255))

Change the $A$1:$A$1000 to suit the entire range of your original list.

copy that formula across to C1 and D1...

then in C1, change the first $A$1 to $A$2

and in D1, change the same $A$1 to $A$3

Then copy all 3 formulas down as far as you need.

After done, you can copy all 3 columns, then Paste Special >> Values over them.. so that you can delete the original list if desired...

**Richard Buttrey** · 01-29-2010, 11:43 AM

Hi,

No leave the spaces we can use those. Assuming your list starts in A1 use the following formulae

C1:
=RIGHT(OFFSET(A$1,ROW()*4-4,0),LEN(OFFSET(A$1,ROW()*4-4,0))-SEARCH(" ",OFFSET(A$1,ROW()*4-4,0)))

D1:
=RIGHT(OFFSET($A$1,ROW()*4-3,0),LEN(OFFSET($A$1,ROW()*4-3,0))-SEARCH(" ",OFFSET($A$1,ROW()*4-3,0)))

E1:
=RIGHT(OFFSET($A$1,ROW()*4-2,0),LEN(OFFSET($A$1,ROW()*4-2,0))-SEARCH(" ",OFFSET($A$1,ROW()*4-2,0))-3)

then copy this down for 1/4 of the total rows in column A

HTH