How to Display Text from Another Cell in Excel (4 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

When using Excel, it is a frequent need for us to display text to a cell from another cell. It is tiring and time-consuming to insert the same values or texts into different cells over and over. In this article, I will show you 4 suitable ways to display text from another cell.


How to Display Text from Another Cell in Excel: 4 Suitable Ways

Suppose you have a fixed grading system for an institution.

Grading System Dataset

And, you have a dataset of 10 students’ obtained marks and grades per the institution’s grading system.

Student Report Card Dataset

Now, you want to display text from another cell like displaying a specific student’s grades, formulas, looking up definite values, etc. Read the article carefully below to accomplish these tasks and display text from another cell in Excel.


1. Use Cell Reference to Display Text

Say, you want to display Tim’s grade and remarks upon the grade in other cells. You can use a cell reference to display text from another cell in this regard. Go through the steps below to achieve this.

1.1 From Same Worksheet

📌 Steps:

  • Tim’s grade is in cell E7 of the active worksheet.
  • So, click on cell G5 and insert the following formula.
=E7
  • Afterward, hit the Enter button.

Using Cell Reference to Display Text from Another Cell

As a result, you will get the grade of Tim in the G5 cell as per your desire.

Read More: How to Reference Text in Another Cell in Excel


1.2 From Different Worksheets

📌 Steps:

  • Now, the remark for Tim’s grade is in cell E8 of the Grading System worksheet.
  • So, click on cell G8 and insert the following formula.
='Grading System'!E8
  • Subsequently, press the Enter button.

Using Cell Reference to Display Text from Another Worksheet

Thus, you will get the remark for Tim’s grade in your desired cell.

Read More: How to Use Variable Row Number as Cell Reference in Excel


2. Use Named Range to Display Text from Another Cell

You can also use named ranges for accomplishing the previous task. Say, you want to display Tim’s grade in cell G5. Follow the steps below to achieve this by using named ranges.

📌 Steps:

  • First, you need to create a named range for Tim’s grade.
  • To do this, click on cell E7 >> go to the Formulas Tab >> Defined Names group >> Define Name option.

Access Define Name Tool

  • As a result, the New Name window will appear.
  • Following, write _tim_grade in the Name: text box and click on the OK button.

New Name Window

  • Afterward, click on cell G5 and insert the formula below.
=_tim_grade
  • Subsequently, press the Enter button.

Using Named Range to Display Text from Another Cell

Thus, you will get Tim’s grade displayed in the required cell.

Read More: How to Reference Cell by Row and Column Number in Excel


3. Display Formula Text from Another Cell’s Formula

Now, sometimes it might happen that you need the formula of a cell in text. Suppose, you have automated the grades of students according to their obtained marks. Now, you want to display the formula in another cell. Follow the steps below to do this.

📌 Steps:

  • At the very beginning, click on cell B17.
  • Subsequently, insert the following formula containing the FORMULATEXT function and press the Enter button.
=FORMULATEXT(E5)

Using FORMULATEXT Function to Display Formula from Another Cell

Consequently, the formula for cell E5 will be displayed in cell B17.

Read More: How to Find and Replace Cell Reference in Excel Formula


4. Use Excel Functions to Display Specific Parts of Text from Another Cell

You can also use the LEFT, MID, or RIGHT functions to display specific parts of a text into another cell.

4.1 Show Left Part of a Text with LEFT Function

Say, you want to display the first 2 letters of cell C10. Follow the steps below to accomplish this.

📌 Steps:

  • At the very beginning, click on cell G5.
  • Following, insert the following formula and hit the Enter button.
=LEFT(C10,2)

Using LEFT Function to Display Specific Part of Text from Another Cell

Thus, you will see the first 2 letters shown in cell G5.

Read More: How to Use OFFSET for Cell Reference in Excel


4.2 Show Middle Part of a Text with MID Function

Now, say you want to show only the 3 middle letters starting from the 3rd letter of cell C10 on cell G5. Follow the steps below to do this.

📌 Steps:

  • First, click on cell G5.
  • Subsequently, insert the following formula and hit the Enter button.
=MID(C10,3,3)

Using MID Function to Display Specific Part of Text from Another Cell

As a result, you will get the 3 middle letters starting from the 3rd letter of cell C10 on cell G5.


4.3 Show Right Part of a Text with RIGHT Function

Besides, you can also show the specific right part of a text by following the steps below. Say, you want to show the last two letters of cell C10.

📌 Steps:

  • Initially, click on cell G5 and insert the formula below.
=RIGHT(C10,2)
  • Following, press the Enter button.

Using RIGHT Function to Display Specific Part of Text from Another Cell

Thus, you will get your desired result.


How to Combine and Display Text from Multiple Cells in Excel

Now, you might need to combine two or more cells and display them in another cell. Say, you want to combine the first student’s name and his obtained mark with a hyphen (-). Go through the following ways to accomplish this.

  • Use the Ampersand (&) Operator

You can use the Ampersand (&) operator to achieve your desired result. Follow the steps below to dos this.

📌 Steps:

  • At the very beginning, click on cell G5.
  • Afterward, insert the following formula.
=C5&" - "&D5
  • Subsequently, press the Enter button.

Using the Ampersand (&) Operator to Combine Multiple Cells

Consequently, you will get your desired result displayed on cell G5.

  • Use the CONCATENATE Function

You can also use the CONCATENATE function to accomplish the previous task. Follow the steps below to perform this.

📌 Steps:

  • First, click on cell G5.
  • Next, insert the following formula.

=CONCATENATE(C5," - ",D5)

  • Finally, hit the Enter button.

Using CONCATENATE Function to Combine two Cells

Thus, you will see that you have achieved your desired target.

  • Use the TEXTJOIN Function

Moreover, you can use the TEXTJOIN function to combine and display text from other cells. Go through the steps below to do this.

📌 Steps:

  • Initially, click on cell G5.
  • Subsequently, insert the following formula and hit the Enter button.
=TEXTJOIN(" - ",TRUE,C5,D5)

Using TEXTJOIN Function to Combine two Cells

Consequently, you will get your desired result.


How to Display Particular Text in Excel Based on Values of Other Cells Using Formula

Apart from all the previous tasks, you can also display particular text based on the values of other cells using formulas.

  • Use IF Function

Say, you want to automate the grading system. That is, you want to display grades for every student based on the grading system dataset. Go through the steps below to do this.

📌 Steps:

  • Firstly, click on cell E5.
  • Afterward, insert the following formula containing the IF function based on the grading system dataset.
=IF(D5>=90,"O",IF(D5>=80,"A+",IF(D5>=70,"A",IF(D5>=60,"B+",IF(D5>=50,"B",IF(D5>=40,"C","F"))))))
  • Finally, hit the Enter button.

Using IF Function to Display Result from Other Cells

Afterward, you can use the fill handle feature to get the grades for all students based on the grading system dataset.

  • Use INDEX-MATCH Formula

Furthermore, you can find a particular student’s grade from the student report card dataset by using the combination of INDEX and MATCH functions. Follow the steps below to do this. Say, you want to find the grade of the student whose name is on cell D16.

📌 Steps:

  • First and foremost, click on cell D17.
  • Afterward, insert the formula below.
=INDEX(C5:E14,MATCH(D16,C5:C14,0),3)
  • Subsequently, hit the Enter button.

Combining INDEX & MATCH Functions to Display Result from Other Cells

Thus, you will find Tim’s grade from the report card dataset.


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

So, in this article, I have shown you 4 suitable ways to display text from another cell in Excel.  I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.


Related Articles


<< Go Back to Cell Reference in ExcelExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo