Anzeige

Concatenate text and date in Excel – Tutorial

Linking cells and content (also across files) is one of the most important core tasks that Excel offers. Of course, results from several cells can be calculated and displayed in another cell. But is it also possible to map individual content from several cells in a single cell with a function that can be configured as desired?

The answer is “yes, of course!” you can do this with the “CONCATENATE” and “TEXT CONCATENATE” functions. Of course, the functions are not really new, but the same questions always come up for many users.

  • How can I insert spaces between values?
  • How to concatenate a date with texts in Excel?
Excel Zahlen und Text verketten

Topic Overview

Anzeige

In our little tutorial we will take a closer look at how concatenate date with text in Excel works, and above all how to use the function in such a way that the display is correct in the end. The solution lies in the combination of two functions. More about nested functions >>>

Concatenate text and date in Excel – Tutorial

Linking cells and content (also across files) is one of the most important core tasks that Excel offers. Of course, results from several cells can be calculated and displayed in another cell. But is it also possible to map individual content from several cells in a single cell with a function that can be configured as desired?

The answer is “yes, of course!” you can do this with the “CONCATENATE” and “TEXT CONCATENATE” functions. Of course, the functions are not really new, but the same questions always come up for many users.

  • How can I insert spaces between values?
  • How to concatenate a date with texts in Excel?
Excel Zahlen und Text verketten

Topic Overview

Anzeige

In our little tutorial we will take a closer look at how concatenate date with text in Excel works, and above all how to use the function in such a way that the display is correct in the end. The solution lies in the combination of two functions. More about nested functions >>>

Concatenate date, text and time

Concatenate date, text and time

Before we start concatenating different formats from several cells, I would like to briefly describe the basic functionality. In our example we have shown 6 people in a table. Each cell contains a value (last name, first name, birthday, etc.). We now want to merge these values into a single cell.

For concatenation we use TEXTCONCATENATE with separators, and then two more examples with the CONCATENATE function with and without spaces.

see fig. (click to enlarge)

Excel - TEXTVERKETTEN mit Trennzeichen
Excel-VERKETTEN ohne Leerzeichen
Excel-VERKETTEN mit Leerzeichen

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)

Excel-Namen VERKETTEN automatisches Leerzeichen
Advertisement

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)

TEXTVERKETTEN mit Leerzeichen-Datum-Uhrzeit
Advertisement

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.

Before we start concatenating different formats from several cells, I would like to briefly describe the basic functionality. In our example we have shown 6 people in a table. Each cell contains a value (last name, first name, birthday, etc.). We now want to merge these values into a single cell.

For concatenation we use TEXTCONCATENATE with separators, and then two more examples with the CONCATENATE function with and without spaces.

see fig. (click to enlarge)

Excel - TEXTVERKETTEN mit Trennzeichen
Excel-VERKETTEN ohne Leerzeichen
Excel-VERKETTEN mit Leerzeichen

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)

Excel-Namen VERKETTEN automatisches Leerzeichen
Advertisement

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)

TEXTVERKETTEN mit Leerzeichen-Datum-Uhrzeit
Advertisement

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.

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen
2022-11-17T07:06:57+01:00By |Categories: Microsoft Excel, Microsoft Office, Office 365, Short News|Tags: , , |

Title

Anzeige

Most read:

Search by category:

Alle Vorlagen ansehen

Auch interessant:

Go to Top