How To Import Text File With No Delimiters?

I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours. 

P.S. Please keep it simple if possible, Excel is all new to me.

TIA
0
sam9775 (1)
2/8/2005 6:28:40 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
513 Views

Similar Articles

[PageSpeed] 34

Hi Sam,
You did not indicate examples of the 3 to 5 line of data separated by a blank
row as to where they should end up.  Nevertheless, you should find
the answer at:

 SNAKECOLS,  How to snake columns to use fewer pages
  http://www.mvps.org/dmcritchie/excel/snakecol.htm
 with specific answer in the following macro:

 naddr2ss (macro code).  Convert 1-up labels to Spreadsheet format.
 The 1-Up labels must be separated by at least one blank line to start
 a new spreadsheet row.
  http://www.mvps.org/dmcritchie/excel/naddr2ss.txt

If you do not get 5 columns of data you can move the cells
around on that row to match you usage before continuing
to process the next destination row.
---
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

"Sam" <sam@noemail.com> wrote in message news:5vmg011e52qmdc9f3ru5dca26brtn0erko@4ax.com...
> I have a text file with over 2,000 names that look like the following:
>
> Mr. Benoit Smith
> 120 Adam Street
> Dorchester, MA 02124
>
> Marcus Darbouze
> 1553 Blue Hill Avenue
> Boston, MA 02126
>
> American Public Health Initiatives
> 10 Fairway Street
> Mattapan, MA 02126
>
> Most consist of 3 lines some as many as 5. The text file has no
> delimiters so that when I import/open it in Excel it becomes just one
> very long column. I need to convert it or parse it into any other
> database friendly importable format i.e., .dbf, .xls, .csv etc. What I
> need is to convert it into a table with up to five header rows:
>
> head1 head2 head3 head4 head5
>
> Please help, I've been at it for way too many hours.
>
> P.S. Please keep it simple if possible, Excel is all new to me.
>
> TIA


0
dmcritchie (2586)
2/8/2005 5:54:18 PM
Reply:

Similar Artilces:

sorting text & numbers
I have a large list that I want sorted like this: Ward 1 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 Ward 10 When I sort it, it says: Ward 1 Ward 10 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 If I just take out the words Ward and Wards, it makes some of the numbers into dates, and does something else to some other cells that returns a big number. Anything I can do? -- billjr ------------------------------------------------------------------------ billjr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25927 View this thread: http://www.excelforum.com/sh...

changing the delimiter while saving as txt file
Dear Experts, is there a way to change the delimiter to # instead of tab characters while saving the excel file to .txt file. Thanks in advance Hi see: http://www.cpearson.com/excel/imptext.htm -- Regards Frank Kabel Frankfurt, Germany "raj" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:845d01c48520$92fc1c20$a401280a@phx.gbl... > Dear Experts, > > is there a way to change the delimiter to # instead of tab > characters while saving the excel file to .txt file. > > Thanks in advance And some more links... Maybe you can steal some cod...

