In the first picture, concatenation works well with a delimiter of our choice and the function =CONCATENATE(” & “;TRUE;C4;D4). What is probably noticeable here is that a space was left before and after the & character. If we hadn’t done this, the values ​​from the two cells would have been mapped to the target cell, but then everything would have been written together Meier&Hamburg.

In the second picture we see what happens when we use the CONCATENATE function as is. The values ​​from cells C4 and D4 are simply pasted into the destination cell with no spaces or separators. Completely impractical for what we want in the end.

In the third picture we work again with TEXTCHAINS but use a space instead of a separator. It is important here that you (whether any separator or a space is used) always put the space or separator in quotation marks. If you don’t do this, Excel will consider the character as part of the function and, for example, try to calculate with a mathematical character inserted, which will probably fail.

But Excel is not completely stupid at this point, because if you want to use the CONCATENATE function to display cells B4 and C4 with first and last names in the target cell, a space is automatically inserted with this function. It seems that Excel recognizes that this is a name that is usually written separately.

see fig. (click) to enlarge)

The difference between CONCATENATE and TEXT CONCATENATE is actually primarily that the TEXT CONCATENATE function offers the extended possibility to specify a space right at the beginning of the function, with which the values ​​of the individual cells are to be separated from one another, instead of simply lining up all the character strings together.

So much for the basic functionality of concatenation in Excel. But now we want to do what the title of this tutorial says. We want to create a whole sentence in a single target cell from different individual values ​​that are each in a cell.

Our sample table now shows a total of six columns with surname, first name, place of birth, birthday, time and a result column. The aim should be to bring the values ​​from the individual cells into a meaningful sentence using the TEXTCONCATENATE function in combination with formatted values, which is then displayed in a cell. As a result, the table should look like the following figure.

see fig. (click to enlarge)

At first glance, this looks a bit confusing, but don’t worry, we will now take the function apart and explain it piece by piece.

• = TEXTCONCATENATE( | This is where our function begins
  

  ---

• = ” “ | Here we enter the separator which we want to have between all values. In our example just a space.
  

  ---

• = TRUE | With this we determine whether empty cells that we specified as reference points in the function should be ignored.
  

  ---

• = ;$B4;$C4 | These are the first points of reference (surname and first name) that make up our sentence.
  

  ---

• = “is on” | represents a fixed text which is necessary for meaningful text formation. You could also use a cell as a reference point, but since we don’t have one with the required text, we simply write the text in quotation marks ourselves.
  

  ---

• = ;TEXT($E4;”DD.MM.YYYY”) | Here we refer to cell E4, which contains the date of birth, and at the same time we combine the CONCATENATE function with the TEXT function. Because this gives us the opportunity to tell Excel that the text we retrieve from cell E4 should have a certain format. In our example a date format.
  

  ---

• = “around” | again a fixed text for the sentence formation.
  

  ---

• = ;TEXT([@time];hh.mm.ss) | At this point we are referring to the time, and instead of using the date format as before, we simply use the text format: time. Important: In our function, cell F4 is not the reference point, as you would expect, but [@time]. This has to do with the fact that in our example we are working with a dynamic table through which each cell below a column gets a column name that is then used here.
  

  ---

• = “clock in” | again a fixed text for the sentence formation.
  

  ---

• = ;[@birthplace] | Again the column name instead of cell D4 as reference point.
  

  ---

• = “born” | And again a fixed text for the sentence formation

Explanations: The more complex a function becomes, the greater the susceptibility to errors. Here, however, you can often use Excel’s error correction and either display the calculation method, or you will immediately receive an error message in which Excel will mark the relevant position in the processing line where an error is hidden.

You may also have noticed that we put a $ sign in front of reference points such as cell B4. This is so that we don’t have to write this function separately for each line, but can simply use the copy control. The column is then fixed by the $ sign in front of a reference point and does not jump to the next column as usual when copying a formula, which would have messed up our entire function.

We hope that we were able to help you with our little tutorial and wish you lots of success trying it out.