1

I have a string of a numeric calculation in one cell. The input for the cell is ="1+2+3". So the cell is displayed like this:

Column A
1+2+3

How can I get the numeric outcome of the calculation (=6)?

I tried using the function VALUE but it gives an error because it expects a string of a number, not a string of a calculation with operators.

I also tried manually setting the data format of the cell but it didn't change (automatically switched back).

How can I solve this?

Thank you!

Improve this question
5
  • Welcome to Ask Different. :) I hope you find this site a valuable source of info/help. Unfortunately, your question isn't totally clear in terms of what you're trying to do. At first reading it comes across like you just need to add the = to the beginning of your string so that Numbers sees it as a formula, but I'm guessing that's not what you're really asking. Can you clarify by editing your question.
    – Monomeeth ♦
    Commented Sep 5, 2023 at 8:12
  • @Monomeeth thanks for your kind comment. :) I edited my question, I hope it's clearer now. The = is already part of the cell's input.
    – Philea
    Commented Sep 5, 2023 at 8:30
  • 1
    Yes, that is clearer now, thanks. Out of curiosity, why do you need to enter this as a text string (i.e. ="1+2+3") instead of just using digits (i.e. =1+2+3)?
    – Monomeeth ♦
    Commented Sep 5, 2023 at 8:35
  • @Monomeeth Yeah, fair question. I'm coming from trying to convert text inputs like abc - where "a" stands for 1, "b" stands for 2 and "c" stands for 3 - into the sum of what they stand for, in this case 6. I used the SUBSTITUTE function to convert the string "abc" into the string "1+2+3" but don't know how to proceed to make it calculable. Probably there's a more elegant way to do this...
    – Philea
    Commented Sep 5, 2023 at 8:46
  • So the actual question is how to convert “abc” to sum of values represented by letters? Where does these letters coming from? Is there any stage where they are single letters (and later joined). Do capital letters have different value?
    – Aivar Paalberg
    Commented Sep 5, 2023 at 10:12

2 Answers 2

Reset to default
1

Thanks to everyone for your helpful replies!

In the meantime I found a workaround that solves my problem. I'll post it here according to my example "1+2+3" for consistency. It can be adjusted to my use case of a string of characters "abc".

enter image description here enter image description here

Sorry for the German function names :-/. Below are the names of the functions in English:

German English
WENN IF
LÄNGE LENGTH
LINKS LEFT
RECHTS RIGHT
Improve this answer
3
  • Thx for sharing. Pls note that in the 1st row, REGEX.EXTRACT function could help you get each number to a different cell more easily. If you did that, you would not need the formulas in the 2nd row either; you could just sum the cells in the first row then. Also note that you can convert a string consisting of numerals to a proper number simply by using the VALUE function which I think you can use instead of the formulas in the 2nd row.
    – Alper
    Commented Sep 5, 2023 at 12:48
  • @Alper thanks for adding the translations. Yes, you're right, the VALUE function would have been the better choice. Unfortunately I can't try your suggestion with the REGEX function, maybe my Numbers version is too old. But I'm glad we found something that works. Have a good day!
    – Philea
    Commented Sep 5, 2023 at 13:19
  • Yes, Regex functions in Numbers were introduced relatively recently, starting with Numbers 10.1 which was released in 2020. However, assuming you have an Apple ID, you can access those functions, or at least try them, and some other recent Numbers features using Numbers for iCloud which you can access through icloud.com in case of you really need them within Numbers.
    – Alper
    Commented Sep 6, 2023 at 8:17
1

You seem to be looking for the opposite of the FORMULATEXT function of Numbers such as the EVALUATE method which is available in Excel VBA and can be made available in Excel itself through Excel's own Name Manager (see this example). Sadly such a function or method doesn't exist for Numbers as of yet.

If your strings were short, i.e. include only a few numbers and the type of operations were quite limited such as only plus or minus, perhaps, one could write a formula using the Regex functions in Numbers, but honestly, I am not sure if it would be worth the headache even then.

In case you really wish to take the Regex route, here is the screenshot of a simple (!) example that only works for two integers and addition:

enter image description here

And here is the text version of the formula in the screenshot:

IF(ISERROR(REGEX.EXTRACT($A1;"\++";1)="+");"Invalid Operation!";REGEX.EXTRACT($A1;"\d+";1)+REGEX.EXTRACT($A1;"\d+";2))
Improve this answer
8
  • EVAL in Excel? not in my function list, neither is evaluate()
    – Solar Mike
    Commented Sep 5, 2023 at 8:23
  • I've seen EVAL used in MS Access and in VBA code, but not as a standard function in MS Excel. Can you clarify?
    – Monomeeth ♦
    Commented Sep 5, 2023 at 8:29
  • Thanks for your answer! Sad to hear that such a function or method doesn't exist in Numbers. I'm not sure how to use the Regex functions in this case to build a workaround but I'll think about it.
    – Philea
    Commented Sep 5, 2023 at 9:20
  • Thanks for adding the details on that approach! In the meantime I found a solution and added it to my question. I'll mark your answer as the most helpful so that people will see that this issue is solved. Thanks!
    – Philea
    Commented Sep 5, 2023 at 11:55
  • 1
    @Alper Cool, done. Apparently I need more reputation but the upvote has been recorded.
    – Philea
    Commented Sep 5, 2023 at 12:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .