Download beer

The Concatenate Function in Excel is mostly used to join or combine contents of multiple cells into a single cell. For example, if you have a worksheet containing the First Names in one Column and the Second Names in another column, you can use the Concatenate Function to combine the First and Second Names into a third column. Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.

• Format Text In Concatenate Excel
• Concatenate Multiple Cells In Excel
• Concatenate Excel Format

Format Text In Concatenate Excel

I am trying to figure out the logic of a formula that would Concatenate the value of some cells based on the value of others. I am generating a dramatic script from a csv document. I have a layout like this:

INSTANCE	LINE NUMBER	ROLE	LINE
1	22	Joe	Hello.
1	22	How are you today?
2	31	Patty	Okay
3	33	Joe	I'm glad
4	37	Patty	I guess.
4	37	I'll see you around.
4	37	One of these days.

I want to create a way to combine into a single cell all the unique values in the LINE column for each unique INSTANCE. So it'd look like this:

INSTANCE	LINE NUMBER	ROLE	LINE	COMBINED
1	22	Joe	Hello.	Hello. How are you today?
1	22	How are you today?
2	31	Patty	Okay	Okay
3	33	Joe	I'm glad	I'm glad
4	37	Patty	I guess.	I guess. I'll see you around. One of these days.
4	37	I'll see you around.
4	37	One of these days.

Concatenate Multiple Cells In Excel

I'm not sure if this is done via Macro or Formula, if possible formula would be best. I do not have access to the TEXTJOIN function for what it's worth. Any help would be appreciated.

For a long time, the only option to combine text strings has been using the CONCATENATEfunction, which does nothing more than what the ampersand (&) operator does. Recently, Microsoft has released the CONCAT and TEXTJOIN formulas with Excel 2016. In this guide, we are going to focus on the CONCAT function and look at some of the differences between the CONCAT vs CONCATENATE functions.

Let’s begin with the older formula (CONCATENATE) first and see why you might want to prefer using the CONCAT function instead.

CONCATENATE Function

The CONCATENATE function simply merges its parameters, and returns a string value. However, you can do the exact same thing by using an ampersand (&) character between the strings or texts. CONCATENATE function doesn’t provide any advantage for the most part, unless you need to merge 100 strings and do not want to use the ampersand (&) character 99 times. By the way, you still need to select each cell one-by-one because the function doesn’t support ranges.

Syntax

Example

Below is an example of how you can use the CONCATENATE function. Supply each string as an argument and the function will merge them.

The next part in our CONCAT vs CONCATENATE guide is the upgraded version, the CONCAT function.

CONCAT Function

CONCAT function is one of the relatively newer text merging functions in Excel. Microsoft states that the CONCAT function is intended as a replacement for CONCATENATE function. Although Microsoft Excel continues to support the older function, they suggest that you should use the CONCAT function instead.

The CONCAT function addresses the biggest flaw of the CONCATENATE function, not being able to select a range of strings. Unlike its predecessor, the CONCAT function can merge all strings in a range. This way, it saves you from selecting each cell individually.

Concatenate Excel Format

Syntax

Example

The example below is using the same data from the previous example. You can see that the formulas here are considerably shorter. However, you still need to add separators manually.