Combining Text and Values in Excel

Let`s understand how to easily combine text and values in Excel. Firstly, you will learn how to use the functions CONCATENATE and CONCACT to connect text strings from 2 or more cells. Then, you will learn the important Ampersand Technique, which is also used to join strings in Excel.

The functions CONCATENATE and CONCAT allow you to concatenate a list or range of text strings from 2 or more cells. Let’s apply these functions in this example, which shows the bonus payment calculation for each employee of a firm.

Here, let’s assume the bonus payment is calculated as 3 times the monthly salary, multiplied by the percentage of goals achieved. So, insert that calculation in cell E3 and expand it down to the entire range.

Now, let`s check how we can combine text and values with the functions CONCATENATE and CONCAT.

Combining Text and Values with the Function CONCATENATE

Follow the steps below:

1. So, insert the function CONCATENATE in cell H3. Select each argument of this function individually, which are the text strings you want to combine and close brackets.

2. Finally, press ENTER. All strings (i.e. text and values) will be concatenated.

Combining Text and Values with the Function CONCAT:

To avoid the manual process of selecting arguments individually, Excel developed an updated function called simply CONCAT, which is available for Excel 2019 and higher. To use it, follow these steps:

1. First, insert the CONCAT function in cell H3

2. Then, select the entire range at once. The result will be the same as before with the CONCATENATE function.

Function CONCAT with Delimiters

Now, in case you also need to have a clear separation of your text and values, you then need to work with “delimiters”. In this simple example, we will only combine the variables Name and Salary for sake of simplification. Follow the steps below:

1. Firstly, insert the CONCAT function in cell H3 again.

2. The first argument must be the name “John”, so select cell B3 in this case.

3. Then insert the second argument, which is the “delimiter” itself. This one needs to be a dash surrounded by spaces, all within quotation marks (“ – ”).

4. Finally, insert the last argument. In this case, it’s John’s salary from cell C3. Close brackets.

5. Press ENTER.

Functions CONCAT + TEXT

It now looks better but note that the thousand separator is missing. That’s because we combined text and values by ignoring the text format. If that’s important to you, we can adapt the format of the number with the TEXT function.

Now, let’s edit the last argument of this function:

1. So, before cell C3, insert the function TEXT.

2. Insert the first argument, which will be the value from cell C3 itself.

3. Insert the second argument, which is the “format text”. In this example, we need as argument “$ 0,000”. 

4. Close brackets twice as we have 2 functions at this stage.

5. Press ENTER.

It looks much better now. At last, expand this function down to fill the rest of the cells.

Ampersand Technique (Secret weapon to combine text and values in Excel)

If you want to combine text and values without using the functions CONCATENATE and CONCAT, you can simply use the ampersand symbol (&). All you need to remember is to use quotation marks for any text you have between the strings.

Follow these steps:

1. Firstly, delete the functions we just inserted.

2. Then in cell H3, insert “=” to create a formula.

3. After that, select cell B3, which contains the first string.

4. Now, insert the ampersand symbol to connect the sentence and introduce the opening quotation mark.

5. You can then add a comma, a space, and write the sentence “, your salary is ”. Make sure you add a space and introduce the closing quotation mark at the end.

6. At last, insert the ampersand symbol again to connect the last string, which is cell C3.

If we press ENTER here, the number won’t be correctly formatted. To fix that:

1. Insert the function TEXT(C3,”$ 0,000”) after the last ampersand symbol to convert the number into the correct currency format as before.

2. Press ENTER and expand the formula down.

The results (i.e. combination of text and values) are the same as the ones obtained using the CONCATENATE and CONTACT functions.

Keep this tip in mind because it’s more straightforward. All you need to remember is to use the ampersand to extract the values from other cells and to write your sentences using quotation marks.

Standard Messages in Excel

If you understood how to build a small sentence combining value and text, why not build a whole paragraph? This might be useful in case you need to prepare standard messages in your spreadsheet based on the same types of inputs. There are 3 steps you need to perform.

Step 1: Creating a standard message:

In this example, let’s work with a message which is based on the variables Name, Salary, % Goals, Bonus, and Bonus Payment Date.

This is the example of a standard message for the employee named John:

“Hi John. You achieved 83% of your goals this year. Based on your monthly salary of $ 7,500 and a salary multiplier of 3x, your bonus this year will be a total of $ 18,675. Expect this payment to be made by 31-Mar-24.”

1. So, copy the standard message and go to your Excel file. Paste it in cell B12.

2. Then to display the message across the area with the blue background, select the range, go to the home menu, and click “Justify”.

Step 2: Getting the correct format of the 5 key parameters with the function TEXT. 

The idea is to create a dynamic message, by linking the keywords of this message to the correct staff data available in the table. To do that, I created a simple INDEX / MATCH function in row 8 just so that anytime a staff name is selected in cell B8, the respective information related to salary and bonus are returned in row 8.

Note that, as it stands, we only have the “values” pulled out by these functions. So, we also need the correct “format” displayed here which can be done in row 9.

1. So, select cell C9 and insert function TEXT.

2. Then, link the argument “value” to cell C8.

3. Next, the text format of the argument needs to be a dollar currency ($) with a thousand separator. Write it using quotation marks.

4. Now, close the brackets and press ENTER.

5. After that, repeat the process in cell D9, where the “text format” needs to be a percentage with double digits.

6. Cell E9 needs to have the same format as cell C9, so copy the function and paste it.

7. Finally, for the payment date, insert the TEXT function to add the date format, which is day, month, and year separated by a dash (i.e. “dd-mmm-yy”). Write it in quotation marks.

Note: Ignore the alert that appears in the Salary cell as we adapted each function on purpose this time.

Step 3: Linking the key parameters (text and values) to your standard message:

Now, we can combine these keywords into the standard message. Whenever you are building a message like this, I recommend you first transform each of these sentences into a formula with a text value. This makes your work a lot easier. Here’s how:

1. So, press F2 in cell B12 and insert =.

2. Then insert the opening quotation mark and then press CTRL + Arrow Down to move the end of the sentence.

3. At last, insert the closing quotation marks.

By doing this, we have a sentence inside a formula. Then, repeat the same process to all sentences to end up with 3 formulas.

Let’s now edit this formula in cell B12:

1. So, insert quotation marks just before the word “John”. This tells Excel that everything up to this point is a text value.

2. Now delete the word “John” to insert an ampersand and connect it to cell B9 which at this stage is the value “John”.

3. After that, insert ampersand again to connect it with the remaining part of the sentence.

4. Finally, what comes next is another text string, so make sure you insert another quotation mark and press ENTER.

Conclusion:

By doing this you have turned the staff name of your text into a dynamic value that can change based on each name we choose in cell B9. After that, repeat this process to all dynamic values of all 3 sentences.

Now, bear in mind that after you select a different staff name, all these variables located in row 8 will change. As a consequence, the standard message in the blue area will also change.