How to Calculate the Data in the Same Cell with Excel Formula

Normally we use Excel formulas to calculate the data from different cells, but in some cases we may enter mulitple numbers with operational symbols in the same cell and want to calculate the result, can we still use formula to work it out?

Look at this table, There are multiple numbers and operation symbols in each line of column A, you can’t use formula to calculate them directly. But you can still try these effective methods below.

Method 1: Define Name

The first one is using a less frequently used feature in Excel called Define Names.

1. Go to Formulas tab, click Define Name and choose Define Name…

2. Enter =EVALUATE(Sheet10!A1) in the textbox beside Refers to.

Sheet 10 refers to the current sheet you are editing; A1 refers to the first cell you want to calculate. You can specify the area to define according to your situation.

3. Give it a Name that easy to remember. Then click OK to save it.

4. Now click the cell B1, and click Use in Formula under Formulas tab. Choose the name you just defined. In my case it’s SUM.

5. After inserting the Define Name, press [Enter] to work it out. You can see the calculate result in B2 now.

6. Then you can use fill handle to apply this operation to multiple cells and do all the calculations quickly in column A.

Method 2: Find and Replace

The second method is making use of Find and Replace feature in Excel.

1. Enter =”z=”&A1 in B1 to copy the content of A1 plus the extra z=.

2. Use fill handle to apply this operation to other cells in column B.

3. Select column B, and click Paste button to select Paste Special… in the drop-down list.

4. Check Values in Paste Special window and hit OK. Now you’ve copied the content again in column C.

5. Select column C, press [Ctrl+H] to evoke the Find and Replace window. Input z in Find what and hit Replace All directly without inputting anything in Replace with.

6. Then you’ll find the result of calculations magically show in column C.

Method 3: Split the Cells

Of course, you can also split the cells at first, and then use formulas to calculate them.

For your reference, I’ve shared the details in another post How to Split Text from One Cell into Multiple Cells in Excel. Just follow the steps to split the data in a cell or a column, then you can use formulas normally.