Obtaining the equivalent to printf or String.Format in Excel

Question

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?

There's a plugin for that: http://seotoolsforexcel.com/format/ — Niels Bosma, Sep 23 '15 at 05:23
For others finding this from Google: A deleted answer by @PMBottas links to [this VB `sprintf()`](http://www.freevbcode.com/ShowCode.asp?ID=5014) ([Wayback direct code link](https://web.archive.org/web/20170914101712/http://www.freevbcode.com/imagesvr_ce/184390/source/printf.zip)), which worked for me. I imported the included `printf.bas` into a Word VBA project and was able to use the `SPrintF` function without trouble. I was also able to use it from Excel, e.g., `=sprintf("Hello, %s!","World")`. — cxw, Nov 08 '18 at 17:31

score 92 · Accepted Answer · edited Sep 22 '21 at 12:34

92

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)

edited Sep 22 '21 at 12:34

Peter Mortensen

30,738
21
105
131

answered Jun 21 '13 at 11:12

Alex K.

171,639
30
264
288

10

Keep in mind that this is unsafe for instances where one of your replaced tokens may contain another placeholder. (Where `A1` is equal to `'test {1}'` for instance. – Marius Jun 11 '14 at 13:40
2

Marius is right, to make it bullet-proof you could escape `{` and `}` in the tokens array before the replacement, and afterwards unescape them (for example, replace `{` by `{*` and `}` by `*}`, then run the `for` loop, and afterwards replace `{*` and `*}` back to `{` and `}` before you return the string). – Matt Feb 26 '18 at 12:09
Another thing - since this has more similarities to `String.Format` in .NET, why not name it `StrFormat`? Reason is, below we can find another answer where the C-style printf is implemented from your code ... – Matt Feb 26 '18 at 12:19
1

You may want `mask` to be passed by value, e.g. `ByVal mask As String` – gbasin Jul 01 '20 at 21:08
1

@Marius & Matt - implemented the "Bullet Proof" version, as well as the ability to leave out the index: https://stackoverflow.com/a/67858844/6609896 – Greedo Jun 06 '21 at 12:00
With the new Excel LAMBDA function this answer is IMO outdated - the VBA still works but you can now create an equivalent without VBA (see [my answer](https://stackoverflow.com/a/69266041/6609896)) – Greedo Sep 21 '21 at 10:10

score 20 · Answer 2 · edited Sep 22 '21 at 11:03

20

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.

edited Sep 22 '21 at 11:03

Peter Mortensen

30,738
21
105
131

answered Jun 21 '13 at 11:10

JosieP

3,360
1
13
16

Thanks. Thats slightly better as you can see which argument you're in as you type, and I think it will handle errors better. – Rattle Jun 21 '13 at 11:19
2

Also it prevents you from having to deal in ugly ways with quotation marks in strings. – Michiel van der Blonk Sep 13 '14 at 12:36
There's also a character limit on formulas that gets frustrating with this approach... I end up creating a set of columns that are all the text... and then concatenating all the text cells with data cells to get around this issue. – Mir Sep 26 '14 at 19:04
this is the most elegant solution imo without requiring VBA code/macros – Moritz Oct 20 '21 at 13:36

score 15 · Answer 3 · edited Jul 22 '20 at 12:09

15

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

edited Jul 22 '20 at 12:09

psubsee2003

8,563
8
61
79

answered Aug 12 '15 at 18:20

René Houkema

309
2
4

1

Yes, looks like original printf indeed. But what Marius has mentioned in the original answer is also true here: It is unsafe for instances where the **tokens** array contains another placeholder - for example `"test %s"`. In the comments above I made a suggestion how to remediate this issue. – Matt Feb 26 '18 at 12:16

score 13 · Answer 4 · edited Sep 22 '21 at 12:36

13

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.

edited Sep 22 '21 at 12:36

Peter Mortensen

30,738
21
105
131

answered Apr 29 '14 at 14:37

Paresh Rathod

737
7
8

4

Note that this method only works with one token, i.e., you can't do `"I bought "#" Apples and "#" Oranges."`. I was hoping for something like `String.Format("I bought {0} apples and {1} oranges.", , )` but this method can't do that. (I'm trying to avoid VBA) – jrh Apr 08 '17 at 13:57

Greedo · Answer 5 · 2021-09-22T09:06:18.153

3

"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}

edited Sep 22 '21 at 09:06

answered Jun 06 '21 at 11:58

Greedo

4,967
2
30
78

2

Maybe you should make an initial replacement before the loop to replace ```"\}``` with something else and then restore before returning the result just to avoid cases where the mask already contains those characters in that particular order. An example would be a mask like ```"{0\}{1}"``` which should return the ```{0\}``` part as is instead of ```{0}``` as your method does. +1 – Cristian Buse Sep 22 '21 at 08:13
@CristianBuse yeah I was thinking about exactly that earlier except, what if the thing I replace `\}` with is itself in the mask, then when you restore it you get two `\}` back. Could replace with something really long like a GUID to avoid clashes, or closely monitor what happens to Len() and insert characters into the string at particular locations, but really that becomes a better job for regex or just use [Matt's version](https://stackoverflow.com/a/14534361/6609896) so I kept it simple, this suffices for my purposes. But good catch, **a mask containing `\}` is indeed a buggy edge case** – Greedo Sep 22 '21 at 09:02
Funny that you mentioned it because I was thinking of the same thing. What if the "temp" replacement is actually part of the mask, then you add another "temp" which can be part of the tokens which can then be part of the mask. It never ends really. I was also thinking to traverse/parse the whole mask and then "replace" at particular locations via concatenation (no call to ```Replace```). Thanks for the link, I will have a look although regex is very slow in VBA even for simple patterns (could be thousands of times slower). – Cristian Buse Sep 22 '21 at 09:42

score 2 · Answer 6 · edited Sep 22 '21 at 12:37

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..).

Greedo · Answer 7 · 2022-01-10T23:19:31.207

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.

Obtaining the equivalent to printf or String.Format in Excel

7 Answers7

Linked