HELP with data delimiters

[super3origin]

Hi,

I have long list of files in .dat format that I want to convert them into excel format. The files have bunch of data separated by delimiters and I need them to be separated  by columns in excel. I know the excel trick where I can import the data in excel/or open with excel and use the excel text wizard. My problem is that I have over 2000 files that I need to convert and split the texts in columns. Opening and delimiting each one of them will take forever. 

Is there any way I can split all text files at once? 

I can convert all of them to xls, but how I can split the text for all of them at once?  

I appreciate any advice 

[#2]

@super3origin 

See if it helps in you case. 

https://professor-excel.com/merge-excel-files-combine-workbooks-one-file/

[#3]

@AshaKantaSharma 

Thank you for responding!

I'm not trying to merge the files. Rather, I want each text file to be converted to an excel file with text separated in different columns based on their delimiters (i.e semicolon, space, ...)

for example,

Name, orange; time day              .dat

to 

| Name | orange | time | day |      .xlsx

I can do that for one file a time using the text wizard, I'm wondering if there is a way to do that for all of them at once?

[#4]

@super3origin I think the best solution for your request is to save all your files in a specific folder,  then write a macro to loop through all files of that same format in that folder, import each to Excel and split Text to column with delimiter, save imported data in a workbook, name it accordingly then close and move to the next file.

You will need to google vba codes for 2 things:

- Loop through a directory to open each file. e.g. https://www.thespreadsheetguru.com/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-...

- Import file to Excel, e.g. https://sitestory.dk/excel_vba/automatic-import-textfile.htm

If the number of columns in each file is not consistent, you may need to imitate the approach of this code to automate the import regardless of # columns : https://www.mrexcel.com/board/threads/import-tab-delimited-text-file-into-excel-via-directory.671778...