The number() and text() functions in Attio: one conversion fails loudly, the rest fail quietly

Most of Attio's formula library works on values. Two functions work on types.

number() and text() do not calculate anything, do not compare anything, and do not read any history. They take a value that is one type and hand it back as another. That sounds like plumbing, and for one of them it is. For the other, it is the single most common source of a CRM column that looks completely fine and is completely wrong.

The useful way to think about Attio's type conversion functions is not by direction, but by failure behaviour. number() can fail. Nothing else in the category can. The word "error" appears exactly once on Attio's entire formula functions page, and it appears in the description of number(). Every other conversion — text(), currency(), checkbox(), rating() — always succeeds, on every value you give it, forever.

That is not the reassuring fact it sounds like.

This is part of our function-by-function series on Attio formula attributes — previously: if(), timeSpentIn(), dateDiff(), the ?? operator, count(), hasBeenIn(), valueSetAt(), contains(), sum(), previousValue(), dateAdd(), formatDate(), min()/max(), valueAt(), replace()/replaceAll(), avg()/median(), round()/ceil()/floor(), and()/or()/not(), length(), unique(), abs(), today()/now(), eomonth(), day()/hour(), mod(), power(), exp()/log(), split()/splitPart(), startsWith()/endsWith(), lower()/upper(), left()/right(), date()/timestamp(), and setTimezone().

color:var(--color-text-heading)]">Prefer to watch? Every function in this series gets its own short video in the [Every Attio formula function playlist. The whole library is also being built end to end in our first live session — Attio Formulas: Every Function, Built Live, free, no registration, Thursday 8 October 2026.

What number() and text() do

Both take a single argument.

text(value)
number(value)

Attio's descriptions are short enough to quote in full. text(value) converts a value to text: "Every attribute type is supported, and empty values stay empty." number(value) converts a text or numeric value to a number: "If the value can't be parsed as a number, the formula returns an error."

They belong to a Type conversion category that also contains four others:

13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">date() and timestamp() are conversions too, and we gave them [their own article, because converting into a date type is a large enough subject on its own.

One function fails loudly, five fail quietly

Here is the whole category sorted by the only thing that matters:

In every other part of this series, the silent failure has been the villain: an out-of-range splitPart() index returning empty text, a case-mismatched endsWith() returning false, left() returning nothing on a short value, hour() reading a timestamp in the wrong timezone. The pattern repeats so consistently that it is worth naming: Attio's formula language almost never tells you that you are wrong. It gives you a plausible answer and lets you ship it.

number() is the exception. It is the one function that refuses.

So the instinct most people arrive with — that number() is the risky one because it can break the column, and text() is the safe one because it always works — is precisely backwards. A function that always works cannot warn you. A function that can break is the only one capable of telling you your data is not what you think it is.

text() is six different functions

This is the part that catches people, and it is buried in an FAQ accordion at the bottom of Attio's reference page rather than in the function table itself.

text() does not have one behaviour. It has a different behaviour for each attribute type, and several of them return something other than what a person would guess from looking at the record.

Three of those six are genuine surprises. A record reference gives you an identifier. An interaction gives you a person's name rather than anything about the interaction. An actor reference silently empties out when that person leaves the company, which means an "Owner name" text column quietly develops holes every time someone is deactivated — and those holes appear in historical records, not just new ones.

The record reference trap

Of all of these, the record reference is the one worth spelling out, because it is the most natural thing in the world to try.

You want the company name as text, so you can run lower() on it and build a match key. The company is right there on the deal. So you write:

text({Company})

And you get a column of identifiers. Not an error, not a blank — a full column of populated, plausible-looking values that are of no use to anyone.

The fix is not a better conversion. It is to stop converting. Attio's own guidance is to use a path into a text attribute on the linked record instead:

{Company > Name}

That reaches through the relationship and returns the name attribute directly, which is already text. No conversion needed, and therefore no conversion to get wrong.

The general lesson is worth carrying: text() is for changing a value's type, not for reaching into a value's contents. When what you want lives on another record, navigate to it. When what you want is a different aspect of the same value — a readable date, a component of a string — there is a purpose-built function for that, and it is not text().

What text() is actually for

Given that Attio has no way to join two strings together, a reasonable question is what text() is for at all. It has two real jobs.

The first is as the on-ramp to the text functions. Every text function in the library expects text: contains(), startsWith(), endsWith(), replaceAll(), splitPart(), lower(), left(), length(). A select or status attribute is not text, so those functions have nothing to work with until you convert it — and text() on a select returns the option's title, which is exactly the thing you wanted to test.

That unlocks a pattern that is otherwise unreasonably verbose. Suppose your pipeline has Closed won, Closed lost and Closed duplicate, and you want one flag for all of them. Without conversion you are listing every option by hand and updating the formula each time someone adds a stage:

