Excel function to insert variables into string

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?

Crudler

Posted 2016-09-07T07:39:49.883

Reputation: 297

If you're copying a formula, you only have to create it once, and concatenating a bunch of cell references takes way less time than writing this question. It isn't clear what the problem is. – fixer1234 – 2016-09-07T07:54:44.300

I disagree. If I have 20 columns, and the resulting string is 200 characters, littered with apostrophes and commas (I am generating sql statements) it can take ages and can be very error prone. There are other ways to do this (I could write a C# app to build the output, or I could do a bulk sql insert and use stored procs to do the heavy lifting). But that wasn't the point of my question, I am looking for a better excel solution to concatenation – Crudler – 2016-09-07T08:00:57.883

1I'm thinking to downvote you for using Sansa Bolton instead of Sansa Stark. – jcbermu – 2016-09-07T09:29:03.200

1was hoping someone would pick that up :) a bit of controversy attracts view me hopes :P – Crudler – 2016-09-07T14:35:21.197

Answers

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)

jcbermu

Posted 2016-09-07T07:39:49.883

Reputation: 15 868

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)

Máté Juhász

Posted 2016-09-07T07:39:49.883

Reputation: 16 807