Excel function to insert variables into string

Question

Looking for a way to achieve the following. Imagine I have 2 columns

A    |   B
---------------
NAME |   SURNAME
---------------
Ned  | Stark
Arya | Stark
Sansa| Bolton

I would like column C to contain the following: "The next to die will be NAME Surname. Cheers!"

so to achieve this I would make a formula like

=concatenate(The next to die will be,A2,' ',B2,'. Cheers!')

and then pull it down all my Rows

but this is a trivial example. sometimes I might have more than 20 columns, and the resulting string is way more complex. There must be a nicer way to do this? I am thinking like an equivilant to the c# function "Format" e.g.

=Format('The next to die will be {0} {1}. Cheers!',A2,B2)

Any suggestions?

jcbermu · Accepted Answer · 2016-09-07T13:16:10.720

1st option:

="The next to die will be "& A2 & ' '& B2 & ". Cheers!"

2nd option:

(for hardcore users)

Create your own function:

Function myString(ParamArray Vals() As Variant)
    Separator1 = "{"
    Separator2 = "}"
    finalString = ""
    initialString = Vals(0)
    found = True
    firstpos = 1
    While found = True
        pos = InStr(firstpos, initialString, Separator1)
        If pos = 0 Then
            found = False
            endpartval = Mid(initialString, firstpos)
            finalString = finalString + endpartval
        Else
            stringParts = Mid(initialString, firstpos, pos - firstpos)
            pos1 = InStr(pos, initialString, Separator2)
            firstpos = pos1 + 1
            varNumber = Mid(initialString, pos + 1, pos1 - pos - 1)
            finalString = finalString + stringParts + Vals(varNumber + 1)
        End If
    Wend
    myString = finalString
End Function

To make it work you have to open VBA/Macros with ALT+ F11, then under ThisWorkbook insert a new module and paste the code.

Now, in any cell you can put

=mystring("The next to die will be {0} {1}. Cheers!",A2,B2)

or whatever. Keep in mind that the string must go first and then the cell references.

This is valid:

=mystring("The next to die will be {0}, {3} and {2}. Cheers!",A2,B2,B3)

This isn't:

=mystring(A2,"The next to die will be {0}, {3} and {2}. Cheers!",B2,B3)

score 5 · Answer 2 · answered Sep 07 '16 at 08:06

5

There isn't such a function in Excel, you can use SUBSTITUTE, but it still will be long:

=SUBSTITUTE(SUBSTITUTE("The next to die will be {1} {2}","{1}",A2),"{2}",B2)

answered Sep 07 '16 at 08:06

Máté Juhász

22,071

score 1 · Answer 3 · answered Dec 22 '21 at 19:56

Slight improvement to the function given above. Replaced the + with & in order to handle non-string cells

Function myString(ParamArray Vals() As Variant)
    Separator1 = "{"
    Separator2 = "}"
    finalString = ""
    initialString = Vals(0)
    found = True
    firstpos = 1
    While found = True
        pos = InStr(firstpos, initialString, Separator1)
        If pos = 0 Then
            found = False
            endpartval = Mid(initialString, firstpos)
            finalString = finalString + endpartval
        Else
            stringParts = Mid(initialString, firstpos, pos - firstpos)
            pos1 = InStr(pos, initialString, Separator2)
            firstpos = pos1 + 1
            varNumber = Mid(initialString, pos + 1, pos1 - pos - 1)
            finalString = finalString & stringParts & Vals(varNumber + 1)
        End If
    Wend
    myString = finalString
End Function

Excel function to insert variables into string

3 Answers3