[Google Sheets Display Format] A story about how what you see is important too

There are many differences between Excel and Google Sheets, but this time, let's talk about display formats, which are easy to misunderstand (and prone to causing errors).

If you don't know the differences in how display formats are handled between Excel and Google Sheets,

if you create formulas in Google Sheets with the same mindset as Excel, or conversely, create formulas in Excel using the same approach as Google Sheets, you might get unexpected results and

"Why did that happen!" (Conte 55-go)

you might end up feeling confused lol

So, let's make sure to learn the key points in this note! While you're at it, go ahead and press the Like button too lol

Last week's note was Recursive formulas written with LET and LAMBDA where I made a dice simulation.

By the way, regarding this title, it's like an answer song to the one I wrote before, "GAS: A story about how the important things are invisible" lol

Google Sheets string functions treat the information displayed in the cell as-is

This is what I want to convey 👆.

Behavior of Google Sheets string functions

As a result of this, don't you feel a sense of discomfort?

=LEFT(A2)

When you get 1 character from the left of cell A2 using the LEFT function, ¥ is
*If the second argument is omitted in the LEFT function, it is treated as 1

=MID(A2,5,1)

When you get 1 character starting from the 5th position from the left of cell A2 using the MID function, , is

=SPLIT(A2,",")

When you split using the SPLIT function with , as the delimiter, ¥199 and 800 are

=LEN(A2)

When you get the character count with the LEN function, the ¥ and , are also counted as 1 character each, and 8 is

returned as the result.

"Huh??" If you are thinking that, you are someone who has been using Excel.

The ¥199,800 in cell A2 is only because the display format is set to 'Currency (JPY)', so the leading ¥ and the 3-digit separator , are just being displayed visually; the actual state (true value) of cell A2 is the numerical value 199800 shown in the formula bar 199800 because it is a number.

In other words, in the case of Google Sheets, it processes the result of the applied display format shown in the cell, rather than the actual underlying value!

Excel's string functions are not affected by display formats

On the other hand, the case of Excel is different.

=LEFT(A2)

When you get the first character from the left of 199800 with the LEFT function, 1 is

=MID(A2,5,1)

When you get 1 character from the 5th position from the left of 199800 with the MID function, 0 is

=TEXTSPLIT(A2,",")

Even if you try to split 199800 using the TEXTSPLIT function with , as the delimiter, since , does not exist in 199800, the stringified 199800 is

=LEN(A2)

When you get the character count with the LEN function, the ¥ and , are ignored, and the character count of 199800, 6 is

returned as the result.

For those accustomed to Excel, this is taken for granted.

What is displayed in a cell is something that has had a "display format" applied (what you might call enhanced like an image on Instagram), and I think the feeling is that the true value (the raw state) shown in the formula bar is the real one.

Excel returns results based on the true value (199800) without being misled by the cell's display format is what that means.

Even though the same function is used on the same value, the results returned by Google Sheets and Excel are different!

This is a pitfall, isn't it?

String functions in Google Sheets are recalculated and their results change when the display format is changed.

The fact that Google Sheets string functions target what is "visible" in the cell after the display format has been applied means that

if you switch the display format, the function result changes

is what that means.

Please watch the video above. When I changed the display of what was initially 199800 to "Currency" using the ¥ button on the menu bar, all the function results changed.

Furthermore, when I changed the display format from
199800 → ¥199,800.00 → ¥199,800.0 → ¥199,800

, the character count obtained by the LEN function changed in tandem to

6 → 11 → 10 → 8

as you can see. (At the end, the display of decimal places is gone, so the two characters ".0" have been removed.)

It's interesting, but the fact that function results change based on the display format is quite scary , and for those used to Excel, I think it feels quite unnatural.

On the other hand, for a generation whose first experience with spreadsheets is Google Sheets, this might be the norm, and they might feel that "Isn't it strange that Excel's appearance and results are different?"

Let's check the case for dates and times.

So, what about dates and times, where the display format and the content (serial value) are even more different than with numbers?

Let's check it out.

In Excel, dates appear as date formats even when viewed in the formula bar. However, when handled by string functions, they target the serial value.

Let's look at Excel first.

For example, if date data like 2024/12/07 is in A2, even if you select this cell and look at the contents in the formula bar, it is 2024/12/07.

So, is 2024/12/07 the true value? That is somewhat subtle, and when used within a formula,

2024/12/07 is treated as the serial value 45633.

