# Friday, April 24, 2020

I use Excel a lot - sometimes just to format a lot of text by applying the same formula to a bunch of strings.

The CONCAT function is great for this. It concatenates together 2 or more strings to create a new string.

The syntax is:

CONCAT(text1, [text2],…)

where each argument (text1, text2, etc., up to 253 arguments) is a string or a reference to a cell containing a strng.

So, if cell A1 contains the string: "Customer" and cell B1 contains the string "LastName" and you want to store the following string in cell C1:

Customer.LastName

You can do so with the following formula in cell C1:

=CONCAT(A1, ".", B1)

One challenge with the CONCAT function occurs when you want your new string to include double quotes ("). Because double quotes are used to delimit strings, this can cause confusion.

For example, you may want to take the inputs above and form a string like the following:

"Customer" : "LastName"

The following formula generates and error because Excel cannot tell where a string argument ends

=CONCAT(""",A1,"" : "", B1,""")

I've found 3 ways to approach this.

Use single quotes instead of double quotes. This is a compromise, but it can work sometimes, as double quotes and single quotes are considered the same in many contexts.

The following formula accomplishes this.

=CONCAT("'",A1,"' : '", B1,"'")

Use a double set of double quotes

Excel uses the special escape sequence "" to indicate double quotes within a string.

Here is an example of this:

=CONCAT("""",A1,""" : '", B1,"'")

Use CHAR(34)

Excel contains the CHAR function that returns the character associated with an ASCII value. A double quotation character has an ASCII value of 34, so you can use this instead of the character itself. It is perfectly acceptable to embed one Excel function within another.

Here is an example of this:

=CONCAT(CHAR(34),A1,CHAR(34)," : ", B1,CHAR(34))

The last 2 options are my preference. Which you choose depends on which you find more readable.