Joining two or more cells
Joining two or more cells
Excel uses an ampersand (&) as its concatenation operator. Concatenation is simply a fancy term
that describes what happens when you join the contents of two or more cells. For example, if cell
A1 contains the text Tucson and cell A2 contains the text Arizona, the following formula then
returns TucsonArizona:
=A1&A2
Notice that the two strings are joined together without an intervening space. To add a space
between the two entries (to get Tucson Arizona), use a formula like this one:
=A1&” “&A2
Or, even better, use a comma and a space to produce Tucson, Arizona:
=A1&”, “&A2
Another option is to eliminate the quote characters and use the CHAR function, with an appropriate
argument. Note this example of using the CHAR function to represent a comma (44) and a
space
(32):
=A1&CHAR(44)&CHAR(32)&A2
If you’d like to force a line break between strings, concatenate the strings by using CHAR(10),
which inserts a line break character. Also, make sure that you apply the wrap text format to the
cell (choose Home➜Alignment➜Wrap Text). The following example joins the text in cell A1 and
the text in cell B1, with a line break in between:
=A1&CHAR(10)&B1
The following formula returns the string Stop by concatenating four characters returned by the
CHAR function:
=CHAR(83)&CHAR(116)&CHAR(111)&CHAR(112)
Excel uses an ampersand (&) as its concatenation operator. Concatenation is simply a fancy term
that describes what happens when you join the contents of two or more cells. For example, if cell
A1 contains the text Tucson and cell A2 contains the text Arizona, the following formula then
returns TucsonArizona:
=A1&A2
Notice that the two strings are joined together without an intervening space. To add a space
between the two entries (to get Tucson Arizona), use a formula like this one:
=A1&” “&A2
Or, even better, use a comma and a space to produce Tucson, Arizona:
=A1&”, “&A2
Another option is to eliminate the quote characters and use the CHAR function, with an appropriate
argument. Note this example of using the CHAR function to represent a comma (44) and a
space
(32):
=A1&CHAR(44)&CHAR(32)&A2
If you’d like to force a line break between strings, concatenate the strings by using CHAR(10),
which inserts a line break character. Also, make sure that you apply the wrap text format to the
cell (choose Home➜Alignment➜Wrap Text). The following example joins the text in cell A1 and
the text in cell B1, with a line break in between:
=A1&CHAR(10)&B1
The following formula returns the string Stop by concatenating four characters returned by the
CHAR function:
=CHAR(83)&CHAR(116)&CHAR(111)&CHAR(112)
Comments
Post a Comment