Improve this answer. Using the CONCATENATE Function 3. Suppose you have "First Name" in one column and "Last Name" in the second column, and you want to get the full name by combining them. I am diligent, career-oriented, and ready to cherish knowledge throughout my life. CONCATENATE in Excelformula is used when the user needs to join the values of two or more cells into one cell. Highlight the TRANSPOSE(A2:A16)&"," in the formula, and press the F9 key to replace cell reference with cell contents. Creates a sentence by joining the data in column A with other text. Then choose a separator that you want to separate the combined contents from the Specify a separator; (3.) The following image shows the fruits and their prices. Concatenate a text string and cell value 4. To add a space, we can simply add another argument: " " (two double quotes around a space). Example 2 - Concatenating Dates The CONCATENATE function in Excel allows us to join two or more text strings in the same cell. Concatenating a range. TRUE indicates that you are going to ignore empty values. For example, the full address with the full name is written in three lines but a single cell using theWrap Textexcel feature. D1 in our example. Was this reply helpful? The CONCATENATE Excel function is used to join several text strings into one string. You can learn more from the following articles , Your email address will not be published. Tip: To include delimiters (such as spacing or ampersands ( & )) between the text you want to combine, and to remove empty arguments you don't want to appear in the combined text result, you can use the TEXTJOIN function. It can be any string. Hi DOMINIC, Copy the spaces (s) you want to delete in one cell of your column. If TRUE, empty cells are ignored. Public Function concat (r As Range) As String concat = "" For Each rr In r concat = concat & rr.Value & " " Next rr End Function. This is because CONCATENATE may not be available in future versions of Excel. Combination of CHAR and TEXTJOIN to Get a Line Break within Text. Let's see how to do this quickly using the TRANSPOSE function. For example, Excel allows the combination of 255 items, up to 8192 characters, using the CONCATENATE function. Important:In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. =CONCATENATE("Stream population for ", A2, " ", A3, " is ", A4, "/mile."). The delimiter is a comma (,) Did you follow the exactl steps for your data? It is applied to perform operations on the given data easily and efficiently. The result will come as John, (comma) Maths.. If FALSE, a space is displayed to represent the empty cell in the result. The second argument indicates whether we want to ignore the empty cells within the given range or not.