Can’t get formulas to work with large numbers

[u/TwicePuzzled]

I’m having issues with formulas. Row A1 is formatted as text and has a 21 digit number in it. When I go to row 2 and use SUM to try and make cell A2 = A1 + 1 the cell just shows the formula instead of the new number. Any idea how to make this add up?

• Show formulas is turned off
• Entire column is formatted to text since they are large numbers.
• I’ve tried both SUM and ADD functions and neither work. It just shows the formula instead of the 21 digit number.

[u/AutoModerator]

/u/TwicePuzzled - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/caribou16]

I believe Excel has a maximum precision of 15 digits.

[u/Shiba_Take]

Did you enter = A1 + 1 or actually A2 = A1 + 1?

[u/TwicePuzzled → u/Shiba_Take]

A1 + 1

[u/Shiba_Take]

IDK how you got 21-digit number. I tried entering 12345678901234567890 for a test and got 12345678901234500000 instead with General format.

https://preview.redd.it/fggs3e2031md1.png?width=1746&format=png&auto=webp&s=2bf0e0f87a78b985195c78203c8488e6e28eb60f

[u/Shiba_Take → u/Shiba_Take]

I added 1 to the end of 12345678901234500000 and got another zero. =A1+1 "works" though, but it's actually the same number

https://preview.redd.it/afacx98e31md1.png?width=603&format=png&auto=webp&s=57b50a4666c074e00dbdd262214cdac830d3ebf4

[u/Shiba_Take → u/Shiba_Take]

https://preview.redd.it/qh1ag2cf31md1.png?width=536&format=png&auto=webp&s=44987591eb790bf04eb39da7214aaf23c26b0d7b

[u/TwicePuzzled → u/Shiba_Take]

Sorry, it’s formatted as Text not General

[u/Shiba_Take → u/TwicePuzzled]

Then Excel is just understanding your formula as text and not formula.

[u/TwicePuzzled]

https://preview.redd.it/hjud6hk731md1.jpeg?width=1290&format=pjpg&auto=webp&s=e47f5f5ba8702b110cbc01e4cf9f321a8368df55

[u/caribou16 → u/TwicePuzzled]

Those "numbers" are text strings. You can't sum text.

[u/TwicePuzzled → u/caribou16]

That was the only way to get the numbers to show up. I also tried highlighting multiple boxes and clicking the thing in the bottom right corner (forgot what it’s called) but that just copied the numbers instead of following the sequence

[u/caribou16 → u/TwicePuzzled]

Yes, because Excel has a limit of 15 digits for numerical precision.

https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

You can have BIGGER numbers, because it will support scientific notation. But you can't have more precision than #.##############. Anything after that it's going to just add zeros and you can only do math with it for values that would impact the 15 digits of precision.

Example: A number that is 21 "1" would show up as: 111111111111111000000

Adding 2 to it won't change it, but multiplying by 2 would give you 222222222222222000000

[u/Shiba_Take]

Excel doesn't seem to natively support numbers that big with full precision. Unless there's some settings to increase the limit?

[u/mspring501]

Did you type "=A1+1" or "A1+1"?

[u/TwicePuzzled → u/mspring501]

“A1+1”

[u/mspring501 → u/TwicePuzzled]

You need an "=" in front of Excel formulas.

[u/PaulieThePolarBear]

u/Caribou16 has accurately and succinctly provided you with the root cause of your issue.

Please tell us in words, with no to limited references to Excel functions, what you are trying to accomplish here.

[u/TwicePuzzled → u/PaulieThePolarBear]

I basically want to add 1 number to each cell

So cell A1 show 5022440200562497977

Cell A2 show 5022440200562497978

Cell A3 show 5022440200562497979

And so on

[u/Ginger_IT → u/TwicePuzzled]

How many times do you want to iterate this?

Also, you have to press enter twice in reddit

to get text

to show up

on a new

line.

[u/TwicePuzzled → u/Ginger_IT]

500 times

[u/caribou16 → u/TwicePuzzled]

Is it fair to say you want the the last digit increased 500 times from ...7977 to ...8477 and it's ok that the final product is a text string and not a number?

