EXCEL FOR DATA ANALYSIS: USING CONCATENATE

 OBJECTIVE: CONCANTENATE THE ZIP CODE AND AREA OF QUEZON PROVINCE.

Two advanced functions in MS Excel related to the field of data science are concatenation and VLOOKUP. This blog is for CONCATENATE.

Concatenation Function

Use CONCATENATE, one of the text functions, to join two or more text strings into one string. This function is used to combine texts together and format them accordingly.

As a Data Analyst, we often deal with data when doing financial analysis. The data is not always structured for analysis and we often need to combine data from one or more cells into one cell or split data from one cell into different cells. The CONCATENATE function helps us to do that.

For example, consider the following dataset:

You were given only the Zip Code (Column B) and the Area (Column C) and your task is to write the name and zip code together with a space in the middle just like in Column A.

 Step 1.      Write the formula “CONCATENATE(B2, C2)” in cell D2.

 

2.      Pressing enter, the text would appear like this in Column D:

4300Quezon Provincial Capitol.

Therefore, we need to add a space in between.

3. Modify the formula in cell D2 as: =CONCATENATE(B2,” “,C2)

Where we have added a space string between the quotations.

 

4.      Pressing enter, the right format shows up.

 5. Lastly, fill flash the format in the following row as well to use the same format for the subsequent cells. The fill flash is performed by bringing the mouse icon in the bold right down corner of the selected box of cell D2 until a “plus” sign appears and drag it to cell D20.

Final Result:

 

Congratulations, now you know how to use CONCATENATE!