or(
  {Deal stage} == "Closed won",
  or({Deal stage} == "Closed lost", {Deal stage} == "Closed duplicate")
)

With conversion you are testing the naming convention instead, and new stages that follow it are picked up for free:

startsWith(text({Deal stage}), "Closed")

This is the startsWith() anchoring argument applied to a select attribute, and it is the single most useful thing text() does. It does come with that article's caveat attached: the comparison is case-sensitive, so a stage named closed won will not match, and a stage named Closed-out for now will.

The second job is making an if() produce one honest output type. If one branch of a condition returns a number and the other returns a word, the attribute has no single type to be. Converting the numeric branch makes the intent explicit:

if({ARR} > 0, text({ARR}), "Not yet billing")

As a rule of thumb across this series: forcing an output type beats leaving it on Auto. You want the column's type to be a decision you made, not one that was inferred from whichever branch happened to run first.

text() converts, it does not compose

This limit deserves its own heading because so many people run into it about ninety seconds after discovering text().

Attio has no concat() function and no string-joining operator. The + operator is documented as addition, with a numeric example, and nothing in the library joins two text values into one. There is no regex either.

So this, which is the obvious next thought after converting a value to text, cannot be written:

text({Company > Name}) + " — " + text({Deal stage})

There is no version of that formula. text() will happily give you either piece, and then you are stuck holding two pieces.

The practical consequences are worth knowing before you design around them. You can classify — 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">if() returning complete literal strings works fine, because each branch is one finished piece of text. You can truncate, with [left(), but you cannot add an ellipsis to show that you did. You can extract, with splitPart(), but you cannot put the parts back together. Any label that needs to combine two live values belongs in the view, where Attio can show you both columns side by side, and not in a formula.

Do not turn a date into text

The previous article in this series, on date() and timestamp(), was entirely about repairing dates that had arrived in a workspace as text. text() is the function that creates that damage on purpose.

It is worth being blunt about what you give up. A date attribute supports relative filters — "in the next 30 days", "this quarter" — grouping by month or quarter in reports, calendar views, and every date function in the library. Run it through text() and you have a string. Strings do none of that. They sort alphabetically, which for an ISO value happens to look correct and therefore hides the problem for months.

There is almost never a reason to store a date as text. If the goal is a readable format, that is what formatDate() is for, and Attio's own documentation says so: use it rather than text(), because text() returns the raw stored value.

One thing to watch when you follow that advice. The tip and the FAQ on Attio's reference page both name the function format_date(), in snake case. The function table on the same page names it formatDate(). Only formatDate() exists — copy the tip verbatim and you will get an unrecognised function. It is a documentation slip rather than a product one, but it is in the exact sentence people are most likely to copy.

The honest use for text() on a date is narrow: you need a date component as a string for a match key, or you are feeding it into a text function. Even then, check first whether formatDate() can give you the piece directly, because it usually can. Slicing a string that you just converted is a sign you converted the wrong way.

number() is the only loud function in the library

Let us give number() its due, because it is genuinely unusual.

Across every function Attio documents — all the logic, math, date, text and history functions — exactly one sentence mentions an error, and it belongs to number(): "If the value can't be parsed as a number, the formula returns an error."

That makes number() the only data-quality probe the formula language ships with. Point it at a column and it will sort your records into two piles for you: the ones that hold a number, and the ones that do not. No other function will do that. length() will measure junk quite happily. contains() will tell you a value does not contain something whether that is because it genuinely does not, or because the value is garbage.

This is why the date()/timestamp() article leaned on number() so heavily when rebuilding a non-ISO date out of splitPart() results. The parse was doing double duty: extracting the year, and confirming that what it extracted was actually a year.

You cannot catch a number() error

The natural reflex is to defend against it:

if(number({Raw amount}) > 0, number({Raw amount}), 0)

That does not work, and it is important to understand why, because the same reasoning applies to ?? and to every other guard you might reach for. The error does not happen when the result is used. It happens while the argument is being evaluated, before the if() has anything to decide about. There is no state in which number() has returned something that you can then test — it either returns a number or the formula fails.

There is also no "is this parseable" test in the library to check with beforehand. No isNumber(), no regex, nothing that inspects a string's shape.

So stop trying to hide the error and start using it. The workflow is:

• Build the number() column and let it fail where it fails.
• Filter or sort the view to find the records that errored. That is your clean-up list, and it is a real list rather than an estimate.
• Fix the source values, or exclude those records deliberately.
• Keep the original raw text column. This is the rule from the date()/timestamp() article and it applies identically here: once you have only the converted column, you cannot tell a record that failed to parse from a record that was always empty. length({Raw amount} ?? "") > 0 is the detector that keeps those two cases apart.

