How to concatenate text in Microsoft Excel

How to concatenate text in Microsoft Excel
How to concatenate text in Microsoft Excel
Anonim

Tearing your hair out because you can't merge cells in Excel? Would you like to automatically make sentences with the data in your Excel spreadsheet? Lucky for you, there's a Concatenation feature that does it blazingly fast for you!

Steps

395700 1

Step 1. Use the CONCATENATE function to merge two cells

The role of this function is to tie together the contents of two (or more) cells. You can link up to 255 text strings in a single string. To be clear, let's take an example:

Enter the formula

TO B VS
1 Good day = CONCATENATE (A1, B1)

Results

TO B VS
1 Good day Hello
395700 2

Step 2. You can insert spaces when concatenating

To do this, simply place in the parenthesis, in the right place, a space (or more), all in quotes. This is very useful, for example, when merging first and last names. See instead:

Enter the formula

TO B VS
1 John Smith = CONCATENATE (A1, "", B1)

Results

TO B VS
1 John Smith John smith
395700 3

Step 3. You can insert punctuation or text between your starting strings

We saw that we could put a space, but we can also put a predefined text and punctuation. On this last point, don't forget that there are rules regarding spaces before or after signs!

Enter the formula

TO B VS
1 On Monday Friday = CONCATENATE (A1, "-", B1, ", closed on weekends.")

Results

TO B VS
1 On Monday Friday Monday - Friday, closed on weekends.
395700 4

Step 4. Concatenate a series of dates

Warning ! in this case, it is necessary to specify that the cells which contain the dates are texts and for that, we use the function TEXT. Otherwise, Excel will consider these dates as numeric values:

Enter the formula

TO B VS
1 01/14/2013 06/17/2013 = Concatenate (Text (A1, "DD / MM / YYYY"), "-", Text (B1, "DD / MM / YYYY"))

Results

TO B VS
1 14/01/2013 17/06/2013 14/01/2013 - 17/06/2013
395700 5

Step 5. You can use the “&” symbol instead of CONCATENATE

It's totally equivalent. This is very handy for short formulas, but maybe more difficult to manage on longer formulas. Note the space between the quotes! You must put an ampersand (“&”) between each value!

Enter the formula

TO B VS
1 John Smith = A1 & "" & B1

Results

TO B VS
1 John Smith John smith

Popular by topic