Obtaining the equivalent to printf or String.Format in Excel

I seem to spend a large amount of my time in spreadsheets working on formulas like:

="some text '" & A1 & "', more text: '" & A2 &" etc."

It would be much quicker to use a printf or String.Format string like

=String.Format ("Some text '{0}', more text: '{1}'",A1,A2)

Is there anything like this built in to Excel, or can I call out to CLR?

No, but you can create a naive one simply enough by adding the following to a VBA module:

Public Function printf(ByVal mask As String, ParamArray tokens()) As String
    Dim i As Long
    For i = 0 To ubound(tokens)
        mask = replace$(mask, "{" & i & "}", tokens(i))
    Next
    printf = mask
End Function

...

=printf("Some text '{0}', more text: '{1}'", A1, A2)

Not really - there is the CONCATENATE function:

=CONCATENATE("some text '", A1, "', more text: '", A2, " etc.")

But it's no better than using & in my opinion.

I've updated Alex's code so you can use %s for each insertion.

Instead of:

=printf("Some text '{0}', more text: '{1}'", A1, A2)

You can use:

=printf("Some text '%s', more text: '%s'", A1, A2) 

Just like the original sprintf.

The updated code:

Public Function Printf(ByVal mask As String, ParamArray tokens()) As String
   Dim i As Long

   For i = 0 To UBound(tokens)          
      mask = Replace$(mask, "%s", tokens(i), , 1)
   Next

   Printf = mask

End Function

You can use the TEXT function -

You can store your format string somewhere in a cell like I did.

I have value of "BUY "#" CREDITS" in my D1 cell. In my A5 cell I have a value of 5000. When I want to display the formatted string I use =TEXT(A5, $D$1).

It will set the value of cell to BUY 5000 CREDITS.

"Bullet proof" version of the one provided by @AlexK, allowing for:

• Escaped tokens printf("{0}{1}", "test {1}", 2) -> "test {1}2" (NOT "test 22")
• Positional syntax printf("{}{}", "a", "b") -> "ab"

Public Function printf(ByVal mask As String, ParamArray tokens() As Variant) As String
    Dim i As Long
    For i = 0 To UBound(tokens)
        Dim escapedToken As String
        escapedToken = Replace$(tokens(i), "}", "\}") 'only need to replace closing bracket since {i\} is already invalid
        If InStr(1, mask, "{}") <> 0 Then
            'use positional mode {}
            mask = Replace$(mask, "{}", escapedToken, Count:=1)
         
        Else
            'use indexed mode {i}
            mask = Replace$(mask, "{" & i & "}", escapedToken)
        
        End If
    Next
    mask = Replace$(mask, "\}", "}")
    printf = mask
End Function

Usage as before:

=printf("Some text '{0}', more text: '{1}'", A1, A2)

OR positional (left to right)

=printf("Some text '{}', more text: '{}'", A1, A2)

Note as @CristianBuse points out in the comments, this implementation will still be tripped up by a mask like {0\}{1} which results in {0}{foo} not {0\}{foo}, workaround is don't use \} in your mask.

---

