Equivalent of TEXT function in Google Sheets, to format a number exactly how I want it

[Karl_at_Easy_La]

I have a description field, and would like to generate it based on values.  I could not find any way to format numbers, like there is DATE_FORMAT for dates.  The TEXT function in Google Sheets allows you to format exactly like you want it.

In my case, I want to format the number as 0.00.  else, Airtable will sometimes show as 213.3, or 214.57777777 which is very ugly.

[Dominic]

Not sure if this is of any help, but you can format numbers in a number field and then concatenate these fields with other text fields, assuming you are wanting the number to be part of a larger string of text. This obviously assumes that the numbers are always inserted in the same part of the text. Guessing you will have already thought of this, but just thought I'd mention it. 

Alternatively, you could manybe setup an automation that watches a {text} field then runs a script, like below, which outputs to a {Formatted text} field.

let table = base.getTable('YouTableName'); // Replace 'YourTableName' with the actual name of your table

let textField = table.getField('text');

let formattedTextField = table.getField('Formatted text');

// Query all the records

let queryResult = await table.selectRecordsAsync();

// Iterate over each record and update the 'Formatted text' field

for (let record of queryResult.records) {

let originalText = record.getCellValueAsString(textField);

if (!originalText) continue; // If there's no value in the "text" field, skip to the next record




// Find numbers and format them to two decimal places

let formattedText = originalText.replace(/(\d+(\.\d+)?)/g, (match) => {

return parseFloat(match).toFixed(2);

});




// Update the record with the formatted text

await table.updateRecordAsync(record, {

'Formatted text': formattedText

});

}

[Karl_at_Easy_La]

Hi Dominic.  Thanks for your reply. Using an automation + script for something this simple sound like an overkill. Plus I don't want to be possibly hitting my API limits because of something which should be so basic.

I'm not sure what you mean exactly by "you can format numbers in a number field and then concatenate these fields with other text fields".  However, if I have a Number field formatted as Decimal with 0.00 precision, if the value is say 213.30, if you concatenate it in a formula generating a text, the output value is 213.3 not "213.30", as expected.  This is a big pain for consistency in IDs.

[Karl_at_Easy_La]

To add to this, I found a way how to specifically format a number as say 0.00, without using automations/scripts, albeit it seems very hacky. To explain this better, lets take this example:

• Amount: The value I would like to format as text in "0.00" fashion
• Description: The field to generate the description in. Imagine I want to output say "Item Price is 230.30", if value is 230.3

Solution:

1. I created a temporary field, called AmountForFormatting.  This is a formula field, with this value: 

IF({Amount} < 1, "0") & IF({Amount} < 0.1, "0") & Round({Amount} * 100) & ""

  What this does is remove the decimal point, by multiplying it by 100. The &"" is used to turn it into text. Also, if number is less than 1 or 0.1, it adds 0 or 00, so they are padded.

2. I created a formatting field, called AmountFormatted.  This will generate the formatted version in this format "0.00". The formula for this field is:

 LEFT(Amount, LEN(Amount) - 2) & "." & MID(Amount, LEN(Amount)-1, 2)  

What this does is it takes the last 2 characters, prepends them with a dot, and then copies the remaining part.

Then, the AmountFormatted value is used in the Description field. Pretty rudimentary, and much more lengthy than it should - however it should do the trick. 

[Dominic]

Hi Karl_at_Easy_La,

Thanks for the detail on your use case. It helps to have a little more context. The script solution is more appropriate when you have long form text with randomly places numbers throughout that need formatting. 

If you wanted to format a number to a string and preserve the two decimal places, you could maybe also use a formula like:

 FLOOR({number}) & "." & 
IF(
LEN(ROUND(({number} - FLOOR({number})) * 100) & "") = 1, 
"0" & ROUND(({number} - FLOOR({number})) * 100), 
ROUND(({number} - FLOOR({number})) * 100)
)

 Which works, but I am not sure it's really any neater a solution that yours above 🙂