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?

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
– fixer1234, Commented Sep 7, 2016 at 7:54
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
– Crudler, Commented Sep 7, 2016 at 8:00
I'm thinking to downvote you for using Sansa Bolton instead of Sansa Stark. — jcbermu
– jcbermu, Commented Sep 7, 2016 at 9:29
was hoping someone would pick that up :) a bit of controversy attracts view me hopes :P — Crudler
– Crudler, Commented Sep 7, 2016 at 14:35

jcbermu · Accepted Answer · 2016-09-07 13:16:10Z

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)

Máté Juhász · Accepted Answer · 2016-09-07 08:06:50Z

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 7, 2016 at 8:06

Máté Juhász

22.2k6 gold badges64 silver badges83 bronze badges

Add a comment |

Nick Spring · Accepted Answer · 2021-12-22 19:56:13Z

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

Stack Exchange Network

Excel function to insert variables into string

3 Answers 3

You must log in to answer this question.

Hot Network Questions

Excel function to insert variables into string

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions