CommentsToSheets

Guides

Why Excel rounds long comment IDs and turns usernames into dates

Spreadsheets try to be helpful with anything that looks like a number, a date or a formula. Comment exports are full of things that look like all three and are none of them. The conversion is silent and cannot be undone afterwards, so the fix is in how the file is opened.

Updated Nov 19, 2026 · 6 min read

What gets changed

In the fileWhat Excel shows after a double-click on a CSVWhy
17984512345678901 (an Instagram comment ID)1.79845E+16, stored as 17984512345678900Numbers keep 15 digits.
7301234567890123456 (a TikTok comment ID)7.30123E+18, stored as 7301234567890120000Same limit, four digits lost.
A handle such as march1, sept2 or dec101-Mar, 2-Sep, 10-DecA month name next to a number reads as a date.
A handle such as 004242Leading zeros are dropped from numbers.
A handle such as 1e51.00E+05Digits around the letter E read as scientific notation.
A handle such as trueTRUERead as a logical value.
A comment that says 10-12 or 3/412-Oct, 4-MarRead as dates.
A comment that says =) or -_-An error, or a formula promptA leading =, + or - starts a formula.

Formatting the column as Text afterwards does not bring anything back. The cell already holds the converted value, and the original characters are gone from the sheet. Only the file still has them.

Why 15 digits

Excel, Google Sheets and nearly every other spreadsheet store a number as a 64-bit floating-point value. That format holds about 15 significant decimal digits exactly. A 17-digit Instagram ID or a 19-digit TikTok ID does not fit, so the last digits become zeros. Two different comments whose IDs differ only at the end now carry the same number.

An ID is not a quantity. Nobody adds two comment IDs together. It should be stored as text, which has no digit limit, and the whole problem is getting the spreadsheet to agree.

Which IDs are at risk depends on the network. Instagram comment IDs are 17 digits and TikTok IDs are 19, so both are affected. YouTube IDs contain letters, so they stay text on their own. The platform column tells you which you have.

Why it matters for comment work

  • Threads. A reply points at its parent through reply_to_comment_id. If both columns were rounded, replies can attach to the wrong comment. Rebuilding reply threads depends on exact IDs.
  • Duplicates. Removing duplicate rows by comment_id deletes real comments when two IDs rounded to the same value.
  • Matching two exports. Finding what is new since last week means matching IDs between files. Rounded IDs match the wrong rows or none.
  • People. A username turned into a date no longer matches the same person in another export, so counts per person and duplicate commenter checks split or lose them.

Check a sheet for damage in one minute

Put these in spare cells. Each should return 0 on a healthy Comments sheet from Instagram or TikTok. They work in Excel and Google Sheets.

CheckFormula
IDs that became numbers=SUMPRODUCT(--ISNUMBER(B2:B5000))
Usernames that became numbers or dates=SUMPRODUCT(--ISNUMBER(C2:C5000))
Usernames that became TRUE or FALSE=SUMPRODUCT(--ISLOGICAL(C2:C5000))
Comments that became errors=SUMPRODUCT(--ISERROR(E2:E5000))

A date is a number inside a spreadsheet, so the second check catches both. For duplicate IDs, which rounding creates, Google Sheets:

=COUNTA(B2:B) - COUNTUNIQUE(B2:B)

Excel (Microsoft 365):

=COUNTA(B2:B5000) - ROWS(UNIQUE(FILTER(B2:B5000, B2:B5000<>"")))

Anything above 0 means IDs collided. Go back to the original file and open it one of the ways below.

Fix 1: use a format that stores text as text

The Excel workbook writes comment_id, user_id, reply_to_comment_id and every username as text cells. Excel and Google Sheets leave text cells alone, so nothing is rounded or reinterpreted. posted_at_utc is the one column written as a real date, on purpose, so that sorting and pivots by day work.

Copy for Sheets does the same job for a paste: any run of 16 or more digits and any value starting with =, +, - or @ gets a leading apostrophe, which spreadsheets read as "this is text" and do not display.

Fix 2: import the CSV instead of opening it

Excel. Data, Get Data, From File, From Text/CSV. In the preview, set Data type detection to Do not detect data types and press Load. Every column arrives as text. Afterwards convert only the columns that really are numbers or dates: likes with Data, Text to Columns, Finish, and posted_at_utc with a helper column, as in convert UTC timestamps to your time zone. The same window is where the encoding is set: see why emoji and accents break in a CSV.

Google Sheets. File, Import, Upload, and on the import screen untick Convert text to numbers, dates, and formulas. The full walk-through is in how to import comments into Google Sheets.

Fix 3: switch the conversions off in Excel

Current Microsoft 365 versions of Excel let you turn the automatic conversions off for good. File, Options, Data, section Automatic Data Conversion. Untick:

  • Remove leading zeros and convert to a number
  • Keep first 15 digits of long numbers and display in scientific notation
  • Convert digits surrounding the letter "E" to a number in scientific notation
  • Convert continuous letters and numbers to a date

Leave When loading a .csv file or similar file, notify me of any automatic data conversions ticked. Excel then asks before converting, and Don't Convert keeps the file as written. Older versions of Excel do not have these options; use Fix 1 or Fix 2 there.

If you paste by hand

When copying rows from somewhere else into a sheet, format the destination columns as Text first (select the columns, Format Cells, Text in Excel; Format, Number, Plain text in Google Sheets), then paste with Paste special, Values only. Formatting first is what matters: the conversion happens at the moment of the paste.

When typing a single ID or handle into a cell, start with an apostrophe: '17984512345678901. The apostrophe is not part of the value and does not print.

Formulas that compare IDs

A lookup only matches when both sides are the same type. A text "17984512345678901" never equals a number, even an unrounded one. With IDs kept as text everywhere, the usual formulas work as written:

=COUNTIF(I:I, B2)

counts the replies under the comment in row 2. One caution: COUNTIF and COUNTIFS in Excel treat a text value made only of digits as a number while matching, so two different 17-digit IDs that share their first 15 digits are counted as equal. On Instagram and TikTok exports where exact reply counts matter, use an exact text comparison instead:

=SUMPRODUCT(--(I$2:I$5000=B2))

That compares the full text and is correct in Excel and Google Sheets. For a lookup, MATCH(B2, I$2:I$5000, 0) and XLOOKUP compare text exactly and are safe.

FAQ

Can I recover the rounded digits?

Not from the sheet. The spreadsheet no longer holds them. Open the original file again using an import that keeps text, or download the Excel workbook, which stores IDs as text.

Why does formatting the column as Text not fix it?

Formatting changes how a value is shown, not what was stored. The conversion happened when the file was opened. Format the column as Text before the data arrives.

Does Google Sheets round long IDs too?

Yes, when it reads them as numbers. Untick Convert text to numbers, dates, and formulas on import, or use Copy for Sheets, which marks long IDs as text.

Are YouTube IDs affected?

No. YouTube comment and channel IDs contain letters, so spreadsheets keep them as text.

A comment shows #NAME? in Excel. What happened?

The comment starts with =, + or - and Excel tried to run it as a formula. Import the CSV with data type detection off, or use the workbook, where comments are stored as text.