Export a field from text file to excel and remove duplicates

Hi all,

I am an new to the forum and I have ended up here as so to resolve my need in vba aswell learn good things in vba and my requirement goes as follows

I have a folder and the folder has nearly has 350 text files all the text files are of similar patters

Description of text file :

Text file has at least 11 fields and at the max of 18 fields with a delimiter " " (space)


Now I wanted to load only 10th field from the text file to the excel

Since each and every file has duplicates and also considereing the limitations of excel ( I guess it cant store more than 65000+ records) after importing that field I wanted to remove duplicates and then append with 10th field of next file and remove duplicates for the whole list and on and on till 350 text files



My 10th field can be maximum of 8 bytes but it can be less than that ( but Its sure 10th field will be present in the file)


Please note : total size of the folder is around 450 MB ( Total size of all text file)

If anyone has come across this scenario and the slution used will be helpful for me )


Please let me know if anymore information is needed for the same.

Thanks in advance

Please attach a couple of text files and a sample of the final excel file so we can help you better.

BTW, where is Mars?

hi, premkrishnan, can you post a couple txt files and result expected on them?

Apologies as I just cheked few files have a few added format

So according to the files which changed format

I need to load data only with string $HASP373 and the rest of the records need to be ignored and the line SRCHFOR '$HASP373' should be ignored


Sorry again for missing few minor addons before

BTW, where is Mars?

I am a space lover I know its an impossible task to go out of earth so I thought I would put my location as that

So if i understand you correctly, you want a code that will open the first text file, take the column which contains string $HASP373 and copy the contents of the next column into column A of your output file. Then go to file 2, copy the contents into column B of the output file, etc? Is this right?

Hi Arlu,

These are the following steps

1.Open the text file
2.Copy only the next column after the string "$HASP373" except the record SRCHFOR '$HASP373'
3.Remove duplicates from the file and save it in one location say x
4.Open the next text file and do steps 2 and 3 by appending data to location x and eliminating duplicates
5.do 1 to 4 until all text files are processed

Hope I am clear now

Yes, should the data be appended below or side by side?

Hi Arlu,

Append the data below and remove duplicates

Hi Watserv your code is working fine but I am not able to understand it as I was trying to search for the "$HASP373" but couldnt find it.

Any pointers of what you have done will be helpful for me to have this as base for proceeding with any future requirement.

Thanks Waterserv

please check post #12 as the file and code has been updated to avoid error on processing files that have no pattern inside

Yes I checked that..Thanks