How to use a cell value in Sentence in Excel

Microsoft Excel is one of the most efficient and responsive software to perform mathematical calculations on numeric datasets. In addition to this, it also provides many features to handle the text data. There are many functions available to join, split and change the case of text data in Excel.

In this tutorial we’ll learn how to use a particular cell value with a predefined sentence in Excel. Let’s look at the dataset above, it has records of payments made against order IDs. We’ll create a dynamic sentence based upon the current financial status of the payment. The template sentence will be “The payment of the order # 1023 is in process / partially paid / paid”. The bold values in this template sentence will be variable and will be picked from respective cells. 

Let’s do it by following the steps mentioned below. We’ll use a customized dropdown list for financial status i.e., in process, paid, partially paid. If you want to know how to create a customized dropdown list then read this article.

We can create the dynamic sentence by using the following two methods,

• Using the ampersand “&” sign
• Using the TEXTJOIN function

Let’s explore both options in the steps below;

Method 1: Use Ampersand “&” sign

Step 1 – Create the required formula

• Choose an appropriate cell where we’ll create the meaningful dynamic sentence.
• Write the following formula 

=”The payment for the order “&A2&” is “&C2

Where A2 is the order code and C2 will hold the current financial status of the order.

Step 2 – Implement the formula and extend to all data range

• Now we can press enter to implement the formula in E2.
• This will give us the desired result in E2. Now, we’ll double click on the fill handle and it will fill the same formula to all data range. However, all new results will not have any information about the financial status, as it was empty. 
• Now, we’ll change the financial status as per actual and we’ll see that our dynamic sentence will automatically update accordingly as shown above.

Method 2: Use TEXTJOIN Function

Step 1 – Create the required formula with TEXT JOIN

• Choose an appropriate cell where we’ll create the meaningful dynamic sentence. TEXTJOIN requires a text delimiter as the first argument, which will be “ “ blank space in our case.
• Then you need to tell excel whether to ignore empty cells or not. We’ll choose to ignore empty cells.
• After that we can write the text explicitly using double quotes sign or we can use text or numeric values stored in any cells. We’ll use both options. We’ll use the explicit text “The payment for order” and cell references of A2 and C2 to use the number and text values from each respectively.
• Write the following formula 

=TEXTJOIN(” “,TRUE,”The payment for order”,A2,”is”,C2)

Where A2 is the order code and C2 will hold the current financial status of the order. 

Step 2 – Implement the formula and extend to all data range

• Now we can press enter to implement the formula in E2.
• This will give us the desired result in E2. Now, we’ll double click on the fill handle and it will fill the same formula to all data range. However, all new results will not have any information about the financial status, as it was empty. 
• Now, we’ll change the financial status as per actual and we’ll see that our dynamic sentence will automatically update accordingly as shown above.