Here’s a question from one of our readers:Hi, I sometimes need to import the data from a .txt file to my Excel 2016 workbook. I know common way to insert the data from text inside Excel, because I repeatedly transfer the same data from text to Excel. My question is if there is a way to avoid doing it again and transfer on one click?Here’s our answer:Yes, we can do that by recording using the macro. Once we recorded the transfer, we can just run the macro to transfer the data from the text file to your Excel 2016 workbook. We will discuss about them in this post.Record the macro:Go to Developer tab and select Record Macro.
Enter your Macro name, Shortcut key and click OK.Recording will be started.Click on Data in the toolbar and select From text.Select your text file that needs to be imported.Select the Delimiter option.
If your data already contains header, check on My data has headers.Select any of the Delimiter, that makes your data align properly and click Next.Check on the format of the data in the Column data format. (If necessary)Then, Click Finish.Select the cell from where the data has to be entered in the Where do you want to put the data?Click OK.Go to Developer tab and select Stop Recording.
We have finished recording the macro. Now, we just need to modify the code a little bit.Modifications:Select the Macros in the Developer tab.Select the Macro Name and click Edit.
In the Destination:=Range(“$I$1”)), change the inside value to Selection.Address. Example: Destination:=Range(Selection.Address)).Delete .CommandType = 0That’s it, now whenever we click on the macro, the data will be copied from the selected cell.Text Import Wizard:Tab: It’s is Tab Separated values. Values with the Tab spaced are divided.
Semicolon: If your values needed to be separated by a semicolon, we can check on it. (“:”)Comma: Those values that are separated by comma can be separated by it. (“,”)Space: If values are separated by Space, check on Space option. (” “)Other: If there is any other character that is used to separate the value, enter the character and check on it. Like if characters are separated by Question Mark, enter ? in the box.
If there are consecutive characters that separate the values, click the Checkbox next to Click consecutive delimiters as one.Text qualifier: If you have inserted the text qualifier character, then the values that are after the text qualifier character are considered to be one value. Example: If Single Quotation mark (‘) is your text qualifier, the values after the single quotes will be consider as a single value.Different Delimiters:As we have recorded the macro with single delimiter (Tab) and if the other data in a text is separated by (Semicolon), we need to change the code .TextFileSemicolonDelimiter = False (to True). Same change applies for the other delimiters.