The Excel CONCATENATE function is a really useful function for dealing with text in Excel. As an engineer you’ve probably come across text strings that were in different cells and needed to be joined together. This can be easily accomplished by using CONCATENATE.
Excel CONCATENATE Function Syntax
The syntax for this function is:
=CONCATENATE(string_1, [string_2], …, [string_n])
Alternatively, the ampersand (&) can be used to join text strings together with the same result:
=string_1&[string_2]&…&[string_n]
Uses of CONCATENATE Unique to Engineering
Throughout my experience as an engineer, I’ve found three uses for this function that I think are unique to engineering:
- Creating Conclusion/Result Statements
- Dynamically Updating Chart Titles
- Generating Model Codes based on Component Specifications
Conclusion/Result Statements
Once your spreadsheets get fairly complex and you are sharing them with others, you should probably make it a habit of including a conclusion statement near the top of the main worksheet.
This makes it easy for someone in the future (yourself included) from searching through the spreadsheet to understand it’s result.
The statements can also easily be copied into an e-mail or report, which saves time if this is a calculation that is done repeatedly.
You can use the CONCATENATE function to gather the results from scattered cells and organize them into concise result statements written in a complete sentence.
For example, let’s say that you have a spreadsheet that calculates the peak torsional stress in a shaft. The result (in psi) is returned in cell C55 and the value is 40,000.
You could use CONCATENATE to create a conclusion statement in a cell:
=CONCATENATE(“The peak stress in the shaft is “, C55, “psi.”)
The result displayed in the cell would be:
The peak stress in the shaft is 40,000 psi.
Dynamically Updating Chart Titles
Another good use for CONCATENATE is to create chart titles that are based on a chosen data set.
This can be used to create meaningful chart titles without having to manually update them every time a new data set is chosen.
First, link the chart title to a cell.
Next, use the CONCATENATE function to create a chart title in that cell based on whatever input cells you desire.
Generating Model Codes from Specifications
This is one that I use quite often when I order something like a hydraulic pump or motor.
By using CONCATENATE I can substitute a required displacement into the model code automatically.
Now I have a code I can send to the supplier for a quote without having to manually type in a model code.
This is also great for minimizing errors.
Your Turn
How about you? Do you have a really good use for the Excel CONCATENATE function you would like to share? Leave your thoughts in the comments section below.
