Load data into Access/Excel

I have a list of records in a text file with the format below.  I would like to convert it to this format: LastName|FirstName|MiddleName|Title|Company|Telephone|.  Would you please show me how to do it?  It will be ok if we have to load the file to Excel or Access before making it like the format above.

Thanks,


LastName: DOE
FirstName: JOHN
Middlename:A
Title: Technican
Company: CompA
Telephone: 1111111111

LastName: SMITH
FirstName: JOE
Middlename:B
Title: Engineer
Company: CompB
Telephone: 2222222222

LastName: SMITH
FirstName: JOE
Middlename:B
Title: Engineer
Company: CompB
Telephone: 2222222222
...............................
ttta83Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ttta83Author Commented:
Thanks for the link.  Before I can save as pipe delimiter file, I need to load the data into excel or access.  Since the file that I have in this format, I don't know how to do it:

LastName: DOE
FirstName: JOHN
Middlename:A
Title: Technican
Company: CompA
Telephone: 1111111111

LastName: SMITH
FirstName: JOE
Middlename:B
Title: Engineer
Company: CompB
Telephone: 2222222222

LastName: SMITH
FirstName: JOE
Middlename:B
Title: Engineer
Company: CompB
Telephone: 2222222222
0
unknown_routineCommented:
I did saved your file into a text file and right cicked and choose "Open with" and then chose Excel. It worked for me.
0
ttta83Author Commented:
Would you please show me how to import each block form text file into 1 line in Excel

LastName: SMITH
FirstName: JOE
Middlename:B
Title: Engineer
Company: CompB
Telephone: 2222222222

to

SMITH  JOE    B    Engineer   CompB    2222222222

Thanks,
0
Helen FeddemaCommented:
This would be quite easy to do in Word VBA -- I have processed data like this (for import into Access, but it could be done for import into Excel just as well) by writing code to search for a word or phrase (in this case, for example, "LastName:"), go to the next word, and save it to a field in a record in an Access table.  Make a new record every time you get to LastName.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.