Excel TEXT Function Equivalent in pandas

Excel's TEXT function turns a value into formatted text: =TEXT(A2, "#,##0.00"), =TEXT(B2, "0.0%"), =TEXT(C2, "mmm yyyy"). It is how people build labels such as "North: 1,234.50 (12.3%)", file names such as "Sales Mar 2026.xlsx", and sentences in report emails. pandas has no TEXT function, but Python's format specifications and strftime do the same job, column-wide. The translation is mostly mechanical, with one difference that matters when numbers must match a workbook exactly: rounding. This page, part of Excel Formula Equivalents in pandas, maps the common format codes and fixes the rounding.

Prerequisites

pip install pandas openpyxl

summary.xlsx has three regions with revenue (including a negative), a growth rate, a store number and a period-end date. The Excel results quoted below were produced by entering the same TEXT formulas and recalculating the workbook in LibreOffice.

Step 1 — Numbers with separators and decimals

Python's format specification mini-language covers Excel's common number codes. , adds thousands separators, .2f fixes two decimals, % multiplies by 100 and adds a percent sign:

import pandas as pd

s = pd.read_excel("summary.xlsx")
s["revenue_txt"] = s["revenue"].map("{:,.2f}".format)
s["growth_txt"] = s["growth"].map("{:.1%}".format)
s["store_txt"] = s["store_no"].map("{:05d}".format)
print(s[["region", "revenue_txt", "growth_txt", "store_txt"]].to_string(index=False))

"{:,.2f}".format is the column-wide form of =TEXT(B2,"#,##0.00"); "{:.1%}" matches "0.0%"; "{:05d}" pads to five digits like "00000", turning store 42 into 00042. For a single value an f-string reads more naturally: f"{revenue:,.2f}".

Step 2 — Signs, brackets and scaled units

Excel format codes can have separate sections for positive and negative numbers — "#,##0.00;(#,##0.00)" puts negatives in brackets — and trailing commas scale by thousands. Python has no sections, so write the rule as a small function:

def accounting(v):
    return f"({abs(v):,.2f})" if v < 0 else f"{v:,.2f}"

def millions(v):
    return f"{v / 1_000_000:,.1f}M"

s["revenue_acct"] = s["revenue"].map(accounting)
s["growth_signed"] = s["growth"].map("{:+.1%}".format)
print(s[["region", "revenue_acct", "growth_signed"]].to_string(index=False))
print([millions(v) for v in (1_234_567, 987_654_321)])

accounting gives (1,234.50) for East, as Excel's two-section code does; millions reproduces "#,##0.0,,"M"" by dividing first, giving 1.2M and 987.7M. The + flag in "{:+.1%}" shows the sign on positive values too — +12.3% — which Excel spells as "+0.0%;-0.0%". A zero growth becomes +0.0%; if it should read 0.0%, handle zero explicitly in a function.

Excel codes can also have a third section for zero — "#,##0.00;(#,##0.00);–" shows a dash instead of 0.00 — and missing values need a rule too, because formatting NaN produces the text "nan". Fold both into the same function:

import math

def money(v, zero="–", missing=""):
    if v is None or (isinstance(v, float) and math.isnan(v)):
        return missing
    if v == 0:
        return zero
    return f"({abs(v):,.2f})" if v < 0 else f"{v:,.2f}"

print([money(v) for v in (1234.5, -1234.5, 0, float("nan"))])

Keeping the rules in one function per kind of figure — money, pct, count — means every label in a report formats the same way, and a change of convention (a dash for zero, brackets for negatives) is made once.

Step 3 — Dates and times with strftime

Date codes map onto strftime directives: yyyy is %Y, mm is %m, dd is %d, mmm is %b, mmmm is %B, ddd is %a, dddd is %A, and hh:mm is %H:%M:

s["iso"] = s["period_end"].dt.strftime("%Y-%m-%d")
s["month_label"] = s["period_end"].dt.strftime("%b %Y")
s["weekday"] = s["period_end"].dt.strftime("%A")
print(s[["period_end", "iso", "month_label", "weekday"]].to_string(index=False))

The one to watch is mm. In Excel it means month in a date code but minutes after hh; in Python %m is always the month and %M always minutes, so "hh:mm" becomes "%H:%M". Month and day names come from the machine's locale, which is English on most servers; a report for French readers either maps the names itself or runs with a French locale set.

Step 4 — Match Excel's rounding

Here is where a straight translation can disagree with the workbook. TEXT rounds halves away from zero; Python's formatting rounds the binary value half to even:

excel = {1234.5: "1,235", 2.5: "3", 3.5: "4"}          # TEXT(v, "#,##0") after recalculation
for v, shown in excel.items():
    print(f"{v}: Excel {shown!r}  Python {v:,.0f}")

Recalculated in LibreOffice, TEXT(1234.5, "#,##0") gives 1,235, TEXT(2.5, "0") gives 3 and TEXT(3.5, "0") gives 4. Python gives 1,234, 2 and 4: 2.5 and 1234.5 round down to the even neighbour. When the text must match numbers readers see in Excel, round with decimal first, then format:

