Excel is present with hundreds of Functions but Today we’ll learn about one of the great Excel Functions “CONCATENATE”. It is used for joining values of two or more then two cells. It can be used with the most common Excel Function “IF” and other String & Math and Trig Functions such as: LEFT, RIGHT, MID, REPT,CODE, TRIM, LEN, EXACT, UPPER, LOWER, PROPER, SUM, AVERAGE, COUNT, PMT, IMPT, PV and etc..
We will start with a very simple example.
- Insert your data table
- Choose a cell where you want to join the values of different cells.
- Start with (=) sign then type “CONCATENATE” without qoutes.
- Open brackets after typing the function Concatenate.
- Select the values and place “comma” after each word.
- 1=CONCATENATE(text1, [text2],[text3],...)
- Look at the example below:
In the above Example we have three words in different cells (N4, O4 and P4) and we have joined them through the CONCATENATE Function in Cell (Q4).
If you concentrate in the example above we don’t need any space between words in Cell (Q4), but in other cells below that we can see that there is a SPACE between each name or word. So while working with CONCATENATE Function we have to be careful about the SPACES.
If you want to insert a space between two words, You’ve to use (,” “,) after selecting a cell in CONCATENATE Function.
You can add spaces after those cell values which you’re going to join them, in there cells.
Look at the Example Below:
- In example below, I’ve mixed and joined “Thank You”, “Please Pay” and Customer Name “Ali” and have used CONCATENATE with IF function.
- In case if the PAID AMOUNT is less then TOTAL PRICE, “Please Pay + Customer Name” will be visible.
- and if the PAID AMOUNT is equal to 0, then “Thank You + Customer Name” will be visible.
WANT MORE EXCEL FUNCTIONS?
Best of Luck!