Therefore, if you get the first character on the left with the LEFT function, it is 4, the rightmost character obtained with the RIGHT function is 3, and the character count of the LEN function is 5, all returning results targeting the serial value 45633.

The currency case mentioned earlier is fine because the true value is displayed in the formula bar, but the fact that dates are handled as the number 45633, which is not displayed anywhere at a glance, might be quite difficult for beginners to understand.

What is a serial value?? For those who are wondering, I touched on serial values a little in the note about creating a calendar with formulas, so please refer to that.

In Google Sheets, date data can be handled by string functions as what is displayed.

Even if you try to extract only the year from 2024/12/07 in cell A2 by using =LEFT(A2,4), it is meaningless in Excel, which targets the serial value.

However, in Google Sheets, this actually works.

Although you need to be careful that the 2024 extracted by the string function is a string composed of numbers, the year part 2024 was extracted with =LEFT(A2,4) lol

=SPLIT(A2,"/ ") If you do this, you can get the year, month, and day as numbers side by side.

If the date data is displayed as 2024/12/7 (Sat),

=SPLIT(A2,"年月日()")

If you do this, you can get the year, month, day, and day of the week all at once, and

=MID(A2,LEN(A2)-1,1)

So, you can extract only the Saturday of the day of the week.

I previously introduced how to split date data into year, month, and day using the SPLIT function in Super advanced examples of the SPLIT function.

With Google Sheets, you can easily split date and time into date and time.

Furthermore, if it is date and time display, you can easily split it into date and time by taking advantage of the fact that there is a half-width space between the date and time.

For example, if cell A2 contains the date and time data 2024/12/07 17:35:10 (displayed as is)

=SPLIT(A2," ")

Like this, just by SPLITting with a half-width space as the delimiter, you can split the date and time into date and time in one go.

It's super easy, isn't it?

Understand date functions and use them with caution

The methods introduced above for extracting the day of the week, splitting into year, month, and day, or splitting into date and time using display formats and the SPLIT function are convenient and easy, but there is a risk that they will not work in Excel and will not return the correct result if the display format changes.

Please understand that point, and use them after thoroughly understanding the standard approach (using date functions).

Let's check other display format cases

Let's check if display formats other than currency, date, and date-time can also be handled with string functions as they appear.

Percentage display, custom number format

Percentage display and custom number format are also affected.

The result of the RIGHT function that gets the last character is easy to understand; the last character of the percentage display is %, and for those who set "0 items" in the custom number format, the last character is items.

In the case of special custom number formats

So, what happens if you use a custom number format like

like this?

This is a way of writing that separates the display format ; before and after for when the number is positive and when it is negative.

#,##0　 This part specifies the comma separator for 3-digit numbers

_) This means to insert a space with the same width as the character following the _. In this case, by inserting a space with the same width as
), you can align the right edge of the numbers for both positive and negative values.

[Red]　 You can easily
apply colors based on conditions in the formatting, similar to conditional formatting.

In this case, since [Red](#,##0)　 is specified after the semicolon, it is set to display negative numbers in red, enclosed in parentheses, with 3-digit comma separators.

If you enter 8 in a cell, since this is a positive number

a space equivalent to ) is inserted after the number like this, but it seems to be counted as 1 character, so if you get the last character on the right with the RIGHT function, it is a half-width space , and the number of characters obtained with LEN is 2 characters.

If you enter -15, the displayed (15) is targeted, and the beginning and end become ( and ), respectively, resulting in a total of 4 characters including the parentheses.

Let's look at another special display format, cell filling.

* is a display format setting that repeats the character following it to fill the width of the cell.

Therefore, if you set it to 0*/, it will repeat ///// to the right of the entered number to fill the gap in the cell, and if you set it to */0, it will fill the left side of the number with ////.

In this way, the number of repetitions of / increases or decreases visually as you change the cell width, but there is no change in the number of characters obtained by the LEN function.

It seems that in the case of this display format, the repeating character is counted as one /, and the visual repetition does not seem to affect the formula side.

When you retrieve it as text while keeping the cell's appearance using the TO_TEXT function to check, you can see that the content is 200/, confirming that there is indeed only one /.

Checkboxes and boolean values are not affected by the display format

What about checkboxes?

It seems that checkboxes are treated by string manipulation functions as FALSE (5 characters) when unchecked, and TRUE (4 characters) when checked.

Whether it is displayed as a checkbox does not seem to have an effect. Is this because checkboxes are more of a data validation feature than a display format?

