How to Stop Google Sheets from Changing Numbers to Dates

If Google Sheets turns an entry such as 1-2, 3/4, or 10-11 into a date, format the destination column as Plain text before entering or pasting the values. On a computer, select the cells, open Format > Number > Plain text, and then enter the data again. For one value, a leading apostrophe such as '1-2 also tells Sheets to keep the entry as text.

Changing the format after Sheets has already interpreted an entry as a date may not reconstruct the original characters. Use Undo immediately, re-enter the value from the source, or consult version history before replacing a large range. Also check the spreadsheet locale because it controls the default date and number formats for the entire file.

Decide Whether the Entry Is Data or an Identifier

The correct format depends on what the value means, not on whether it contains digits.

• Use Plain text for product codes, case numbers, model names, version labels, tracking numbers, ZIP codes, fractions used as labels, and IDs that are never added or averaged.
• Use Number for values that should participate in arithmetic.
• Use Date only when the value represents an actual calendar date that should sort, filter, or calculate as a date.

An entry such as 03-04 can be ambiguous. It could mean March 4, April 3, a product revision, or a size. Sheets cannot infer the business meaning, so the column format and spreadsheet locale guide how it interprets the entry.

Prevent Conversion Before Typing or Pasting

The most reliable fix is to prepare the column before adding data.

1. Select the empty cells or the entire destination column.
2. Open Format > Number > Plain text.
3. Enter one test value that previously changed into a date.
4. Confirm that the displayed value and formula bar show the exact characters you entered.
5. Paste or type the remaining values only after the test succeeds.

Applying Plain text to a full column is appropriate when every entry is an identifier. Avoid formatting an entire mixed-use sheet as text because formulas and numeric calculations can stop recognizing values as numbers.

Use a Leading Apostrophe for a Single Entry

When only one or two values need protection, type an apostrophe before the entry:

'1-2
'03/04
'00123

The apostrophe is an input marker and is not normally displayed in the cell. Google documents that converting a numeric value with TO_TEXT is equivalent to prefixing it with an apostrophe. Use this method for isolated entries; format the whole column as Plain text for recurring imports or regular data entry.

Fix a Value That Was Just Converted

If the conversion happened moments ago, use Edit > Undo or the standard undo shortcut before making additional edits. Then format the destination as Plain text and enter the value again.

Do not rely on switching the converted cell from Date to Plain text and assume the original entry has returned. Sheets stores real dates as numeric values with a date display format. Reformatting can change how the stored value appears without recovering the exact ambiguous text that was originally typed.

Recover a Large Range Without Overwriting Current Work

If many cells were converted and the original file or copied source is still available, preserve the current spreadsheet before attempting a repair.

1. Make a copy of the current spreadsheet or duplicate the affected sheet tab.
2. Keep the original source file, CSV, email, or exported report unchanged.
3. Format an empty repair column as Plain text.
4. Paste a small sample from the original source and verify it.
5. Replace the affected range only after the sample matches.

If the source is no longer available, open File > Version history > See version history. Google Sheets lets editors inspect earlier versions and restore a previous version. Because restoring affects the file rather than one isolated cell, make a copy of the current spreadsheet first and use the earlier version as a reference unless a full rollback is acceptable.

For data that needs additional cleanup after recovery, How to Clean Messy Spreadsheet Data with Google Sheets Regex explains how to standardize text while keeping an original column for comparison.

Check the Spreadsheet Locale

Google states that a spreadsheet's locale changes its default currency, date, and number formatting. A value that looks unambiguous in one region can be interpreted differently in another.

To review the locale on a computer:

1. Open the spreadsheet.
2. Select File > Settings.
3. Under General, review Locale and Time zone.
4. Change the locale only when the entire spreadsheet should use a different regional convention.
5. Select Save settings, then recheck formulas and date columns.

A locale change applies to the whole spreadsheet and is visible to collaborators regardless of their location. Do not change it merely to repair one ID column in a shared file. Plain text is the safer local fix when the values are identifiers.

After changing the locale, re-enter ambiguous test values. Existing converted cells may still contain date values and should be checked against the source rather than assumed to be repaired automatically.

Protect Values When Pasting from Another Spreadsheet

When copying codes from Excel, another Google Sheet, a website, or an internal system, prepare the target cells first.