from decimal import Decimal, ROUND_HALF_UP

def text_like_excel(v, decimals=0):
    q = Decimal(str(v)).quantize(Decimal(1).scaleb(-decimals), rounding=ROUND_HALF_UP)
    return f"{q:,.{decimals}f}"

print([text_like_excel(v) for v in (1234.5, 2.5, 3.5)], text_like_excel(987654.321, 2))

Now the results are 1,235, 3 and 4, matching Excel. Decimal(str(v)) uses the value as it is written rather than its binary approximation, which also fixes cases like 2.675 that sit just below the half in binary. Round Numbers for Excel Output: float_format vs Number Formats covers the same rounding question for numbers written to cells.

Step 5 — Build labels, file names and sentences

The point of TEXT is usually a larger string. Combine formatted pieces with f-strings, row by row where the label varies:

s["label"] = [f"{r}: {text_like_excel(v, 2)} ({g:+.1%})" for r, v, g in zip(s["region"], s["revenue"], s["growth"])]
print(s["label"].tolist())

period = s["period_end"].max()
filename = f"Sales {period:%b %Y}.xlsx"
sentence = f"Revenue for the quarter ending {period:%d %B %Y} was {text_like_excel(s['revenue'].sum(), 2)}."
print(filename, "|", sentence)

f-strings accept the same format specs inline, including strftime codes for datetime values ({period:%b %Y}), which keeps the whole template readable in one place. A file name built from a date in %Y-%m order sorts chronologically in a folder listing; one built from %b %Y reads better but sorts alphabetically — choose per use.

Step 6 — Do not put text where numbers belong

Formatted text looks like a number and behaves like text: it cannot be summed, it sorts character by character, and Excel shows its green "number stored as text" triangle. For cells in a report, write the numbers and set a number format; keep TEXT-style strings for labels:

from openpyxl import load_workbook

s[["region", "revenue", "growth", "label"]].to_excel("report.xlsx", index=False)
wb = load_workbook("report.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2):
    row[1].number_format = "#,##0.00;(#,##0.00)"
    row[2].number_format = "+0.0%;-0.0%;0.0%"
wb.save("report.xlsx")
print([(c.value, c.number_format) for c in ws[2][:3]])

The revenue cell still holds 1234.5 and displays 1,234.50; the label column holds text for the chart or the email. Write Custom Number Format Codes in Excel with Python covers the number-format side.

When a number must appear both ways — in a cell and in a chart title — write the number to the cell and build the title text from the same value with the functions above, so the two cannot drift apart.

Common pitfalls

• Formatting numbers that will be calculated with. Keep numbers numeric in cells; format only labels.
• Python's half-even rounding. Use decimal.ROUND_HALF_UP when text must match Excel.
• mm versus minutes. Python separates them: %m month, %M minutes.
• Locale-dependent names. %b and %A follow the machine's locale, so the same job can produce "Mar" on one server and "mars" on another; set the locale explicitly or map names yourself for reports that must be identical everywhere.
• Format specs on missing values. "{:,.2f}".format(nan) gives "nan"; handle NaN before formatting.

Performance notes

map with a format string runs one Python call per value, about a million values per second — fine for labels and summaries. dt.strftime is vectorised and faster. For very large exports, formatting is almost never needed: write numbers and let number formats handle display.

Conclusion

Excel's TEXT(value, code) becomes a Python format spec — f"{v:,.2f}", f"{v:.1%}", f"{v:05d}" — or dt.strftime with directives such as %Y-%m-%d and %b %Y, applied to columns with map. Write small functions for Excel's multi-section codes and scaled units, round with ROUND_HALF_UP when the text must match Excel, build labels and file names with f-strings, and keep numbers as numbers wherever readers will calculate with them.

Frequently asked questions

When should I use TEXT-style formatting instead of a number format? Use text only when the result is text by nature — a label, a file name, a sentence in an email, a chart title. For cells that readers may sort, sum or chart, keep the number and apply a number format instead.

What replaces TEXT(A1, "#,##0.00")? A Python format spec: f"{value:,.2f}", or df"col".map("{:,.2f}".format) for a whole column. The comma adds thousands separators and .2f fixes two decimals.

Why does Python give 1,234 where Excel gives 1,235? Python's formatting rounds halves to even and works on the binary value, while Excel's TEXT rounds halves away from zero. Round with decimal.ROUND_HALF_UP first when the text must match Excel.

How do I format dates like TEXT(A1, "mmm yyyy")? Use df"date".dt.strftime("%b %Y"). Excel's mmm becomes %b, yyyy becomes %Y, dddd becomes %A. Month and day names follow the machine's locale unless you map them yourself.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas — the other text functions.
• Read Cell Values as Displayed in Excel with Python — the reverse: displayed text from a workbook.
• Format Numbers in Thousands and Millions in Excel with Python — scaled units as number formats.