How to use ConcateNate function on Excel

ConcateNate function on Excel is a function that connects strings of characters in different data columns in a table, forming a complete string of characters.

In Excel when we need to join characters in data columns in a table, we will use ConcateNate function. The function will help users to associate each character in each column and create a complete string of characters, according to the content requirements of each data table.

The ConcateNate function is most commonly used when processing data tables, including Full and Full names, with the requirement to enter the Full Name in the data table. Instead of having to manually type the full name of each person, we can immediately use ConcateNate function to connect the First and Last names in different columns to a complete name. The following article of Network Administrator will guide you how to use ConcateNate function in Excel, with each case connecting different character strings.

  1. Summary of expensive shortcuts in Microsoft Excel
  2. These are the most basic functions in Excel that you need to understand

ConcateNate function syntax on Excel is = CONCATENATE (text 1, text 2, .) .

Inside:

  1. Text 1 is a required character string.
  2. Text 2 can enter this character string or not. The maximum for Text 2 is 255, but a comma is required between strings.

Note to use ConcateNate Excel function:

  1. Between Text arguments, a comma is used to separate.
  2. When there is a space in a character string, "" should be added between the words.
  3. In case the error results with the #NAME error, it means that in the formula did not use "" quotation marks when adding text characters as arguments.
  4. From Excel 2016 onwards, the function will be renamed to CONCAT. Usage and structure will be similar to those used with ConcateNate.

I will take the example with the following table of data when connecting the characters in the first and last column of Name and column to complete the string in the Last and Last column. Users need to check after each character must not have spaces, avoid the case when the graft will be redundant.

How to use ConcateNate function on Excel Picture 1How to use ConcateNate function on Excel Picture 1

1. Connecting common words:

For normal word pairing, enter the formula in the result cell = CONCATENATE (B2, C2) and press Enter.

How to use ConcateNate function on Excel Picture 2How to use ConcateNate function on Excel Picture 2

Then we will get the result as the complete name as shown below.

How to use ConcateNate function on Excel Picture 3How to use ConcateNate function on Excel Picture 3

2. Append characters with extra spaces:

We enter the formula = = CONCATENATE (B3, "", C3) .

How to use ConcateNate function on Excel Picture 4How to use ConcateNate function on Excel Picture 4

The result will be as shown below.

How to use ConcateNate function on Excel Picture 5How to use ConcateNate function on Excel Picture 5

3. Append the word to the text:

For example, I will add the word Thị to the result box D4, enter the formula = CONCATENATE (B4, "Thi", C4,) and press Enter.

Note to the user , depending on the location of the word where we insert it in the correct position in the formula, here I will insert in the middle of 2 cells. Double quotes must also be adjusted at the two ends so as to create the distance between words. If you have a "sign in the word to be inserted, that means the characters will be written immediately. With the data tables, you and the name need to adjust the '' so that it is appropriate.

How to use ConcateNate function on Excel Picture 6How to use ConcateNate function on Excel Picture 6

If there is an extra space like the above example, then the final result will have a space like the image below.

How to use ConcateNate function on Excel Picture 7How to use ConcateNate function on Excel Picture 7

Above is how to use the ConcateNate function in Excel, with the use of different characters. You can apply the general formula to the ConcateNate function when concatenating a string, or use an extra '' in the case of a special string.

I wish you all success!

5 ★ | 1 Vote