Concatenate Text And Numbers From Different Cells in MS Excel 2007 or 2010 with Example

Concatenate Text And Numbers From Different Cells / Combine Text And Numbers From Different Cells in MS Excel 2007 and 2010 With example.

There are so many situations when need to combine values from different cells into one / single cell. For Example when you have a table of names where the first names are in one column (B) and the last names are in another column (A). You can CONCATENATE (or combine) the contents of these two separate columns into a string of text in another column (C) using a formula such as =B1 & " " & A1. Note the blank space added betyouen the first and last name. You could also use CONCATENATE function =CONCATENATE(B1," ",A1) and get the same result in Microsoft Excel. But the ampersand (&) method is much simpler.

Concatenate Text

Now, suppose you want to combine / Join (Concatenate) some text with a numeric value from another cell, for example, "Payment is due 21-Sep-2012". Lets suppose M5 contains the payment date, you can create a formula using ="Payment is due " & M5. You will notice, however, that the payment date displays as 41173. The reason is that the formula uses the underlying value (41173) from the referenced cell rather than the formatted value you see in cell M5 (21-Sep-2012).

Concatenate Text with Numbers

Normal numeric cell formatting does not work with text strings. To format the number in your text string, you need to include the TEXT function in your formula. The TEXT function allows you to define the formatting of a number in a text string. In this example, your final formula would be ="Payment is due " & TEXT(M5,"dd-mmm-yyyy").


Understanding this opens up a whole new range of possibilities. Below are some other examples of combining text and numbers in Excel. There are many, many more.

Some of the other ways you can use this in your spreadsheets?

Concatenate Text with Number