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