 ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# Calculate Value In A Single CEll

0

What is the formula in excel to show the result below

Example: 049-054 and the result 6

Thank you..

### Discussion

R

John_Ru (rep: 5147) Aug 11, '22 at 2:13 am

1

R

If cell A3 contains 049-054 (as your file does) and that represents a series of numbers, then the count of numbers in that series is obtained using the text manipulation functions LEFT and RIGHT as follows:

``=RIGHT(A3,3)-LEFT(A3,3)+1``

That extracts the 3 digits at the end of the string and subtracts those at the start then adds 1 to get the number of items (e.g. 049-054 gives 6 and 072-072 would give 1).

It assumes that the larger number is always at the end of the string. If not, you could use what follows...

You can use Excel's ABSOLUTE function (ABS) to return the absolute value of a number. That converts negative numbers to positive numbers (while positive numbers remain the same).

If cell A1 is 49 and A2 is 55, then:

``=ABS(A1-A2)``

will give 6.

If both are strings (like '049) then use:

``=ABS(VALUE(A1) -VALUE(A2)) ``

### Discussion

Was that what you wanted, R? (I was surprised to get no response)
John_Ru (rep: 5147) Aug 22, '22 at 4:39 pm