1. Format the target column as Plain text.
2. Paste a small sample.
3. Compare the first, middle, and last values with the source.
4. If source formulas or styling are unwanted, use Paste special > Values only.
5. Confirm leading zeros, separators, and long identifiers before pasting the full range.

Values only removes source formulas and formatting, but it does not replace the need to prepare the destination data type. Use Plain text first when the pasted content must remain exact.

If data arrives through IMPORTRANGE, keep the source and receiving columns consistent and test a small range before building a dashboard. How to Build a Google Sheets Dashboard with IMPORTRANGE covers access approval, refresh behavior, and source-range checks.

Handle CSV Imports and Exports Carefully

A CSV stores characters and separators, not Google Sheets cell formats. When a CSV is opened or imported, software may interpret date-like strings, long numbers, or leading zeros according to its own rules.

For important CSV data:

• Keep an untouched copy of the original CSV.
• Import into a duplicate spreadsheet rather than the production file.
• Check columns containing IDs, ZIP codes, SKUs, dates, and long numeric strings.
• Compare record counts and several exact source values before replacing existing data.
• Do not assume that a visually correct custom number format will preserve the same underlying value when exported again.

If the workflow generates invoices or filenames from Sheets, verify the stored value rather than only its appearance. The automation in How to Generate and Email PDF Invoices from Google Sheets depends on consistent source fields and should be tested with sample IDs before sending real documents.

Choose Between Plain Text and a Custom Number Format

Plain text preserves the exact characters. A custom number format changes how a numeric value is displayed while keeping it numeric.

• Use Plain text for 00123 when the leading zeros are part of an ID.
• Use a custom number format such as 00000 when the underlying value is genuinely numeric but must display at a fixed width.

The difference matters for formulas, lookups, sorting, and exports. A text value 00123 is not identical to the number 123, even if a display format makes them look similar.

Use TO_TEXT and VALUE for Controlled Conversions

Google Sheets provides functions for explicit conversion:

=TO_TEXT(A2)
=VALUE(B2)

TO_TEXT converts a numeric value to text while retaining its existing display format. It is useful when a formula output must become a text label. It cannot recreate an original ambiguous entry that was already converted incorrectly.

VALUE converts recognized text into a number. Google notes that date-like text can become a date serial number. Use it only when the destination should be numeric; do not use it to protect identifiers from date conversion.

Find Data-Type Problems Before They Break Formulas

A column can contain values that look identical but use different underlying types. That can cause lookups, filters, sorting, charts, and validation rules to behave inconsistently.

Check suspicious columns by comparing them with the original source and by testing a few cells in a separate area. For identifiers, standardize the entire column as text. For calculation fields, standardize the entire column as numbers or real dates. Avoid mixing types in the same operational column unless the design intentionally supports mixed data.

What Not to Do

• Do not reformat a converted date and assume the original text has been recovered.
• Do not change the locale of a shared spreadsheet without checking the effect on dates, currencies, and collaborators.
• Do not delete the source column before validating the repaired data.
• Do not use a custom date format for product codes or identifiers.
• Do not run cleanup formulas across the production range without a backup or comparison column.

Confirm That Automatic Date Conversion Is Stopped

Use a temporary test row before importing real data. Enter several examples that match the actual pattern, such as 1-2, 03/04, 00123, and a long identifier. Confirm that:

• Each cell displays the exact intended characters.
• The formula bar shows the same value.
• Leading zeros remain present.
• Sorting and lookup behavior matches an identifier column rather than a date column.
• Copying the value to a plain-text editor preserves the expected characters.

Once the test passes, apply the same format to the full destination range and keep the original source until the final record count and sample values have been verified.

Official Google References

• Format numbers in a spreadsheet
• Set a spreadsheet's location and calculation settings
• Edit and format a spreadsheet
• Find what's changed in a file
• TO_TEXT
• VALUE
• Learn how to optimize data ingestion

Set the Data Type Before the Data Arrives

Plain text is the correct choice when digits and separators form an identifier rather than a calculation. Set the format before entry, use an apostrophe for isolated values, review the spreadsheet locale, and keep the source available until the repaired column has been verified. This prevents a display problem from becoming a permanent data-quality problem.