need to find the file that Outlook emails are stored in
there is one file that contains all the outlook email and information. i need help to find it. also, how can i view Hidden Files? thanks arthur Open Windows Explorer and select Tools | Folder Options. Select Show Hidden Files and Folders and uncheck the option to hide protected operating system files. OK out and open the search panel (f3). If you are using Windows XP, you need to configure the search to look for hidden items. If all goes right, you should find your *.pst file(s) under \documents and settings\<userid>\local settings\application data\microsoft\outlook. -- Neo [...

Graphics and attached file in e-mail activity / quick campaigns e-mails
Hi. We have migrated from Siebel to MS CRM 4.0. We are happy with the decision, but very disappointed by the default options on e-mails, especially e-mails in quick campaigns: - Graphics cannot be inserted, e.g. a company logo - A file, e.g. a PDF, cannot be attached Can anyone help with a workaround, or any tips on a third party add-on / tool that can expand the functionality in CRM? We have tried deferent workarounds: - Export the targets e-mail addresses to Excel. Design the e-mail in Word, and send the e-mails from Word via the Excel list merge. That solves the graphics issue, but...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Can't open many XLS files at once
I have Excel 2000 and Win XP pro. I used to be able to highlight many XLS files in Explorer, right click, choose open and all the highlighted files would open in Excel with the different xls files on my task bar. I can still do this on my W98 computer, but I can only open one file at a time on my Win XP computer. If I double click on a 2nd XLS file the 2nd XLS file opens and the first file closes. I tried the run excel.exe /regserv fix but this did not solve the problem. Does anyone know how to fix this for Win XP? Thanks Dennis Hi maybe: Tools - Options - General and uncheck 'Ig...

Access corrupt files DBF?
Access corrupt files DBF? "Frank Dulk" <fdulk@bol.com.br> wrote: >Access corrupt files DBF? What is your question? I see you're from Brazil. Have you asked your question in the Portuguese Access newsgroup? microsoft.public.pt.access. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ "Frank D...

Importing Data From Access Problem
I'm trying to import some query results from MS Access into Excel using the Import External Data function. Some of the queries are coming across OK, others are only bringing the column headers across and some are bringing partial data. Does anyone have any ideas why this may be happening? Thanks Helena ...

Take a look at important patch from the M$ Corporation
--bgknsnsxrf Content-Type: multipart/related; boundary="eqmmxvfryf"; type="multipart/alternative" --eqmmxvfryf Content-Type: multipart/alternative; boundary="nagrsturcwerjp" --nagrsturcwerjp Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to maintain the security of your computer. This update...

Write a tool to generate a PST file with the same structure as old
I will migrate 1000+ users to Exchange 2010 and those users are using OL 2003 and OL2007. To help them migrate more smoothly, I will provide a tool for them to generate PST file with the same structure as an old PST file. Is it possible? How to begin this idea? Thanks, Sam Have you looked at ExMerge? If you are copying a PST file, why do you need to create a copy instead of just using the original PST file? -- Dmitry Streblechenko (MVP) http://www.dimastr.com/ OutlookSpy - Outlook, CDO and MAPI Developer Tool - "PST TOOL" <PST TOOL@discussions.microsoft....

Access text box
I was trying to add text boxes to my database in form design view, which worked fine up until the moment when i was back in form or table view. For some reason when I add detail in one of the cells of the new column it was automatically copied to every other cell in that new column. This has only been happening with the new columns and the ones I created using the wizard when starting the database seem to be working fine. What can I do about this? On 25 mrt, 12:19, FernP <Fe...@discussions.microsoft.com> wrote: > I was trying to add text boxes to my database in form desig...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Official name of the File Selection Part of CFileDialog
I want to know the official name of the file selection portion of the CFileDialog. I also want to know how this is implemented, is there a special kind of ListBox that can do multiple columns? "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:Kw%3h.10768$IC.9812@newsfe21.lga... >I want to know the official name of the file selection portion of the >CFileDialog. I also want to know how this is implemented, is there a >special kind of ListBox that can do multiple columns? List-View http://msdn.microsoft.com/library/en-us/shellcc/platform/commctls/list...

Office 2007 Professional: Help Files Issues
Gents, I cannot find a solution to this odd issue and before I submit a support ticket I thought I would ask the community. We have office 2007 professional (volume licensing) on a Window 2003 terminal server. We have a few users out of a bunch that get the "This page is unavailable" I have tried the Refresh and back. I've tried online and offline modes. In offline I get the table of contents to the left but same unavailable message when I click the links. I noticed on testing some other users, that apparently never used the help. That there are some prompts...

EXCEL 2007
I have a GIS application which uses .dbf files (not sure if they are actually Dbase files). When I need to append data (fields) to the files, I have a big problem in that excel 2007 no longer allows save as / export to .dbf files. If I try bring the data in MS Access and save as .dbf files, I have untold problems with the GIS application... The only way I have been successful with this is to find a user with an older version of excel, insert the data as database columns and then save as .dbf Any suggestions as to how I can get around this with out purchasing a converter? ...

Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price ac...

Can Money Deluxe 2006 Read Money Premium File
Can Money Deluxe 2006 read/write a Money Premium 2006 file? Thanks. Bob Mc. In microsoft.public.money, Bob Mc <Bob Mc@discussions.microsoft.com> wrote: >Can Money Deluxe 2006 read/write a Money Premium 2006 file? Yes. ...

How to playing AVI file....
Hi Could anybody please let me know how to play an "AVI" file in MFC Dialog base application. Thanks in advance.... Mujtaba Look at codeproject/codeguru for samples: http://www.codeproject.com/audio/mp3player.asp -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Mujtaba" <tomujtaba@hotmail.com> wrote in message news:8f4f92aa.0502112151.53cecfc9@posting.google.com... > Hi > > Could anybody please let me know how to play an "AVI" file in MFC > Dialog base application. > > Thanks in advance.... > > Mujtaba You can use a CAnimate...

How I can print full text bigger than column, in repeat column
When I print statements with repeat columns in excell which have text bigger than repeat columns, complete text is printed on all sheets except first sheet. How I can overcome this problem? I want to print complete text in repeat columns, which is expanded to other columns which are blank. ...

covering part of a text box border
I want to have a large text box with a border...I then want to place a smaller text box within the larger one (with its own border). However, some of the border of the large text box is vissible within the small text box. Is there a way to set the small text box on top of the larger one and partially cover the large text box border? I can not find an answer to this problem after many searches. set the smaller box on a higher layer and set a fill color on the = smaller box. "Willie Birdie" <Willie Birdie@discussions.microsoft.com> wrote in = message news:95B71C45-E4BD-...

CFtpFileFind::FindNextFile() can't get the correct file name.
Once I want to get file from HP-UX by FTP. I use MFC CFtpFileFind to find the files under a specified directory in HP-UX, but the return value of CFtpFileFind::GetFileName () is not the correct file name, it includes time (sometimes and date) ahead, such as: 19:30 Mydemofile001.txt 19:35 Mydemofile002.txt but the real file name existed are only Mydemofile001.txt and Mydemofile002.txt. Is there any problem in the low level encapsulated by MFC for FTP list files? Could anyone give me some advice? Thank you. ...

Distributing Mulit-Event ICS file
I would like to email a file to my group, have them click it, and have it update their calendars in Outlook with multiple events. Is this possible? Thanks, Andrew It depends on their version of Outlook. Older versions of Outlook can't use multievent ics files. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE...

Outlook crashes when attacthing files over the network
Hi, A user at my company tries to attach documents etc on a netowrk share. Everytime she goes to do this, Outlook just closes. She's running Outlook 2000 Premium with SPK3 installed. Any idea's? Thanks Lee ...

Wanting to grab group of files and PkZip them.
I have an excel file that has two columns, Code and Filename. i.e. CODE Filename Abc 123456.txt Abc 123457.txt Def 123458.txt Def 123459.txt Etc… I would like to be able to select all files with a specific Code and have them zipped into a file that is named the same as the Code. i.e. Abc.zip would contain 123456.txt & 123457.txt AND Def.zip would contain 123458.txt & 123459.txt The file names are actually quite random but they are associated with specific codes according to this Excel file. I am creating an Access form that will allow users to browse to the Excel...

Inserted sound files won't play
I've seen this reported a few times on Google Groups but no definitive solution: I have PowerPoint 2003 I have inserted a .wav sound into slide 2 of my presentation I clicked Insert>Movies and Sounds>Sound from file In the Browse window, the sound won't even play in Media Player if I right-click it and choose Play From Explorer the same sound file plays fine I have tried converting the sound into other formats such as MP3 I have tried different sound files The icon appears in the slideshow but doesn't play I have set it to play 999 times and not to stop when clicked I have...