A visible error on eleven records is a far better outcome than eleven silent zeroes mixed into a total that someone is about to present.

Never convert an identifier to a number

Here is the mistake that survives longest, because nothing about it looks wrong.

Plenty of CRM fields are made entirely of digits and are not numbers. Postcodes and ZIP codes. Account numbers. SKUs. Phone numbers. Company registration numbers. Order references. They are labels that happen to be written with the digit characters.

Run one through number() and the leading zeros are gone:

number("01234")     →   1234

Not an error. Not a warning. A perfectly good number that is no longer a valid ZIP code, in a column that now sorts numerically and can never be repaired — because there is no concat() to pad the zeros back on. The damage is one-way.

The test is simple and it has never let me down: would you ever add two of these together? Two deal values, yes. Two ZIP codes, obviously not. If adding them is nonsense, the field is a label, and it stays text no matter how numeric it looks.

The one legitimate exception is an intermediate step. If you are pulling a year out of a date string with splitPart() and immediately doing arithmetic on it, converting is correct — the number is a quantity in that moment, and it never gets stored as an identifier.

The import repair chain

The commonest real job for these functions is an amount column that arrived from a spreadsheet as text: $1,200.00, £950, 2,400 USD.

You cannot hand that to currency(), because currency() converts a number, not text. And you cannot hand it to number() either, because the currency symbol and the thousands separator will not parse. The repair is a chain, and it has to run in the right order: strip, parse, then type.

currency(
  number(
    replaceAll(replaceAll({Raw amount}, "$", ""), ",", "")
  )
)

Working outward: replaceAll() removes the currency symbol and then the thousands separators, number() parses what is left, and currency() gives it the type. Add another replaceAll() for each stray character your particular export contains — spaces, non-breaking spaces, a trailing currency code.

The valuable part is what happens on the rows you did not anticipate. A row reading TBC or see contract or 1.200,00 in European format will not parse, and number() will error on exactly those rows. That error list is the thing you actually wanted from this exercise. It is the difference between importing a column and knowing you imported it correctly.

Two cautions. currency() does not take a currency code as an argument, so it produces a value in whatever currency the attribute is configured for — if your export mixes currencies, converting them all with one formula silently relabels them, and you want a separate currency column and a conversion step, not a formula. And set the attribute's output type explicitly rather than leaving it on Auto, so the column's type is your decision.

checkbox() is three-valued, and the text false is true

checkbox() reads, from the documentation: "Non-empty or non-zero values become true. Empty values stay empty."

Read that again with a CSV import in mind. It tests for presence, not for truth.

A spreadsheet column of booleans typically contains the literal words true and false. Every one of those cells is non-empty. By the documented rule, every one of them becomes true — including, and especially, the ones that say false. You end up with a flag that is set on every record that had any opinion at all, which is a flag that means nothing, and it will look completely reasonable in the table. Test it on one row you know says false before you trust an imported boolean column anywhere.

Never run checkbox() over imported text. Compare explicitly instead, which costs nothing and means what you say:

lower({Raw flag} ?? "") == "true"

The second half of the rule is its own trap: "Empty values stay empty." A checkbox produced by 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">checkbox() is therefore three-valued — true, false, or empty — and empty is not false. If you need a real boolean on every record, supply a default at the input, using [the ?? operator the same way you would for any other blank-safety problem:

checkbox({Legacy flag} ?? 0)

checkbox() is at its best where it was designed to be used: over a number or a currency, where "has a value" and "is true" genuinely mean the same thing. checkbox({Open pipeline}) as a "has live pipeline" flag is honest. checkbox({Some text column}) almost never is.

rating() clamps without telling you

rating() converts a number to a rating from 0 to 5, and "Numbers outside this range are clamped to the nearest valid value."

Clamping is the purest example of the quiet failure this article opened with. Feed it a 1-10 satisfaction score and every record from 5 upwards becomes a 5. No error, no blank — just a distribution that has been flattened against a ceiling, in a column that will happily go into a report and produce an average that is wrong in a specific and flattering direction.

Rescale first, then convert:

rating(round({Score 1 to 10} / 2, 0))

Use round() deliberately here — round() to nearest, ceil() to be generous, floor() to be conservative — because that choice is a real decision about how you are reporting scores, and it should be made on purpose rather than inherited from whichever function you typed first.

The same warning applies to any 0-100 scale, an NPS score, or a percentage. If your source range is not already 0-5, rating() on its own is guaranteed to be wrong and guaranteed not to say so.

Conversions do not round-trip

One last property that catches people building repair pipelines: these functions are not inverses of each other. Going out and coming back does not return you to where you started.

