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?
Description. The Microsoft Excel FORMAT function takes a string expression and returns it as a formatted string. The FORMAT function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a VBA function (VBA) in Excel.
String. format returns a new String, while System. out. printf just displays the newly formatted String to System.
On the worksheet, click and drag to select the cells you want to print. Select File > Print > Print. To print only the selected area, in Print Options, select Current Selection. If the print preview shows what you want printed, select Print.
Select the column, or range where you'll be putting the values, then use CTRL+1 to bring up the Format > Cells dialog and on the Number tab select Text. Now Excel will keep your leading 0's. If you've already entered data and Excel has removed your leading 0's, you can use the TEXT function to add them back.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With