What is a text separator in Excel

Correctly import CSV files into Excel

Thorsten Eggeling

By default, Excel offers the option of importing CSV files. To ensure that the content is not completely written into a cell, the following must be observed due to the use of commas as separators.

EnlargeThis is how importing CSV files into Excel works.

CSV stands for "Comma Separated Values", but if you offer this common text format to Excel, it simply writes each line as text in the first cell. Commas are not interpreted as separators for the fields (columns) because under German Windows ("Control Panel ➞ Regional and Language Options") the semicolon is defined as a list separator.

Ten Excel Tips for Office Professionals

Change import file or "Text in columns": It is better not to change the separator in the Control Panel. If the CSV or text file does not use a semicolon as a field separator, simply write the separator used in the top line of the file with a pure text editor (e.g. Notepad) in the form:
sep =,
as well as for the separator “comma” or also sep = and after the “=” press the tab key. Then Excel knows which character to use to split the data records. You can, however, also repair the defective import later in Excel: To do this, mark the column with the mess of text and choose "Data ➞ Text in Columns". Then take the file type "Separated" and mark the appropriate separator. You can already see the success and result of the action in the data preview.