Welcome to Excel Jet Consult blog post. In this blog post, we will learn 5 ways to combine text string in Excel Worksheet. Let’s get started
SAMPLE DATA
For this exercise, we shall be using the dataset below which is designed as an HR Data
FLASH FILL
Our first method is to use the flash fill feature in Excel. Succinctly, the Flash Fill automatically fills data when it senses a pattern. To use the flash fill, we need to give Excel enough pattern which it needs to recognise and follow suit
- In cell D2, enter the combined Last and First Name from cells B2 and C2 respectively.
- In cell D3, enter the combined Last and First Name from cells B3 and C2 respectively.
- In cell D4, start typing the first few letters of the Last name in cell B4. Viola, a ghost list is visible which is the pattern that excel recognised based on the examples provided above.
- Click Enter to accept the result.
There you have the first methods
TEXTJOIN Function
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
So, we want to use the TEXTJOIN function to combine the Last and the First name in column E
- Therefore, in cell E2, execute the formula: =TEXTJOIN(” “,TRUE,B2:C2)
- Click Enter and Copy down the formula using fill handle
In the above formula, the delimiter is space in between the double quotes, and we used TRUE to ignore empty cells. Finally, we provided the text in B2:C2. Voila, we have the combined last and first name as seen in the screenshot below.
CONCATENATE FUNCTION
The CONCATENATE function joins two or more text strings into one string.
Syntax:
CONCATENATE(text1, [text2], …)
So, we want to use the CONCATENATE function to combine the Last and the First name in column F
- Therefore, in cell F2, execute the formula: =CONCATENATE(B2,” “,C2)
- Click Enter and Copy down the formula using fill handle
In the above formula, the text1 argument is the value that cell B2 will return, while the text2 is the space in between double quotes and the text3 is the value that cell C2 will deliver. Voila, we have the combined last and first name as seen in the screenshot below.
CONCAT FUNCTION
Just like the CONCATENATE function, the CONCAT function can be used to combine data from multiple cells into a single cell.
Syntax:
CONCATENATE(text1, [text2], …)
We want to use the CONCATENATE function to combine the Last and the First name in column G
- Therefore, in cell G2, execute the formula: =CONCATENATE(B2,” “,C2)
- Click Enter and Copy down the formula using fill handle
In the above formula, the text1 argument is the value that cell B2 will return, while the text2 is the space in between double quotes and the text3 is the value that cell C2 will deliver. We have the combined last and first name as seen in the screenshot below.
AMPERSAND Formula
AMPERSAND Formula can be used to combine string, numbers and date values from multiple cells into one cell using the & symbol
We want to use the AMPERSAND formula to combine the Last and the First name in column H
- In cell H2, execute the formula: =B2&” “&C2
- Click Enter and Copy down the formula using fill handle
In the above formula, we referenced the value in cell B2 and then used the & symbol. Next, in order to factor the space in between the names to make it readable, we used the space in between double quotes and then reference the value in cell C2. We have the combined last and first name as seen in the screenshot below.
In conclusion, the above are 5 ways in which we can combine text string in Excel worksheet. Although, there is another way of doing the above using the Power Query