Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

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

Date:2019-10-11 Author:Cordelia

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.

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

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…

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

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.

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

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.

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

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.

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

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=.

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

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

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

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.

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

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.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *