Concatenate & Delimit: 2 Simple Operations to Manipulate Data in Excel

Once your data is structured and cleaned at a basic level, you may need to carry out a series of functions across your data set. Delimiting and concatenating are two simple operations that will help you manipulate your data and make it ready for analysis.

Delimiting Data

Delimiting data is helpful to automate data segregation.

Imagine you are analyzing responses to the survey question, “What is the name of the household head?” and the responses include both the first and last name of the household head separated by a space. If you wish to separate this information into two columns — first_name and last_name — you can delimit the values using the space as the delimiting character.

In Excel, click on “Text to Columns” in the “Data” tab of the Excel ribbon. A dialogue box will pop up that says “Convert Text to Columns Wizard”. Select the “Delimited” option. Now choose the delimiting character to split the values in the column. You can see a preview below. Click on ‘Next’ for additional options and then click ‘Finish’ once you are satisfied with the preview.

This example shows how you can separate the first and last names in this column by using the space character as a delimiter.

Once you click on “Finish”, you will have two columns — containing the first and last names.

Concatenating Data

Concatenating data achieves the opposite of delimiting data — it combines strings from separate fields into one text string.

The concatenate function in Excel allows us to carry out this function across all cells. Here’s how the concatenate function looks in Excel:

=CONCATENATE(comma-separated list of strings to be concatenated)

Let’s again use the example of names; assume that we want to combine the first and last names of each household head into one cell with an underscore in between the names. We can concatenate this by applying the formula:

=CONCATENATE(first_name,"_",last_name)