How can I convert a text file to a Excel table?

[Keith Ko]

Dear Folks,

I got a plain text file which is a contact list and formatted as below:

Last Name : Chan
First Name : Tai Man
Address : xxx...
Email Address : T...@abc.com

Last Name : Lee
First Name : Siu Ming
Address : zzz...
Email Address : pe...@xyz.com
.
.
.

How can I convert it into a Excel table? Any help are greatly appreciated.

Thanks,
Keith

[acw]

Keith

Assuming that the data is in column A starting from cell
A1.

1) Use Data, Text to Columns, Delimited, Other (with a
colon) to parse the data into columns A and B.
2) Select the headings from A1 to A4 and paste special,
transpose into cells E1 - H1.
3) In cell E1 put in the formula
=INDIRECT("B"&ROW()*5-(14-COLUMN()))
and copy across to H2. Copy this down as required to
cover all the data.

Tony

>-----Original Message-----
>Dear Folks,
>
>I got a plain text file which is a contact list and
formatted as below:
>
>Last Name : Chan
>First Name : Tai Man
>Address : xxx...
>Email Address : T...@abc.com
>
>Last Name : Lee
>First Name : Siu Ming
>Address : zzz...
>Email Address : pe...@xyz.com
>..

>..
>..

>
>How can I convert it into a Excel table? Any help are
greatly appreciated.
>
>Thanks,
>Keith
>
>

>.
>

[David McRitchie]

Hi Keith,
For that you would need macro, since your data does not have
a fixed number of Infos tags on the left you would need a macro.
Hope this is what you want, the zip code states, and zip codes
are not going to line up, because of differences in your output
used as input, but don't know what is important to you.

If you need instructions for installing/using a macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

The coding was modified from
http://www.mvps.org/dmcritchie/excel/code/naddrdb.txt
and is documented for more convential usage in
http://www.mvps.org/dmcritchie/excel/snakecol.htm
where the left column would have unique names such as
Infos1:, infos2:, infos3:, etc

If your first set does not have all of the items you may have
to move a column around in your output worksheet.

Before running macro:
Use text to columns or the input wizard to separate data
into two columns (tags on left, data on right)
based on the colon delimiter. You may
want to run TRIMALL macro from my
http://www.mvps.org/dmcritchie/excel/code/join.htm#trimall
page to remove spaces from left and right of each cell.

Option Explicit
Public Sub NAddrDB_modified()
'Convert 1-Up Name and Address labels to Spread Sheet format.
'David McRitchie http://www.mvps.org/dmcritchie/excel/code/naddrdb.txt
' 2002-05-05 NAddrDB macro work with names as arg in A and value in B
' will accept Arguments in any order within blank row delimited ranges
' temporary modification for Keith Ko
Dim nCol As Long, nRow As Long, cRow As Long, lastrow As Long
Dim insureCol As Long
Dim wsSource As Worksheet, wsNew As Worksheet
Dim lastcell As Range
nCol = 0
nRow = 2
Dim Desc(50) As Variant
Dim Dsub As Long
Dsub = 0
Dim I As Long
Set lastcell = Cells.SpecialCells(xlLastCell)
lastrow = lastcell.Row + 1 'adjustment to help with insureCol
Set wsSource = ActiveSheet
Set wsNew = Worksheets.Add
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For cRow = 1 To lastrow
If Trim(wsSource.Cells(cRow, 1).Value) = "" Then
If nCol <> 0 Then nRow = nRow + 1
nCol = 0
Else
nCol = 1 'not zero
For I = 1 To Dsub
If wsSource.Cells(cRow, 1) = Desc(I) Then
'-- check for Infos added modification for Keith Ko, Jul 22, 2003, misc
If Trim(wsSource.Cells(cRow, 1)) = "Infos" Then
If wsNew.Cells(nRow, I).Value <> "" Then GoTo nxt_I
End If
wsNew.Cells(nRow, I).Value = wsSource.Cells(cRow, 2).Value
GoTo nextcrow
End If
nxt_I:
Next I
Dsub = Dsub + 1
wsNew.Cells(1, Dsub) = wsSource.Cells(cRow, 1).Value
Desc(Dsub) = wsSource.Cells(cRow, 1).Value
wsNew.Cells(nRow, Dsub).Value = wsSource.Cells(cRow, 2).Value
wsNew.Cells(nRow, Dsub).NumberFormat = wsSource.Cells(cRow, 2).NumberFormat
GoTo nextcrow
End If
nextcrow:
Next cRow
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True 'place at end when debugged
Application.DisplayAlerts = True
End Sub

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Keith Ko" <~@~.com> wrote in message news:OR5Hi7BU...@TK2MSFTNGP12.phx.gbl...
> Tony,
> Many thanks~~~
>
> But, may I use your method to apply on attached file?
>
> Thanks,
> Keith
>
>
> "acw" <ac_wa...@yahoo.com> wrote in message
> news:07ad01c35008$3001f160$a301...@phx.gbl...