CONCATENATE

Combines several text strings into one string.

Syntax:

CONCATENATE(text1, text2, ... text30)

returns up to 30 text strings text1 - text30, joined together.

text1 - text30 may also be single cell references.

The ampersand operator & may also be used to concatenate text in a formula, without the function.

Example:

CONCATENATE("al", "tog", "ether")

returns altogether.

"al" & "tog" & "ether"

also returns altogether.

CONCATENATE(A1, A2)

where cell A1 contains key and cell A2 contains board returns keyboard.

CONCATENATE(ROUND(RAND()*10), ROUND(RAND()*10), ROUND(RAND()*10))

returns the text string xyz where x, y, z are three randomly independent digits, while:

REPT(ROUND(RAND()*10), 3)

returns nnn where n is a random digit repeated three times.

Here Zapof function converts each number given by ROUND(RAND()*10)) to text before concatenating.

CONCATENATE("Print Date: " , TEXT(TODAY(),"dddd, dd mmmm yyyy"))

returns Print Date: Thursday, 9 December 2021 (assuming today's date was Dec 9, 2021). This example serves to demonstrate that you can combine functions in quite complex ways. This example is made up of the date function TODAY() which returns the current date as a number (NOTE: Dec 9, 2021 is represented as 44527) and the text function TEXT() which allows you to format the numeric representation of the date in a useful textual format.

Application:

Creating a Standardized Product Code

Imagine you are working for a company that sells various electronic devices. To keep track of your inventory, you want to create a unique and standardized product code for each item. This code should be a combination of the product category, the model number, and the color of the device.

Table: Products

	Product ID	Product Category	Model Number	Color	Product Code
	A	B	C	D	E
1	101	Laptop	Alpha-7	Midnight Blue	Laptop-Alpha-7-Midnight Blue
2	102	Smartphone	Zeta-X	Stellar White	Smartphone-Zeta-X-Stellar White
3	103	Tablet	Pro-Tab	Black	Tablet-Pro-Tab-Black
4	104	Smartwatch	Chronos-5	Red	Smartwatch-Chronos-5-Red

The CONCATENATE Formula

We'll use the CONCATENATE function to join the text strings from the Product Category, Model Number, and Color columns.

We'll use a hyphen (-) as a separator to make the final code easy to read.

For the first row (Product ID 101), the formula would be:

CONCATENATE(B1, "-", C1, "-", D1)

B1 is the cell containing "Laptop".
"-" is the text string for the separator.
C1 is the cell containing "Alpha-7".
"-" is another separator.
D1 is the cell containing "Midnight Blue".

This formula will produce the following output:

Laptop-Alpha-7-Midnight Blue