How to Use Multiple Character Delimiters in Excel

CSV (Comma Separated Values) files are frequently used in storing and distributing raw data in practically any field of work like finance, engineering, construction, food, health, commerce and others. Customer lists, product lists, order lists, inventory lists and statistical reports can be shown as example uses for CSV files. The data saved in a CSV file, also known as comma delimited file, while meaningful and will provide valuable information to those who analyze it, it can't be processed and put into use without first being separated into columns.

One can separate the data in a CSV file manually (via basic copy & paste) if the amount of data stored in the file is tiny (very few rows) or programmatically using Excel or any programming language that may do the job. In Excel, there is a Text to Columns feature that exactly does this task.

What Text to Columns does is that it basically converts text that's stored in one cell into columns. Though it is used mostly for converting text that consists of comma separated values, you can use it any similar text separation based on certain criteria. In most cases, the separation is done based on the delimiter character that is used in the CSV file, such as comma, tab, semicolon or space. However, there are times where you may need to use more than one character as a delimiter for your specific needs, e.g. a comma and a space, two semicolons etc.

Now, let's see how we can use the Text to Columns feature to separate comma separated values into columns by using multiple character delimiters.

Excel Text to Columns with Multiple Characters as Delimiter

I will be using MS Office 2007 for demonstration purposes but since Excel's core features are more or less the same across different versions, you should have no issues following the tutorial if you are using another version of Excel. Our multiple character delimiter is a comma plus a dot (,.).

STEP 1: Open Excel and open your CSV file.

STEP 2: Select all the cells that contain the delimited text, that you want to separate into columns.

STEP 3: Go to the Data tab at the top menu, where you will see the Data Tools section.

In the Data Tools section, you will see the Text to Columns button.

The tooltip for this button says "Separate the contents of one Excel cell into separate columns. For example you can separate a column of full names into separate first and last name columns."

STEP 4: Click the Text to Columns button, that will open the Convert Text to Columns Wizard.

This wizard will guide you through the separation process.

STEP 5: Select Delimited and click Next.

On the next step, you will see a number of options to set your delimiters.

STEP 6: Firstly, check Comma and Other values, and enter dot character (.) into the input field next to Other.

Note that you need to select and enter the correct characters for the text in your CSV file depending on what delimiter characters are used.

STEP 7: Secondly, check the checkbox for "Treat consecutive delimiters as one".

This will kind of merge the two characters, comma and dot, into one character delimiter and the separation of your text will be accordingly as you can also see in the Data Preview.

Once you are done, click the Next button.

The next step contains data format and destination options which you may change to fit your needs or leave as is if they are fine (this step has no importance in the usage of multiple character delimiters, hence we are skipping quickly).

STEP 8: Finally, click the Finish button to do the conversion.

The comma and dot separated text is now separated into multiple columns and it is ready for use in your calculations and other procedures.

f t g+ in