_{*behaviour using mixed syntax =printf("Some text '{}', more text: '{1}'", A1, A2) is undefined but the replacements go left to right so "{}{2}{1}" is equivalent to "{0}{2}{1}". "{}{1}{}" won't do what you want}

Ok I gave a VBA solution but here's a pure Excel version using the relatively new LAMBDA functions, no VBA.

---

_{Note I've posted an alternate version with comma separated args PRINTF(mask, arg1, arg2, ...) over on Code Review, however I'll try to keep this one canonical and up to date as new better options become available}

---

To achieve this I have created a named function with signature printf(mask, tokensArray) where:

• mask is a string you want to format, containing positional {} or indexed {i} interpolation locations.
• tokensArray is the set of values to be substituted in, supplied as either a 1D Range (row or column), an array (hardcoded or returned from a function) or a single value.

... and which returns a formatted string. Called from a cell like:

=printf("Some text '{1}', more text: '{2}'", A1:A2) //continuous 1D row/col
=printf("Some text '{1}', more text: '{2}'", {"foo","bar"}) //hardcoded array
=printf("Single Value {1}", "foo")

or with positional args (left to right)

=printf("Some text '{}', more text: '{}'", A1:A2)

---

Define these two functions by entering them in the name manager (see LAMBDA function MSDN docs for detailed instructions, though this link will die I'm sure...) :

Param	Value
Name	ReplaceRecursive
Scope	Workbook
Comment	Recursively substitutes {} or {i} with tokens from the tokens list, which it escapes one by one leaving } in the result string
Refers To	=LAMBDA(mask,tokens,i,tokenCount, IF(i >tokenCount, mask, LET(token, INDEX(tokens,i),escapedToken,SUBSTITUTE(token,"}", "\}"),inIndexedMode,ISERROR(FIND("{}",mask,1)),substituted, IF(inIndexedMode, SUBSTITUTE(mask,"{"&i&"}", escapedToken),SUBSTITUTE(mask, "{}", escapedToken,1) ),ReplaceRecursive(substituted,tokens,i+1,tokenCount))))

=LAMBDA(
    mask,
    tokens,
    i,
    tokenCount,
    IF(
        i > tokenCount,
        mask,
        LET(
            token,
            INDEX(
                tokens,
                i
            ),
            escapedToken,
            SUBSTITUTE(
                token,
                "}",
                "\}"
            ),
            inIndexedMode,
            ISERROR(
                FIND(
                    "{}",
                    mask,
                    1
                )
            ),
            substituted,
            IF(
                inIndexedMode,
                SUBSTITUTE(
                    mask,
                    "{" & i & "}",
                    escapedToken
                ),
                SUBSTITUTE(
                    mask,
                    "{}",
                    escapedToken,
                    1
                )
            ),
            ReplaceRecursive(
                substituted,
                tokens,
                i + 1,
                tokenCount
            )
        )
    )
)

Param	Value
Name	printf
Scope	Workbook
Comment	printf(mask: str, tokensArray: {array,} | range | str ) -> str | mask: string to substitute tokens into e.g. "Hello {}, {}" or "Hello {2}, {1}" (1-indexed) | tokensArray: 1D range or array of tokens, e.g. "world" or {"foo","bar"} or A1:A5
Refers To	=LAMBDA(mask,tokensArray,LET(r,ROWS(tokensArray), c, COLUMNS(tokensArray), length, MAX(r,c), IF(AND(r>1, c>1), "tokensArray must be 1 dimensional", SUBSTITUTE(ReplaceRecursive(mask, tokensArray, 1, length), "\}","}"))))

=LAMBDA(
    mask,
    tokensArray,
    LET(
        r,
        ROWS(
            tokensArray
        ),
        c,
        COLUMNS(
            tokensArray
        ),
        length,
        MAX(
            r,
            c
        ),
        IF(
            AND(
                r > 1,
                c > 1
            ),
            "tokensArray must be 1 dimensional",
            SUBSTITUTE(
                ReplaceRecursive(
                    mask,
                    tokensArray,
                    1,
                    length
                ),
                "\}",
                "}"
            )
        )
    )
)

---

There will be improvements to Excel's LAMBDA functions that make this easier to write I'm sure, but this recursive approach is good for now I think.

Interesting question... I was thinking the same.. how to build a String without having to cut off long sentence in some single parts between Numbers.

And because I don't want to create a VBA function (which will be much smarter), here is my solution...

SUBSTITUTE(P253;O252;"A1A";1)

where

• P253 is my long string with "A1A" for #1 label
• O252 is the Value of #1 label

And so on... (even if not my need) if other values...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(P253;O251;"A1A";1);O252;"A2A";1);O253;"A2A";1)

Well, I presume, even in #C, there should be multi substitution primitive function for sprintf(....%s, %s, %d..).