How do i convert text file to excel​？

Converting a text file to Excel can seem like a simple task, but there are various methods to accomplish it depending on the format and structure of the text file. 

Text Files and Excel Format

Text Files (.txt, .csv): These files contain raw data separated by delimiters (like commas, tabs, spaces, or semicolons) but do not include any formatting or complex structure.

Excel Files (.xlsx, .xls): These files are structured with rows, columns, and cells, often containing formatted data, formulas, charts, and more.

Excel allows importing and converting various types of text files into spreadsheets, but the exact steps differ based on the type of delimiter or structure in the text file.

1. Converting CSV Files to Excel

CSV (Comma-Separated Values) is one of the most common types of text files for storing data in a tabular format. Each line in a CSV file represents a row of data, with columns separated by commas.

Method 1: Import Using Excel’s “Open” Feature

Open Excel: Launch Excel on your computer.

Navigate to File: Click “File” in the top-left corner of the Excel window.

Open File: Click “Open” and browse to the location of your CSV file. Make sure to change the file type filter to “All Files” or “CSV Files” in the open dialog box.

Select the File: Choose the CSV file you want to open and click “Open.”

Check the Data: Excel will automatically recognize the comma separators and arrange the data into separate columns.

Save as Excel: Once the data is loaded, you can save it as an Excel file by going to “File” > “Save As” and selecting the Excel format (.xlsx or .xls).

Method 2: Import Wizard (for Advanced Options)

Open Excel: Launch a new or existing Excel workbook.

Go to the Data Tab: Select the “Data” tab in the ribbon.

Get External Data: Click “From Text” or “From CSV” in the ribbon. This will open the Text Import Wizard.

Choose Your File: Browse and select your CSV file.

Delimiter Selection: In the wizard, select the delimiter used in the file (commonly “Comma,” but it could also be “Tab,” “Semicolon,” or others).

Preview and Adjust: Excel will show a preview of the data and you can adjust the column formats (text, number, date, etc.) if necessary.

Finish: Click “Finish” to load the data into Excel. You can then save it as an Excel file.

2. Converting Tab-Delimited or Other Delimited Files

Tab-delimited or other delimiter-separated files (such as files separated by semicolons, pipes, or spaces) can also be converted to Excel. These files are similar to CSVs, but they use a different character to separate the columns.

Method 1: Open and Adjust Delimiters in Excel

Open Excel: Start Excel and open a new workbook.

Open the Text File: Click “File” > “Open” and select your delimited text file (e.g., .txt).

Text Import Wizard: Excel will launch the Text Import Wizard. Here, you will be asked to select the delimiter used in the file.

Select the Correct Delimiter: Choose the appropriate delimiter, whether it’s tab, semicolon, pipe, or space.

Adjust Data Formatting: Excel will show a preview of how the data will be divided into columns. You can also adjust the format for each column (e.g., text, number, date).

Finish Import: Once everything looks correct, click “Finish” to load the data into Excel.

Method 2: Using Power Query (Advanced Users)

Power Query is a powerful tool in Excel that helps with more advanced data import and transformation. It’s particularly useful for larger or more complex files.

Launch Power Query: Go to the “Data” tab and click on “Get Data” > “From File” > “From Text/CSV.”

Choose the File: Select your tab-delimited or custom-delimited text file.

Set Delimiters: In the Power Query editor, you can define the delimiters if Excel doesn’t automatically detect them.

Load Data: After making any adjustments, click “Close & Load” to import the data into Excel.

3. Converting Space-Separated Text Files

Space-separated text files may not always be as straightforward to import as CSVs or tab-delimited files, especially if there’s inconsistent spacing between columns. Excel can still handle these files, but some adjustments might be necessary.

Method 1: Using the “Text to Columns” Feature

Open Excel: Open a new or existing workbook in Excel.

Copy and Paste the Data: If you have a space-separated file, you can copy the data from the text file and paste it into Excel.

Select the Data: Highlight the column that contains the space-separated data.

Text to Columns: Go to the “Data” tab and click on “Text to Columns.”

Choose Delimited: In the dialog that appears, choose “Delimited” and click “Next.”

Select Space as the Delimiter: In the delimiter options, select “Space.” If your file uses multiple spaces to separate the data, you may need to choose the “Treat consecutive delimiters as one” option.

Finish: Click “Finish” to convert the text into separate columns based on the spaces.

Method 2: Using Power Query for Advanced Handling

If the file is large or you need to perform more advanced transformations (like handling inconsistent spacing), Power Query is the best tool for the job. The steps for this are similar to those used for tab-delimited files.

4. Converting Fixed-Width Text Files

Fixed-width files store data in columns with a specified number of characters per column. These files require special handling, as they don’t use delimiters to separate the columns.

Method 1: Using the “Text to Columns” Wizard

Open Excel: Open Excel and paste the data from the fixed-width file.

Highlight the Data: Select the column that contains the fixed-width data.

Text to Columns: Navigate to the “Data” tab and click “Text to Columns.”

Fixed Width Option: In the wizard, choose the “Fixed width” option and click “Next.”

Set Column Breaks: Excel will display a preview where you can set the column breaks by clicking at the appropriate positions.

Finish: Click “Finish” to separate the data into columns.

Method 2: Using Power Query

For more complicated fixed-width files, Power Query provides better control for setting column widths and importing data correctly.

Launch Power Query: Go to “Get Data” > “From File” > “From Text.”

Set Fixed Width: Power Query allows you to manually adjust column widths by defining positions based on the data structure.

5. Dealing with Special Formatting or Complex Text Files

Sometimes, the text files might have special formatting or embedded data (such as HTML tags or unescaped characters) that require more advanced techniques for conversion.

Method 1: Using Excel’s Formulae

For certain complex text files, you may need to use Excel functions like LEFT(), MID(), RIGHT(), FIND(), or TEXT() to extract or clean data after it’s been imported.

Method 2: Power Query or VBA Scripting

For highly complex cases, such as text files with embedded HTML or JSON, using Power Query’s advanced transformations or writing a custom VBA (Visual Basic for Applications) script can help automate the extraction of specific data points.

Conclusion: Choosing the Right Method

The method you use to convert text files to Excel largely depends on the structure and complexity of the text file:

For simple CSV files, Excel’s built-in open or import options work best.

For tab-delimited or space-separated files, you can either use Excel’s “Text to Columns” feature or Power Query for more complex files.

Fixed-width text files can be easily handled with the “Text to Columns” wizard or Power Query.

For complex or irregularly formatted files, Power Query or VBA scripting might be the best option.