If you want to join multiple texts, numbers or function values into one cell, Excel has a function called CONCATENATE(). You can use the function is some very creative ways.
Simple text concatenation
The CONCATENATE function works like this: = CONCATENATE( text1, text2, text3). In the example below, text1 in in A1 cell, text2 is a space (if you specify text you have to put it in quotes) and text3 is in cell B1. Addind Good, a space and Morning will result in “Good Morning”
Concatenate text with numbers
Concatenate works with text and numbers just as well. The numbers will be joined to the text creating a larger text string.
Concatenate text with a formula value
CONCATENATE works with formula values too. For example, if in B2 we put the formula YEAR(NOW()) resulting in the current year of today’s date, then the CONCATENATE formula will add the text in A1 plus a space with the result of the formula.
The C1 formula is =CONCATENATE(A1,” “,B1)
Get creative with CONCATENATE
You can use the CONCATENATE formula in a creative way to create a random message and predict a result of a raffle.
The formula is:
=CONCATENATE(“The winner is “,VLOOKUP(RANDBETWEEN(1,6),C:D,2,FALSE))
It concatenates the text “The winner is ” with the lookup of a random number between 1 and 6. This formula uses the functions RANDBETWEEN and VLOOKUP.
Now, by just pressing F9 (calculate) the value gets randomized and the text shows the winner of the raffle.
