 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004importing text file into excel 2000
I have a text file with a list of addresses on multiple lines. Each address is three lines, name, street address, town/state/zip. Each record is divided by a blank space. I want to import each of these into excel 2000, with each line being a field in the record. It doesn't appear I can do this with the import wizard. Ultimately I want to use this spreadsheet for a mail merge word function. Any thoughts on the best way to do this? Thanks! i meant to say each record is divided by a blank LINE, not space. ie: Carlson GMAC Real Estate 28 Green St Newburyport, MA 01950-2650 Phone: (978) 462-8155 Century 21 Heritage Realty Associates 10 Prince Pl Newburyport, MA 01950-2612 Phone: (978) 463-3100 Dewolfe Companies Inc 61 State St Newburyport, MA 01950-6612 Phone: (978) 465-1927 >I have a text file with a list of addresses on multiple lines. Each >address is three lines, name, street address, town/state/zip. [quoted text clipped - 4 lines] > Ultimately I want to use this spreadsheet for a mail merge word function. > Any thoughts on the best way to do this? Thanks! sigh..... outlook express reformatted my text, there is no blank line between the name, street address and town address >i meant to say each record is divided by a blank LINE, not space. > [quoted text clipped - 26 lines] >> Ultimately I want to use this spreadsheet for a mail merge word function. >> Any thoughts on the best way to do this? Thanks! Rob In that case, just enter 3 in the InputBox using my code. Gord >sigh..... outlook express reformatted my text, there is no blank line >between the name, street address and town address [quoted text clipped - 29 lines] >>> Ultimately I want to use this spreadsheet for a mail merge word function. >>> Any thoughts on the best way to do this? Thanks! thanks, worked great! > Rob > [quoted text clipped - 37 lines] >>>> function. >>>> Any thoughts on the best way to do this? Thanks! Thanks for the feedback Rob. Gord >thanks, worked great! > [quoted text clipped - 39 lines] >>>>> function. >>>>> Any thoughts on the best way to do this? Thanks! Rob If data is consistent in groups of three with a blank in between as your example shows, this macro will do the trick. When the inputbox comes up, enter 4 to accommodate the blank rows. Sub ColtoRows() Dim rng As Range Dim i As Long Dim j As Long Set rng = Cells(Rows.Count, 1).End(xlUp) j = 1 nocols = InputBox("Enter Number of Columns Desired") If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub For i = 1 To rng.Row Step nocols Cells(j, "A").Resize(1, nocols).Value = _ Application.Transpose(Cells(i, "A").Resize(nocols, 1)) j = j + 1 Next Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents End Sub If unfamiliar with macros, visit David McRitchie's "getting started with VBA" site at http://www.mvps.org/dmcritchie/excel/getstarted.htm Gord Dibben Excel MVP >i meant to say each record is divided by a blank LINE, not space. > [quoted text clipped - 26 lines] >> Ultimately I want to use this spreadsheet for a mail merge word function. >> Any thoughts on the best way to do this? Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.