excel

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?

Solution

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