String display formats do not affect string manipulation functions

Actually, even with the same display format, in the case of a string display format specified by @, it does not affect string manipulation functions.

Above, we are adding @-sama to the end of the string using a custom display format to append "sama".

However, the last character retrieved by RIGHT is the "chu" from Tanaka, and the character count is 2 characters, meaning the "sama" added by the display format is not recognized by string functions.

This means that while the display format of numbers (including dates and times) affects string functions, the display format of strings or boolean values does not.

Functions affected by numeric display formats

Next, let's check the major functions affected by the display format of numbers (including dates and times).

String functions are affected

First, the LEN function, which retrieves the character count mentioned from the start, and the LEFT, MID, and RIGHT functions, which extract specified parts from a string, are functions that are affected.

String search and replacement functions are also affected

FIND and SEARCH, which are string search functions, as well as SUBSTITUTE and REPLACE, which are replacement-type functions, also process values after the display format has been applied.

The fact that the underlying content is a number does not change, and REGEX functions cannot be used.

Even if a date display format makes it look like a string, such as December 7, 2024 (Sat), the underlying content remains a serial value (number).

If you evaluate the cell with the ISTEXT function, which checks if a cell is a string, it returns FALSE, while both the ISDATE function, which checks if it is a date, and the ISNUMBER function, which checks if it is a number, return TRUE.

Therefore, REGEX-type functions that cannot take numbers as arguments, such as REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE, cannot be used.

String concatenation and string splitting functions differ depending on the function.

String concatenation functions differ depending on the function.

CONCAT function and CONCATENATE function, which are simple concatenation functions that do not insert delimiters, are not affected by the display format and convert dates back to serial values for concatenation.

Although it is not a function, concatenation using the & operator works the same way.

On the other hand, the JOIN function and TEXTJOIN function, which concatenate strings with delimiters, and the SPLIT function, which splits strings by a specified delimiter, process the values after the display format has been applied. (They are affected.)

REPT function is also affected by the display format and repeats the value exactly as it appears on the cell.

DATEVALUE is affected by the display format.

There is a function called DATEVALUE that converts a date string into a serial value.

It is a bit confusing, but this is a function that converts a string in date format into a serial value, and it cannot originally be used on actual date data (which is not a string). (This is not possible in Excel.)

However, in the case of Google Sheets, even if it is actual date data, if the display format is yyyy/MM/dd, yyyy-MM-dd, or yyyy年MM月dd日, the DATEVALUE function can be used.

On the other hand, if it is yyyy年MM月dd日(ddd) which displays day-of-the-week information, the DATEVALUE function will not work and will return an error. The same applies if it is just the serial value.

Even if the underlying information is the same, this is an example where the function returns an error depending on the display format.

The EXACT function is affected by the display format

The function that determines the exact match of two pieces of text is the EXACT function.

In the case of Excel, even if the display formats are different, if the contents are the same, the EXACT function returns TRUE.

However, in the case of Google Sheets, the EXACT function is affected by the numeric display format.

Just by changing the display format,

EXACT gets easily fooled...

What does 'exact' even mean?? is how it feels.

Functions other than string functions are generally not affected

Functions such as COUNTIF, XLOOKUP, and XMATCH can search for cells that "contain" a specified string using wildcards. *For XLOOKUP and XMATCH, you need to specify the search mode as 2.

However, unlike string functions, these functions are not affected by the display format.

Therefore, even if you search a data range displayed as 2024年〇月〇日（〇） with "*年*", none of them will be hit.

The same applies to VLOOKUP, HLOOKUP, and the MATCH function.

The SORT function and UNIQUE function are also not affected by the display format.

Above, strings like ZZZ and FFF are added to the left of the numbers using custom number formats for each cell, but you can see that they are sorted and deduplicated without being affected by that.

QUERY function In the case of sorting by order by, the display format itself was overwritten by that of the first data item.

I believe this is because the QUERY function determines and processes column types.

For now, you can basically assume that functions other than string-related ones are not affected by display formats.

IMPORTRANGE is linked to the display format of the referenced cell range

The IMPORTRANGE function is a useful function for synchronizing other spreadsheets, but it basically only retrieves the numbers or strings within cells and cannot retrieve font settings such as cell or text color and bolding.

However, it seems that display formats are surprisingly linked, so if you change the display format of the original sheet, the results output by the IMPORTRANGE function will also change accordingly.

