How to Convert Text Box to Cell in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you the step-by-step procedures on how to convert a text box to a cell in Excel. You can use these steps for any type of dataset to quickly convert text boxes to cell values. Throughout this tutorial, you will also learn some important Excel tools and techniques that will be very useful in any Excel-related task.


How to Convert Text Box to Cell in Excel: with Easy Steps

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Employee Name, Location (TextBox), and Location (Cell). Although we may vary the number of columns later on if that is needed.

excel convert text box to cell


Step-1 Creating Base Dataset

In this first step, we will create the starting Excel dataset for our operation. Follow the steps below to do this.

  • First, insert the following columns with the sample employee names.
  • Then, insert some text boxes and enter the locations as in the image below.

creating base data to convert a text box to a cell in Excel

Read More: How to Insert Scrolling Text Box in Excel


Step-2 Inserting VBA Code

Now, we have to insert the necessary VBA code that will convert the text box to a cell. Let us see how to do this.

  • For this method, go to the Developer tab and select Visual Basic.

opening vba window to convert a text box to a cell in Excel

  • Now, select Insert in the VBA window and click on Module.

inserting module to convert a text box to a cell in Excel

  • Next, type in the formula below in the new window:
Sub ConvertTextBoxToCell()
    Dim Sh_xRg As Range
    Dim Sh_xRow As Long
    Dim Sh_xCol As Long
    Dim Sh_xTxtBox As TextBox
    Set Sh_xRg = Application.InputBox("Select a cell):", "Convert Text Box to Cell ", _
                       ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
    Sh_xRow = Sh_xRg.Row
    Sh_xCol = Sh_xRg.Column   
    For Each Sh_xTxtBox In ActiveSheet.TextBoxes
        Cells(Sh_xRow, Sh_xCol).Value = Sh_xTxtBox.Text
               Sh_xRow = Sh_xRow + 1
    Next   
End Sub

vba code to convert a text box to a cell in Excel

Here, we are creating an input box to take user input using the Application.InputBox method. Next, ActiveWindow.RangeSelection method represents cells that the users select.

Read More: How to Add a Dynamic Text Box in Excel


Step-3 Running VBA Macro

Once we have inserted the VBA code inside the module, we can run this code directly from the Macros option. Here are the steps to achieve this.

  • To begin with, click on the Developer tab again and select Macros.

opening macros list to convert a text box to a cell in Excel

  • Then, select the macro name and click on Run.

running the macro to convert a text box to a cell in Excel

  • Consequently, this will show a popup window.
  • Here, click on the cell in which you want to enter the data and click OK.

  • As a result, this will convert the text box values into cell values as we wanted.


How to Convert Cell to Text Box in Excel

If you want to perform the reverse operation of what we did previously, then you can follow this method to quickly convert a cell to a text box in Excel.

Steps:

  • To start with, click on the icon in the top left corner of the worksheet, as in the image below.
  • Next, click on the Fill Color drop-down above.

  • Then, select any color as the Fill Color.

  • Now, click on any of the cells to convert it to a text box.

  • After that, press Ctrl+1 to open Format Cells.
  • Then, go to the Fill tab and select the color, White.

  • Next, go to the Border tab and set the top and left border as the color black.
  • Similarly, set the bottom and right border as the color white.
  • Now, click on OK.

  • Finally, this should format the cell as a text box.

Read More: How to Anchor Text Box in Excel


How to Create Dynamic Text Box in Excel

In this final method, we will see a very important feature that Excel has, which allows us to create dynamic text boxes instead of hard-coding the data into them.

Steps:

  • Firstly, select cell B5 and go to the Data tab at the top of the screen.
  • Now, click on Data Validation under Data Tools.

  • Next, in the Data Validation window, set List as the Allow option from the drop-down.
  • Then, type the employee names in the Source field as in the image below and click OK.

  • Immediately, this will insert a drop-down list inside cell B5.

  • Now, click on cell B7 and go to the Insert tab thereafter.
  • Then, click on Text Box under Text.

  • After that, type in the following formula inside the text box:
=$B$5
  • Then, press Enter.

  • Finally, this will create a dynamic text box.

 


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to convert a text box to a cell in Excel. As you can see, it involves quite a few steps to achieve this. So carefully follow these while applying them to your dataset. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.


Related Articles


<< Go Back to TextBox | 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.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo