Is there an equivalent of printf or String.Format in Excel

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)

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

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

Tags:

Excel