Split Text in Google Sheets Without Losing Formatting (Preserve Zeros & Hex)

If you’ve ever tried to split text in Google Sheets without losing formatting, you may have run into a frustrating issue: Google Sheets often changes your text values—especially those with leading zeros or hexadecimal codes—by converting them into numbers or scientific notation. This can corrupt important data like item codes, product IDs, or hex values.

In this tutorial, you’ll learn how to split text without losing formatting, including how to:

Preserve leading zeros in codes or IDs
Prevent hex values from converting to scientific notation
Keep the split result in text format reliably

Why Does Google Sheets Change Text When Splitting?

Google Sheets automatically tries to interpret text as numbers when you use the SPLIT function or the Split text to columns feature. This can cause two major problems:

1. Hexadecimal Values Become Scientific Notation

Example:

Original: 9C40|3E80|4FB0|DDA|40D8|4E20
Formula: =SPLIT("9C40|3E80|4FB0|DDA|40D8|4E20", "|")

Result:

9C40
3.00E+80
4FB0
DDA
40D8
4.00E+20

Some values are converted to scientific notation because they resemble large numbers.

2. Leading Zeros Are Removed

Example:

Original: 0001200-0001201-0001202-0001203
Formula: =SPLIT("0001200-0001201-0001202-0001203", "-")

Result:

1200
1201
1202
1203

All the leading zeros are lost, which can be a serious issue when dealing with product or item codes.

Why TO_TEXT Doesn’t Help

You might try wrapping the split function with TO_TEXT or ARRAYFORMULA(TO_TEXT(...)) to preserve the formatting, but that doesn’t prevent Google Sheets from removing leading zeros or applying scientific notation.

The Solution to Split Text in Google Sheets Without Losing Formatting

To keep the split result in text format, you can use a clever trick: insert an apostrophe (') before each value to tell Google Sheets to treat it as plain text.

Here’s the formula pattern:

=SPLIT(SUBSTITUTE("'"&B6, "|", "|'"), "|")

Example showing how to split text in Google Sheets without losing leading zeros or converting to scientific notation

Explanation:

SUBSTITUTE("'"&B6, "|", "|'") adds a ' before each value.
SPLIT(..., "|") then splits the string, and because each value begins with ', Sheets treats them as text.

Example 1: Preserve Hexadecimal Values

Original String:

9C40|3E80|4FB0|DDA|40D8|4E20

Formula:

=SPLIT(SUBSTITUTE("'9C40|3E80|4FB0|DDA|40D8|4E20", "|", "|'"), "|")

Result:

9C40
3E80
4FB0
DDA
40D8
4E20

This preserves formatting and prevents conversion to scientific notation.

Example 2: Preserve Leading Zeros in Item Codes

Original String:

0001200-0001201-0001202-0001203

Formula:

=SPLIT(SUBSTITUTE("'0001200-0001201-0001202-0001203", "-", "-'"), "-")

Result:

0001200
0001201
0001202
0001203

Final Notes

This technique is simple but powerful—just prepend an apostrophe before each item using SUBSTITUTE, and SPLIT will split text in Google Sheets without losing formatting or altering your original values. This is especially useful when working with:

Hexadecimal values
Product/item codes with leading zeros
Any alphanumeric data that shouldn’t be interpreted as numbers

Copy-Paste Formula Templates

Hex Example Template:

=SPLIT(SUBSTITUTE("'"&A1, "|", "|'"), "|")

Item Code Template:

=SPLIT(SUBSTITUTE("'"&A1, "-", "-'"), "-")

Replace A1 with your cell reference and adjust the delimiter as needed.