Número Antes E Depois

Formatting Numbers With Prefixes and Suffixes in Spreadsheets

If you have ever tried to make a spreadsheet that displays values like "R$ 1.250,00" or "50 kg" without breaking the ability to do math with those cells, you know exactly how annoying this gets. The core problem is simple: formatting and values are treated as two different things in Excel, Google Sheets, LibreOffice Calc, and pretty much every other tool that handles numbers. Change the display and the underlying value stay separate, which creates a constant tug-of-war.

Custom number formats for número antes e depois

The most common approach that actually works is using custom number formatting. In Excel, you select the cells, press Ctrl+1, open the custom category, and type a format string. For currency with the real symbol before the value and cents after, the format looks like this: "R$ "#,##0.00. For a plain label before and a unit after, something like "Item: "#,##0 "unidades" works fine. The critical thing here is that the actual cell value stays as a number. You can still SUM, AVERAGE, or reference it in formulas and everything adds up correctly. The trick that most people miss is that you can mix text and number codes in the same format string. The text inside quotes shows exactly as written, while the number codes like #,##0 control how the digit side renders. Put them in the right order and you get precisely what you want on screen.

Why this breaks in practice

I ran into a concrete issue last year when a client asked me to build a pricing table where every row had a number with both a prefix and a suffix, and then they wanted to sort by that column. Custom formatting does not change how Excel sorts. The sort uses the underlying value, which means if you have a mix of positive and negative numbers or a bunch of zeros that display differently because of custom formats, the sort order looks completely wrong. The visual order and the actual sort order diverge. The workaround I used was to add a helper column with a formula that converted the formatted display into something sortable. In that helper column I used =VALUE(SUBSTITUTE(A2;"R$ ";"")) to strip the text and get back the raw number, then sorted by that column while hiding it. It is not elegant, but it is reliable. This saved me from manually rebuilding three spreadsheets that had the same problem across different tabs.

Using formulas for labeled output instead of formatting

Another path is building the label entirely with formulas using CONCAT or the ampersand operator. Something like = "Total: " & TEXT(B2;"#.##0,00") & " BRL". This produces a text string you can paste wherever you need it. The catch is that the result is text, not a number. You cannot sort numerically, you cannot use it in SUM or VLOOKUP without extra steps, and filtering by value range becomes impossible. I recommend this only for display-only outputs like exported reports or PDFs where no further calculation is needed. Google Sheets handles this slightly better when it comes to combining formatted numbers with text inside a single cell using the TEXT function directly in the cell, but the fundamental limitation remains: once it is text, it is no longer numeric. The same principle applies to LibreOffice Calc and Numbers on Mac.

Common pitfalls that waste time

The first pitfall is mixing regional number formats. Brazil uses a comma for decimals and a period for thousands. Europe often does the opposite. If you copy a format string from an online template without adjusting the decimal separator, your numbers will display backward and every calculation downstream will be wrong. Always check what your system locale expects before pasting a custom format. The second pitfall is assuming that custom formatting applies everywhere. When you export to CSV, the formatting vanishes and only the raw value is written. If your prefix or suffix was purely cosmetic, that is fine. If you relied on the formatting to communicate meaning and someone imports the CSV elsewhere, they will see just a bare number with no context. In those cases, you need a separate text column or a proper data export step.

When custom formatting is not enough

There are scenarios where custom number formatting simply cannot deliver what you need. If you require dynamic content based on another cell value, such as showing "Pendente" or "Aprovado" next to a number depending on status, you need conditional formatting combined with a helper column, or you need to switch to a different tool entirely. Google Apps Script, Excel VBA macros, or Python scripts with pandas can automate the generation of formatted output tables where the logic is too complex for a single format string. For one-off tasks, I typically write a short Python script using openpyxl that reads raw numbers, applies the desired prefix and suffix formatting at the cell level, and writes out a file that keeps the numeric values intact while displaying the formatted versions. This usually takes about twenty minutes to set up for a one-time report, but it scales well if you need to repeat the same output format weekly.

Quick reference for common número antes e depois formats

"R$ "#,##0.00 — currency before, two decimal places after "Peso: "#,##0 "kg" — label before, unit after, whole numbers

"Qtde: "#,##0.0 "pcs" — one decimal, unit label after ""#,##0 — number followed by a space and another number, useful for comparisons inside a single cell

The main takeaway is that custom formatting is the right tool when you want the display to carry extra text while the cell remains usable for calculations. It fails when you need dynamic conditional text or when the output leaves the spreadsheet environment. Knowing which boundary you are hitting is what separates a five-minute fix from a half-day troubleshooting session.