How do I create a macro to import and sort a txt file with more than one delimiter?

**JonnyEnglish** · 01-28-2016, 03:53 PM

Hey everyone,
I’d like some help to create a marco that will import a txt file containing up to 100 lines into excel and sort it into columns. I’m ashamed to say that I’ve been doing it manually as I’ve never had the time to learn how to use excel properly until now.
I’m a slow learner but I’m determined to learn how use excel as it could really help me at work.
Thanks for any help

this is the format of the txt source file:

"Jonnyenglish___________X"
Optimized meds: 9 units: (34.0006% sin) 22.3611: Meda, Medabc, demB#2, Meda, Medabc, demab
"JonnyBrovo___________X"
Optimized meds: 5 units: (60.106% sin) 5: Meda, Medabc, dema, Meda, Medabc, demab
"JonnyDepp___________X"
Optimized meds: 9 units: (17.6% sin) 90.11: Meda, Medabc, htctttt, Meda, Medabc, demab
"JonnyKnoxville_________X"
Optimized meds: 7 units: (34.0006% sin) 31.3322: Dpata#3, Medabc, dema, Meda, Medabc, demab

This is how I would like it to be sorted:

Jonnyenglish___________X	9 units	34.0006% sin	22.3611	Meda, Medabc, demB#2, Meda, Medabc, demab
JonnyBrovo_____________X	5 units	60.106% sin	5	Meda, Medabc, dema, Meda, Medabc, demab
JonnyDepp______________X	9 units	17.6% sin	90.11	Meda, Medabc, htctttt, Meda, Medabc, demab
JonnyKnoxville_________X	7 units	34.0006% sin	31.3322	Dpata#3,Medabc,dema,Meda,Medabc,demab

**Bernie Deitrick** · 01-28-2016, 04:51 PM

IS your desired result 5 columns of data? Perhaps you could post an example text file, and the workbook that you would want created from that example file

**JonnyEnglish** · 01-28-2016, 05:18 PM

Hi, thanks for your reply. As requested - I’ve attached a couple of files as examples.

**Bernie Deitrick** · 01-28-2016, 06:30 PM

Try this macro:

Please Login or Register  to view this content.

**JonnyEnglish** · 01-28-2016, 07:24 PM

Thankyou! It works like a charm. I’d like to learn how you did that - could you recommend some reading material?? Again, thank you.

**Bernie Deitrick** · 01-28-2016, 10:45 PM

Write complex data-analysis macros for 30 years or so...

I figured out the steps needed then recorded a macro doing them. Then I edited the code and made it flexible.

I always recommend a book by John Walkenbach in his "Power Programming with VBA in Excel xxxx" It is really comprehensive and clear.

**JBeaucaire** · 01-29-2016, 03:57 AM

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

How do I create a macro to import and sort a txt file with more than one delimiter?

LinkBack

Thread Tools

Rate This Thread

Display

How do I create a macro to import and sort a txt file with more than one delimiter?

Re: How do I create a macro to import and sort a txt file with more than one delimiter?

Re: How do I create a macro to import and sort a txt file with more than one delimiter?

Re: How do I create a macro to import and sort a txt file with more than one delimiter?

Re: How do I create a macro to import and sort a txt file with more than one delimiter?

Re: How do I create a macro to import and sort a txt file with more than one delimiter?

Re: How do I create a macro to import and sort a txt file with more than one delimiter?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Import text file and sort the data

[SOLVED] import text file using multiple spaces as delimiter

Import .csv with delimiter as a semicolon & file location dependant on cell selection

Implementing TextFile import Delimiter settings on TXT Import VBA Script

Macro to collect data from multiple excel files N store in a text file with pipe delimiter

VBA macro for Inserting text file(having pipe as delimiter) into Excel sheet

[SOLVED] How to import multiple txt file and arrange by column with delimiter

Bookmarks

Bookmarks

Posting Permissions