Search for:

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.

concatenate

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

New call-to-action

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)
New call-to-action
New call-to-action
Author

Content at Atlan. Tweets infrequently at @christngrcia.

4 Comments

  1. ALFONSE MBATHA Reply

    Is it possible for one to get a certificate from your academy.

  2. Dear Sirs, it is with pleasure that I made your discovery on the Net. I really appreciate what you do so generously. As a WaterAid Research and Documentation Officer in Mali, I am constantly on the lookout for reliable data and methodology to obtain reliable data or to improve data quality. So reading your publication improves my learning on data and surveys methodologies.

    Thanks a lot!
    Regards!

  3. Thanks for the quick primer on concatenate and data parsing. I wish there was a way in excel to use multiple delimiters…

Write A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.