excel combine text from two cells into one

#Excel combine text from two cells into one how to
#Excel combine text from two cells into one code

Here is an example of how the TEXTJOIN function works: – this is an optional argument where you can specify up to 252 arguments that could be text strings or cell ranges.It could be a text string, or array of strings, such as a range of cells. text1 – this is the text that needs to be joined.ignore_empty – if this is TRUE, it will ignore empty cells.You can manually enter this or use a cell reference that has a delimiter. delimiter – this is where you can specify a delimiter (separator of the text).TEXTJOIN(delimiter, ignore_empty, text1,, …) It also allows you to specify a delimiter. This function, as the name suggests, can combine the text from multiple cells into one single cell. In Excel that comes with Office 365, a new function – TEXTJOIN – was introduced. CONCATENATE Excel Ranges Using TEXTJOIN Function (available in Excel with Office 365 subscription) Now you can use this function as any regular worksheet function in Excel.

#Excel combine text from two cells into one code

Copy paste the above code in the module code window.In the VB Editor, right-click on any of the objects and go to Insert and select Module.Go to the Developer Tab and click on the Visual Basic icon (or use the keyboard shortcut Alt + F11).Here are the steps to copy this code in Excel: Here is the VBA code that will create this custom function to combine multiple cells: Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As StringĬONCATENATEMULTIPLE = Left(Result, Len(Result) - 1) CONCATENATE Excel Ranges (Using VBA)īelow is an example of the custom function I created using VBA (I named it CONCATENATEMULTIPLE) that will allow you to combine multiple cells as well as specify a separator/delimiter. If you want, you can use other separators such as a comma or hyphen. Note that in this case, I used a space character as the separator (delimiter). Based on your regional settings, you can also try =A1:A5 (instead of =TRANSPOSE(A1:A5)).Go to formula bar and enter =TRANSPOSE(A1:A5)&” “.Here are the steps to concatenate an Excel Range with space as the separator (as shown in the pic): CONCATENATE Excel Ranges (With a Separator) Note that since we use any delimiter (such as comma or space), all the words are joined without any separator. Add =CONCATENATE( to the beginning of the text and end it with a round bracket ).ĭoing this would combine the range of cells into one cell (as shown in the image above).Remove the curly brackets from both ends.Select the entire formula and press F9 (this converts the formula into values).Go to formula bar and enter =TRANSPOSE(A1:A5).Select the cell where you need the result.Here are the steps to concatenate an Excel range without any separator (as shown in the pic): CONCATENATE Excel Range (Without any Separator) Note: If you’re using Excel 2016, you can use the TEXTJOIN function that is built to combine multiple cells using a delimiter.

#Excel combine text from two cells into one how to

In this tutorial, I will show you how to combine multiple cells by using the CONCATENATE function. While you can enter the reference one by one within the CONCATENATE function, it would not work if you enter the reference of multiple cells at once (as shown below):įor example, in the example above, while the formula used is =CONCATENATE(A1:A5), the result only shows ‘Today’ and doesn’t combine all the cells.

=CONCATENATE(A1&A2) will give you the result as GoodMorning (where A1 has the text ‘Good’ in it and A2 has the text ‘Morning’.

=CONCATENATE(“Good”,” “, “Morning”) will give you the result as Good Morning.

=CONCATENATE(“Good”,”Morning”) will give you the result as GoodMorning.

In its basic form, CONCATENATE function can join 2 or more characters of strings.

If you’re not using Office 365, keep reading. If you’re using Excel with Office 365 subscription, I suggest you click here to skip to the part where the TEXTJOIN function is covered.