Along with this, you can see that the number of characters retrieved by the LEN function from the range output by IMPORTRANGE also changes.

Of course, the text color set via display format is not linked, and if you directly set the display format of the cell range where the IMPORTRANGE results are output, that will take priority.

It is interesting that IMPORTRANGE also synchronizes display formats.

By the way, IMPORTRANGE works very well with named ranges and tables

I recommend this way of writing it.

Features affected by display formats

Although they are not functions, there are also several features affected by display formats.

Let's take a look at these as well.

Find and replace is affected by display formats

A typical example of something affected by display formats is the "Find" feature.

The simple search that can be triggered with Ctrl + F as shown above,

Ctrl + H search and replace can both search for characters and numbers added via display format.

Furthermore,

you can even replace the character 'year' added by the display format.

However, be careful, as date data can sometimes become converted to text like this.

While a YYYY-MM-DD display will be converted to text, if it is a date with a yyyy/MM/dd display, you can even do things like bulk change 2024 to 2025 without it becoming text.

Dates might not be hit by search and replace if their display formats differ.

In Excel, if there are several cells containing the same date but with different display formats, they all look the same in the formula bar (in the case above, 2024/12/7), and you can find them all with a search.

However, in Google Sheets, if the display format is interpreted as a date type, the formula bar will also show that date format.

Only the version with the day of the week, such as December 7, 2024 (Sat), shows as 2024/12/07 in the formula bar, but you can see that for others, the cell display and the formula bar display are the same.

This also affects searching. In other words, if you search for 2024/12/7...

Like this, only cells with the 2024/12/7 display format (without the leading zero) will be hit, and you cannot find dates with other display formats that should be the same date, such as 2024/12/07, 2024-12-07, or December 7, 2024.

This can be a bit troublesome.

By checking the search option 'Within formulas', you can search even if the display is different, provided the formula bar display is the same.

By searching for 2024/12/07 with 'Also search within formulas' checked, you can see that December 7, 2024 (Sat), which shows as 2024/12/07 in the formula bar, is also hit in the search.

However, you cannot hit items where the display in the formula bar is different, such as 2024/12/7, 2024-12-07, or December 7, 2024.

This is quite inconvenient.

I haven't found a good solution yet, so if I discover a good method, I would like to introduce it on note.

"Split text to columns" is also affected by display format

Also, the "Split text to columns" feature allows you to specify characters added by display format as delimiters.

For example, if you split date data by /

You can do something similar to the SPLIT function like this.

By the way, the method of splitting dates by / is also possible in Excel.

Find and replace also affects string display formats

Incidentally, while string functions like FIND, SUBSTITUTE, and SPLIT can only handle the results added by numeric display formats, the Find and replace feature also targets string display formats.

@-sama You can see that the A2:A5 cells above, which have the display format applied, hit on "sama" in a simple search.

This is a point that differs from Excel.

However, you need to be careful when replacing characters added by string display formats,

like this, where you intend to replace "sama" with "dono"

Yamada-sama -> Yamada-donosama

and it ends up like that lol

This is because while the "@sama" display format added "sama" could be replaced with "dono", the "@sama" display format itself did not change, so "sama" is still attached to "Yamada-dono" after the replacement.

It's meaningless unless you fix the display format itself.

What is the ultimate makeup remover function to strip away display formats?

I have introduced how display formats (especially date and numeric display formats) in Google Sheets have a significant impact on features like search and replace, as well as the results of string functions.

So, the true form with display formats stripped away, or in other words, the bare, makeup-removed value—what should you do if you want to get function results based on that?

*By the way, cleaning up data is actually expressed using the word cleansing.

Stripping away unnecessary display formats: The TO_PURE_NUMBER function

The function that can be called the ultimate makeup remover, stripping away what was added by display formats, is the TO_PURE_NUMBER function.

This is a function not found in Excel, and its description also states:

It says that.

Let's try it out for now.

As you can see, unnecessary characters added by display formats, decimal point display settings, currency symbols like ¥, and 3-digit separator commas are removed, and dates, regardless of their display format, are all converted to serial values, becoming pure numbers!

Furthermore, this TO_PURE_NUMBER function can be used in combination with ARRAYFORMULA.

Blanks are returned as blanks, so without worrying about blank cells,

you can process them all at once with a formula like this.

The TO_PURE_NUMBER function can also strip away string display formats

