#FORMULA TO CONCATENATE ROWS IN EXCEL HOW TO#

This function only works with the latest versions of Microsoft Office, and it offers some nice features.įirst, you can choose how you want to separate the values of different cells, with no need to type these spaces, commas, or other symbols in the formula. TEXTJOINĪnother function that you can use when combining textual content is TEXTJOIN. However, sometimes you may want to use the CONCAT function to keep your formulae clean and to make them easier to read. In contrast, the CONCATENATE function is limited to 8,192 characters, which means that you can only use it to join up to 255 strings. Besides, the “&” operator has no limitations regarding the number of strings that you can join. Using the “&” operator is a more convenient option. If you want to separate the values of cells with spaces or commas, here’s what your formulae will look like: Here’s an example of a formula that you can use:

This method of joining cells is recommended by Microsoft, and it’s much easier to use than the CONCAT and CONCATENATE functions. However, you may also choose not to use either of the formulas above and choose an even simpler option - the ampersand operator (&). Here’s what the example above would look like with the CONCAT function: You can use this formula in the same way as CONCATENATE, but CONCAT is certainly easier to use because it’s shorter. The CONCAT function works with Excel 2016 and Excel Mobile. The CONCATENATE function may also be unavailable in future versions. However, if you’re using a newer version of Excel, you might consider updated functions. If you want to separate values of cells with spaces, you can add them in quotation marks, separated with commas:ĬONCATENATE is the oldest function of this kind and the only function you can use to join text strings when dealing with Excel 2013. X1, X2, and X3 are the cells that you want to join. In this case, your formula will look like this: First of all, you can use the CONCATENATE function.

#FORMULA TO CONCATENATE ROWS IN EXCEL HOW TO#

How to Join Text Strings in Excel CONCATENATEĮxcel lets you to join text strings in different ways. In this case, you can use concatenation functions, like “CONCATENATE,” “CONCAT,” and “&.” Let’s consider each one of these formulas and figure out the differences. You may want column D to contain both their first and last names, but retyping their names manually is too time-consuming and inefficient. You can also insert a number obtained from a formula in-between textual content.įor example, you may have your customers’ first names in column B, and their last names in column C.

In Excel, such content is called text strings. For example, you can use this method to combine pieces of textual content from different cells. If you choose to concatenate cells instead, you won’t merge the cells themselves but combine their content.Ĭoncatenation doesn’t impact cells but joins multiple values. As a result, you get a single large cell that is displayed across multiple columns or rows. The first option means turning multiple cells into one. Generally, Excel enables you to combine data in two ways: you can either merge cells or concatenate their values. In this guide, we will look at concatenation in detail and examine the techniques that you can use in different situations. Most often, you use concatenation in Excel to join such data as names and addresses, display time, and date. The second process is called concatenation. You may also need to do the opposite, combining data from multiple columns into one. Sometimes, you may need to split the content of one cell into different cells. When dealing with Excel workbooks, data may be structured in a way that doesn’t fit your needs and objectives.

Formula to concatenate rows in excel