Nested strings for text(,format) conversion

Question

How do you use Text() with a format that has a string inside it ?

=TEXT(A1,"Comfi+"#0"(JO)";"Comfi-"#0"(JO)")

Tried """ both the inner string :

 =TEXT(A1," """Comfi+"""#0"""(JO)""";"""Comfi-"""#0"(JO)""" ")

Same result with &char(34)&

Similar issue here, but I couldn't transpose the solution to my problem : How to create strings containing double quotes in Excel formulas?

Post Solution edit :

Building an almanac/calendar with the following (now fixed)formula :

=CONCATENATE(
    TEXT(Format!K25,"d"),
    "              J+",
    Format!S25,
    "               ",
    TEXT(Format!AA25,"""Comfi+""#0""(JO)"";""Comfi-""#0""(JO)"""),
    "               ",
    Format!AI25
)

Giving the following output in each cell :

           9
        J+70               
Comfi+21(JO)               
         CRG

@Ron Rosenfeld solved it ! The final string looks like "Comfi+10JO" it's an extract of a larger concatenate() with various formats (date, numbers, string) — Adav, Mar 07 '18 at 12:35

score 1 · Answer 1 · 2018-03-07T11:49:38.117

1

Personally, doubling up double-quotes within a quoted string is something I try to avoid at all costs. You can 'escape' the text into literals with a backslash.

=TEXT(A1,"\C\o\m\f\i+#0\(\J\O\);\C\o\m\f\i-#0\(\J\O\)")
'alternately
="Comfi"&text(a1, "+#0;-#0")&"(JO)"

Not all of those actually need to be escaped; only reserved characters. However, I usually escape them all and let Excel sort them out.

edited Mar 07 '18 at 11:49

answered Mar 07 '18 at 11:22

I honestly have no idea. I'm only showing the process. – Mar 07 '18 at 11:25

score 1 · Accepted Answer · answered Mar 07 '18 at 12:28

1

You've got too many quotation marks inside:

=TEXT(A1,"""Comfi+""#0""(JO)"";""Comfi-""#0""(JO)""")

You were tripling many of the inside quotation marks.

answered Mar 07 '18 at 12:28

Ron Rosenfeld

53,870
7
28
60

Nested strings for text(,format) conversion

2 Answers2