You could break off the end of the text, turn it into a number, add, then recombine it with the rest of the text string. Put the starting "number" in A1, then in A2 put: =LEFT(A1,15)&VALUE(RIGHT(A1,4)+1) and drag down.

Like THIS.

[u/Ginger_IT → u/TwicePuzzled]

Strip the 18 digits off the front and format as text.

Iterate your 500 numbers into the next column.

Then format those as text. (& Fix the missing zeros if you start at 1)

In the third column, CONCAT the first two columns.

Profit.

[u/TwicePuzzled → u/Ginger_IT]

Thanks, this worked

Solution verified

[u/reputatorbot → u/TwicePuzzled]

You have awarded 1 point to Ginger_IT.

---

^{I am a bot - please contact the mods with any questions}

[u/TheNightLard → u/TwicePuzzled]

You could try to split the original reference to separate the "large portion" of the number that will be unaffected by your operation, and the "small portion" that will be affected, converting them to values. After the operation, convert back to text and concatenate both strings.

If you know your operation and what ranges you are handling, it kind of straightforward.

For example: A1=12345678901234567890

If you want to add "1" 500 times:

B1=VALUE(RIGHT(A1,4))

B2=B1 + 1

A2=CONCAT(LEFT(A$1,16),TEXT(B2,"0000"))

Drag B2 and A2 down for as long as needed. I didn't validate that so let me know if I messed up the formulas.

Edit: messed up format

[u/Ginger_IT → u/TwicePuzzled]

Though a faster way would be to iterate the 500 digits with the same requirements from my other comment.

Then B1=CONCAT("Thatfirst18digits" & A1)

Then drag down for the iterations.

[u/PaulieThePolarBear → u/TwicePuzzled]

What is the maximum number you require?

What version of Excel are you using?

It appears that from your image, your output is in an Excel CTRL+T table. Is this an absolute requirement?

[u/Ginger_IT]

When formatting a number as text, just imagine that asking Excel to do an arithmetic operation is like asking:

apple + 1. Is that two apples? Is it "apple1"?

As explained ad nauseum, there's a limit to the number of digits in Excel. Formatting as text means that it's no longer the number it appears to be. It's now a thing. It's the name of a brick, for example.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
LEFT	Returns the leftmost characters from a text value
RIGHT	Returns the rightmost characters from a text value
TEXT	Formats a number and converts it to text
VALUE	Converts a text argument to a number

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #36662 for this sub, first seen 31st Aug 2024, 18:22] [FAQ] [Full list] [Contact] [Source code]}

[u/ZestycloseSection768]

Using text format for large numbers can be tricky! Change the format to 'Number' or 'General' and re-enter the formula. You got this!

[u/Greedy_Phone_6934]

Sounds like you're running into a pretty common issue with Excel when dealing with big numbers. Since your cells are formatted as text, Excel isn't recognizing the formulas as something it needs to calculate—they're just being displayed as plain text. Here’s how you can fix that:

First off, you’ll need to change the format of the cell where you're trying to do the calculation (let's say A2). Right now, it’s set to "Text," which is why the formula isn’t working. Just right-click on A2, hit "Format Cells," and switch it to "Number" or "General." Once you’ve done that, re-enter your formula (`=A1 + 1`) and hit Enter. That should make Excel treat it as a real formula instead of just text.

If the number in A1 is still formatted as text (which it sounds like it is), Excel won’t be able to do the math with it. You can convert it to a number by using the `VALUE` function. For example, in A2, you could type `=VALUE(A1) + 1`, and that should do the trick. Alternatively, you can just multiply the text number by 1 to force Excel to treat it as a number, like `=A1 * 1 + 1`.

If you're working with a whole column of these big numbers, you might want to reformat the entire column. Just highlight the column, right-click, choose "Format Cells," and switch it over to "Number" or "General." This will save you from having to do it cell by cell.

After you’ve got the formatting sorted out, re-enter your formula, and Excel should be good to go. It’ll add the numbers instead of just showing the formula.

This should solve your problem and get those big numbers working in your formulas. If anything else pops up, feel free to ask!

[u/AutoModerator → u/Greedy_Phone_6934]

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.