"1,200.00"   →  number()  →  1200   →  text()  →  "1200"
"01234"      →  number()  →  1234   →  text()  →  "1234"

Formatting is not part of a number, so it cannot survive the trip. Thousands separators, currency symbols, decimal places that were trailing zeros, leading zeros, and any surrounding text are all discarded at the number() step and there is nothing in the library that can put them back.

Practically, this means: convert once, in one direction, at the point where you need the new type — and keep the original. Do not build a chain that converts a value to text to do something to it and then converts it back, because each hop is lossy and none of the hops announce what they dropped.

CRM use cases

• Flag every closed stage with one formula. startsWith(text({Deal stage}), "Closed") instead of an or() chain that has to be edited every time someone adds a stage.
• Repair an imported amount column into a real currency attribute with the strip-parse-type chain, and use the records that error as the clean-up list.
• Audit a numeric-looking text column. Build a temporary number() attribute over it purely to find out how many records are not what they claim to be, then delete the attribute.
• Build a match key from a select. lower(text({Industry})) gives a normalised, machine-comparable version of an option title for comparison against another field.
• Turn a value into a report-ready rating with the rescale-then-convert pattern, so the 0-5 column actually reflects the underlying score.
• Produce an honest "has pipeline" checkbox from a currency total with checkbox({Open pipeline} ?? 0), where presence and truth really do coincide.
• Get a company name as text by navigating with {Company > Name} rather than converting with text({Company}).

Copy-paste formulas

Closed-stage flag from a naming convention:

startsWith(text({Deal stage}), "Closed")

Imported amount to a real currency value:

currency(
  number(
    replaceAll(replaceAll({Raw amount}, "$", ""), ",", "")
  )
)

Detector that separates a failed parse from a genuinely empty cell:

length({Raw amount} ?? "") > 0

A boolean that reads the content instead of testing for presence:

lower({Raw flag} ?? "") == "true"

A checkbox with a real false rather than an empty:

checkbox({Open pipeline} ?? 0)

A 1-10 score rescaled into a 0-5 rating:

rating(round({Score 1 to 10} / 2, 0))

Normalised match key from a select option:

lower(text({Industry}))

One honest output type across both branches:

if({ARR} > 0, text({ARR}), "Not yet billing")

Gotchas

• number() is the only function in the library documented to return an error. That makes it the only built-in data-quality probe you have. Treat the errors as output, not as a bug.
• You cannot catch that error. It is raised while the argument is evaluated, so if() and ?? cannot rescue it, and there is no isNumber() test to guard with.
• text() on a record reference returns the ID, not the name. Use a path such as {Company > Name} instead.
• text() on an actor reference returns empty for deactivated or deleted members, so an owner-name column develops holes retroactively as people leave.
• text() on an interaction returns the owning member's name, not the interaction's type or time.
• There is no concat() and no string-joining operator. + is addition. You can convert to text but you cannot combine it with anything.
• Never number() an identifier. Leading zeros are destroyed permanently, and without a join function they cannot be restored. If adding two of them together is nonsense, it is a label.
• checkbox() tests presence, not truth. The text "false" is non-empty and becomes true.
• A checkbox() result can be empty, and empty is not false. Default the input with ?? 0 if you need a genuine boolean everywhere.
• rating() clamps silently. Any source range wider than 0-5 must be rescaled first.
• currency() takes a number, not text, and carries no currency code — the attribute's configured currency is what you get, so mixed-currency imports need a real conversion step rather than a formula.
• Attio's docs call formatDate() by two names on the same page. The tip and FAQ say format_date(); only formatDate() exists.
• Conversions are lossy and do not round-trip. Convert once, in one direction, and keep the original column.

Final thoughts

The temptation with a type conversion function is to treat it as punctuation — something you sprinkle in to make an error message go away. That is how columns of record IDs and walls of clamped fives get into production.

The better frame is that type is capability, not cosmetics. An attribute's type decides which functions will accept it, which filters appear in the view, and whether a report can group by it. number() and text() are the two functions that change what a column is *able to do*, and a change that large deserves a decision rather than a reflex.

And if you take one thing from this article, make it the inversion. The conversion that can fail is the one that will tell you the truth about your data. The conversions that never fail will let you believe whatever you already believed. In a CRM, that is the more expensive of the two.

Next: year(), quarter() and month() — the date extractors Attio quietly added to the library, and why quarter() returning a number is a more useful thing than it first appears.

If your workspace is full of imported columns that are the wrong type and nobody is quite sure which, that is a fixable problem and a good place to start. We do a free Attio audit that covers exactly this kind of data-model damage, and if you would rather have it rebuilt properly than patched, our AI-native Attio sprint is the faster route.