Why does this work? Replacing IF with Text

[Aussiebear]

Long ago we learned that a simple If formula can produce a "true or false" result

=IF(B2>=0,"favourable","adverse")

or another alternative (where say cell J5 contains the options "favourable","adverse")

=TEXT(B2,$J$5)

but then I find that the following formula also works

=TEXT(B2,"""favourable"";""adverse""")

Can someone explain to me what is the purpose of all the "'s in the last formula please?

[Paul_Hossler]

To insert a quote (ASCII 34) into a string as a literal quote, you need to double it

Simpler example --

s = "ABC" returns ABC

but

s = "A""B""C" returns A"B"C

[Aussiebear]

So in essence its:

"favourable" and "adverse" at the lowest level, to signify the strings

""favourable"" and ""Adverse"" when you step up a level in the formula then as you indicated we need to double them

("""favourable"";""adverse""") at the top level of the formula

[Paul_Hossler]

What I think ...

In

=TEXT(B9,"""favourable"";""adverse""")

the """favourable"";""adverse""" is one formatting string, i.e. pos;neg;zero

Removing the text markers of the formatting string at the beginning and end gives ""favourable"";""adverse"" (pos and neg)

The double quotes get converted by Excel into a single quote (ASCII 34) as a text character as part of the text string

So a positive B9 returns the text favourable and a negative B9 returns the text adverse


In

=TEXT($B16,$F$13)

F13 Excel knows that F13 is already a string made up of '"favourable";"adverse" in which the " is just a text character

Did you notice that in the formula bar there is a leading single quote Excel added to force text

' "favourable";"adverse"

[Aussiebear]

This is the link to the Site where I became interested in this matter. There is no single quote used there from what I can see. https://exceloffthegrid.com/dynamic-text-number-formats/.

[Aflatoon]

TEXT requires a format string in quotes:

=TEXT(A1,"format codes here")

That string uses the same formats - other than colour codes - as if you were entering them in the custom number format box in the Format Cells dialog. Many/most letters can be entered normally and will be treated as regular text but some are built-in format codes - e.g. 'y' for year, 'd' for day and so on (and more surprisingly letters like 'e' and 'b')- so they need to be escaped to be treated as literal text. You can escape a single character by preceding it with a backslash. For longer text, it is easier to just enclose it in quotes. So, in the number format code dialog, you would enter:

"favourable";"adverse"

However, since here this is being used within a quoted text string as a formula argument, you have to escape each quote by doubling it so that the formula interpreter knows that you aren't trying to end the text argument - hence it becomes:

=TEXT(A1,"""favourable"";""adverse""")

where the red quotes are the normal quotes enclosing the format code string and the blue quotes are the escaped quote marks.

You could also enter that as:

=TEXT(A1,"\f\a\v\o\u\r\a\b\l\e;\a\d\v\e\r\s\e")

or by only escaping the necessary letters:

=TEXT(A1,"favoura\bl\e;a\dv\er\s\e")

[Aussiebear]

Okay, I think..... I was following along until the last three lines. Anyway I'm going to mark this as Solved. Thank you to both Paul and Aflatoon for your assistance ( and your patience).

[Aflatoon]

Maybe a simpler example will clarify the last bit. The letter 'k' is not a built-in format code for anything, so if you have a number in Excel that is actually in thousands (e.g. your cell contains 400 but that's actually representing 400,000), you could use the custom number format:

0k

which will display 400 as 400k. You could therefore do the equivalent using TEXT like this: =TEXT(A1,"0k")

Now, if the number were actually in millions, you might think you could use:

0m

as the format code but, because 'm' is a built-in character code for months (or minutes, depending on the context), you can't. You need to escape the character either by using \m or by using "m" instead:
0\m

or:

0"m"

The backslash option will only escape one character, whereas the quotes will escape everything inside them, so using quotes is easier for more than one letter. For example, if you actually wanted the word 'millions' to appear, it's easier to use:

0"millions"

as the code than it is to use:

0\m\i\l\l\i\o\n\s

Does that help explain the last bit of my previous post?

[Aussiebear]

Hmmm... :reading: Nope, that's probably a letter needing an escape.... maybe this one.... or this one over here. I'm not sure anymore!!!