How to Prefix Text Before Numbers in Google Sheets (2026)

Understanding the need to prefix numbers in Google Sheets

In many datasets, you may want to display a human-friendly label before a numeric ID without turning the value into text. For example, turning 1423 into ORD-1423 helps with readability and downstream workflows. According to How To Sheets, keeping the underlying numeric value intact is essential for sorting, filtering, and calculations. This means choosing methods that preserve the number while altering only its displayed form. The best approach depends on whether your data will be used for calculations or simply displayed in reports. When you prefix numbers for display, ensure you test formulas on a copy of the sheet to avoid data drift.

Different teams prefer different prefixes (ORD, INV, No., etc.). The key is to select a method that keeps numeric integrity where it matters (calculations) and provides readable labels where it matters (reports). The phrase google sheets before number often comes up in templates and dashboards because readable IDs help stakeholders quickly identify records without altering the data’s numeric nature.

Planning your prefix strategy

Before you start inserting prefixes, map out what you want to prefix, where it will appear, and how it will affect downstream processes. Decide if the prefix should be visible in the same cell (harder to sort) or in a neighboring column (easier to keep calculations intact). Consider future data imports or exports: if you bring the sheet into another system, will the prefix survive or cause parsing issues? Write down a small set of rules (e.g., “ORD- must prefix all order numbers in column A”). This planning reduces rework and keeps your sheet consistent across teams. How To Sheets recommends starting with a sample data row to validate your chosen approach.

Method A: Prefix with CONCAT or & operator

A simple and common approach is to prepend a text prefix using the CONCAT function or the & operator. For example, if A2 contains a numeric ID, you can place in B2: ="ORD-"&A2 or =CONCAT("ORD-", A2). This keeps the original numeric value in A2 intact. The resulting B2 will display ORD-1423, while A2 remains a number. If you copy the formula down, Google Sheets will adjust references automatically. Note that the result in B2 is text, which affects numeric operations in B2 but not A2.

Pros: easy to understand, preserves original numbers in their cells. Cons: the display column becomes text, which may impact sorting in that column.

Method B: Prefix with the TEXT function for formatting

If you need more control over the numeric display (for example, adding leading zeros or combining with other formats), use TEXT. For instance, =TEXT(A2, "ORD-0000") will display ORD-0001 for A2=1. TEXT converts the number to text, but it gives precise control over formatting. This is especially useful for fixed-width IDs where you require a consistent number of digits, or when you want the prefix to appear with the exact formatting every time.

Caveat: because TEXT returns text, you cannot perform numeric calculations directly within the same cell. Use a separate column for calculations or revert to the original numeric value for those tasks.

Method C: Custom number formats for in-place display

Custom number formats let you show a prefix without changing the underlying numeric value. Select the target cells, open Format > Number > More formats > Custom number format, and enter a pattern like "ORD-"0000. With A2=123, the display shows ORD-0123, but the cell remains numeric for calculations. This approach is powerful for dashboards and reports where you want clean, consistent prefixes without sacrificing data integrity.

Tip: test a few sample values to ensure always the same digits appear, especially if your data varies in length.

Real-world templates you can reuse

Template 1 (simple prefix): ="ORD-"&A2 Template 2 (leading zeros): =TEXT(A2, "ORD-0000") Template 3 (display-only prefix via format): Select cells → Format → Number → Custom number format → ORD-0000 Template 4 (dynamic prefix from another cell): =IF(B2="Order", "ORD-"&A2, A2) These templates illustrate different needs: quick labeling, fixed-width IDs, or dynamic prefixes based on conditions. Start with the simplest and only move to more complex methods if your data needs justify it.