Since it has "NUMBER" in the name, you might feel like it can only be used for numbers, but this function is also effective for strings.

The "Mr./Ms." added by the @ display format is removed, becoming the original content of the cell.

Cells containing a mix of strings and numbers, or strings consisting only of numbers, are not automatically converted to numbers, but are output 00156 as a string as is.

Furthermore, checkboxes are processed as FALSE/TRUE, errors remain as errors, and chips are also handled without any issues.

The TO_PURE_NUMBER function strips away all display formats. Could we call it the ultimate makeup-removing bare-face function? lol

Comparison with the VALUE function and N function

For those who aren't familiar with the TO_PURE_NUMBER function, you might wonder about using the VALUE function or the N function which are also available in Excel, to convert to numbers.

While both are functions for converting to numbers,

As shown here, the VALUE function cannot convert values that have characters added via display formats, nor can it convert dates with days of the week into serial values.

On the other hand, the N function supports all number and date display formats, but it is troublesome that both treat blanks as 0.

Neither handles strings well.

The VALUE function returns a #VALUE! error when targeting a string, and the N function turns strings into 0.

The N function even treats strings composed of numbers like 00156 as strings and turns them into 0.

When it comes to stripping away display formats, you can see that the TO_PURE_NUMBER function is the best choice.

Getting the character count of the true value (number) without considering display formats

Therefore, in Google Sheets, if you want to get the character count using the LEN function without considering display formats, targeting the true value (number) or the serial value for dates,

you use a formula like this.

If you want to process multiple cells at once by combining it with ARRAYFORMULA,

it becomes a formula like this.

You can see that the resulting numbers are different when compared to a formula that uses the LEN function directly.

When you want to target the true value while using LEFT, MID, or RIGHT functions, combining it with the TO_PURE_NUMBER function works just fine!

Getting values in GAS while considering the display format

Let's also touch on how to get the appearance with the display format applied using GAS (Google Apps Script).

getDisplayValue() to get the cell exactly as it looks

When getting spreadsheet values in GAS, you normally use getValue() or getValues() from the Range class.

For example, if you want to get the values of the currently selected cell range and write them to the log,

function displayFormatTest1() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  const values = range.getValues();

  console.log(values);
}

you would probably write code like this, but if you use this to get a cell range where display formats are applied (or that contains dates),

you get a result like this.

Numbers are retrieved as pure numbers before various display formats are added. This is the same as when using the TO_PURE_NUMBER function.

On the other hand, for dates, regardless of the display format, if the underlying date is 2024/12/07, it returns

as the result.

This is because when a cell contains date data, GAS automatically determines it to be a date type (Date object).

While this can be convenient depending on the process, it can be a bit troublesome when you simply want to copy it into an email, output it to a document, or combine the date with other text to create a sentence.

A separate method is provided for when you want to get the value exactly as it appears on the cell with the date display or display format applied.

That is getDisplayValue() or getDisplayValues().

The code to execute this is

function displayFormatTest2() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  const displayValues = range.getDisplayValues();

  console.log(displayValues);
}

If you prepare it like this and run it,

In this way, you can get the values or dates exactly as they appear in the cells. *Note that repeating characters in cell-filling display formats (like the / in the image) are still treated as a single character.

However, please note that all results are strings.

[ [ 'UUU1' ],
  [ 'WWW4' ],
  [ '5.00 ' ],
  [ '16.44 ' ],
  [ '(8.00)' ],
  [ '¥199,800' ],
  [ '/91' ],
  [ '2024/12/07' ],
  [ '2024/12/7' ],
  [ '2024-12-07' ],
  [ '2024年12月7日' ],
  [ '2024年12月7日(土)' ] ]

Even so, there are times when you want to get the values in GAS exactly as they look in the cells!, so it is a convenient method.

getDisplayValue() and getDisplayValues() can be said to be methods that perform processing similar to the TO_TEXT function in sheet functions!

With this, you can handle both cases: when you want to use the result with the display format applied as-is from GAS, and when you want to use the true value without the display format!

Summary of Google Sheets Display Formatting

Finally, here is a summary of the handling of "display formats" in Google Sheets introduced this time.

Since this is a niche topic, there isn't much information about it online, but "Ikinari Kotaeru Biboroku" covered it before (in September 2023) lol. As expected.

When using string functions in Google Sheets on dates or numbers with display formats, be aware that it works differently than in Excel!

The topic for next time is undecided... I'm busy at the end of the year, so it might be a lighter topic.