In Microsoft Excel, Concatenate is a text function, and its purpose is to combine two are more text strings into one string. The Concentrate function can improve the way your data is aligned in your spreadsheet, especially if you want to join the data together.
The formula for the Concatenate function is Concatenate(text1, [text2],…).
The syntax for the Concatenate function is below:
- Text 1: The first item to join. This item can be a text value, number, or cell reference. It is required.
- Text 2: Additional text items to combine. You can have up to 255 items and up to a total of 8,192 characters. It is optional.
How to use Concatenate in Excel to improve data formatting
Follow are the method below on how to use the Concatenate function in Excel to improve data formatting:
- Using the Concatenate function to create a sentence.
- Using the Concatenate function to join and add space between words.
- Use the Concatenate function to join and add a comma between words.
- Use the Concatenate function to join and add space and ampersand and another space between words.
1] Using the Concatenate function to create a sentence
You can use the Concatenate function to create sentences in your table or spreadsheet. In the photo seen above, we wanted to create a sentence that Lisa Ried got an average of 60. The Formula would be =CONCATENATE(“The Average for “, A5, ” “, B5, ” 60 “). You will notice that the text in A5 is joined together with B5.
2] Using the Concatenate function to join and add space between words
You can join text in different cells together and have space between them. Use the formula =CONCATENATE(A4, ” “, B4). You will notice that there is space between the joined words.
3] Use the Concatenate function to join and add a comma between words
You can also add a comma between your words by using the formula =CONCATENATE(A3, ” “, B3, “,”, 90). In the photo above, you will see that we have joined the last name and first name together and added a comma to separate the average.
4] Use the Concatenate function to join and add space and ampersand and another space between words
If you want to combine your words using an ampersand and add a space between them, use the formula =CONCATENATE(A3, ” & “, A7). In the photo above, you will see that the texts are joined with an ampersand and a space between them.
You can also use the formula =A3 & ” & ” & A7; it gives the same result as the formula above. The difference is that they are written differently; it uses only the ampersand and calculation operator instead of Concatenate.
What are some other kinds of data you could combine using concat or CONCATENATE?
Usually, persons would use the Concatenate function to combine data such as names and addresses, date and time. It’s helpful to users that know what they are doing when it comes to the Concatenate function.
What is the benefit of CONCATENATE in Excel?
The benefit of using the Concatenate function is that it allows you to combine text from different cells in your spreadsheet or tables into one cell. You can combine data from cell A with cell B.
We hope this tutorial helps you understand how to use the Concatenate function in Excel to improve data formatting; if you have questions about the tutorial, let us know in the comments.
Now read: How to fix the #VALUE error in Excel.
