How to Format Combined Columns in Excel
Lenskies.com - You might have text, numbers, dates, and more in your merged Excel columns. As a result, leaving the cells concatenated without formatting isn't always appropriate.
Here are several suggestions on how to format joined cells to aid you. Although we will use the ampersand approach in our examples, the CONCAT formula follows the same reasoning.
How to Format Combined Columns in Excel
1. How to Put a Space Between Combined Cells
If you had a "First name" column and a "Last name" column, you would want a space between the two cells.
To do this, the formula would be: =A2&" "&B2
This formula says to add the contents of A2, then add a space, then add the contents of B2.
It doesn't have to be a space. You can put whatever you want between the speech marks, like a comma, a dash, or any other symbol or text.
2. How to Add Additional Text Within Combined Cells
If you had a "First name" column and a "Last name" column, you would want a space between the two cells.
To do this, the formula would be: =A2&" "&B2
This formula says to add the contents of A2, then add a space, then add the contents of B2.
It doesn't have to be a space. You can put whatever you want between the speech marks, like a comma, a dash, or any other symbol or text.
The combined cells don't just have to contain their original text. You can add whatever additional information you want.
Let's say cell A2 contains someone's name (e.g., Marge Simpson) and cell B2 contains their age (e.g., 36). We can build this into a sentence that reads "The character Marge Simpson is 36 years old".
To do this, the formula would be: ="The character "&A2&" is "&B2&" years old"
The additional text is wrapped in speech marks and followed by an &. You don't need to use speech marks when referencing a cell. Remember to include where the spaces should go; so "The character " with a space at the end.
3. How to Correctly Display Numbers in Combined Cells
If your original cells contain formatted numbers like dates or currency, you'll notice that the combined cell strips the formatting.
You can solve this with the TEXT function, which you can use to define the required format.
Let's say cell A2 contains someone's name (e.g., Marge Simpson) and cell B2 contains their date of birth (e.g., 01/02/1980).
To combine them, you might think to use this formula: =A2&" was born on "&B2
However, that'll output: Marge Simpson was born on 29252. That's because Excel converts the correctly formatted date of birth into a plain number.
By applying the TEXT function, you can tell Excel how you want the merged cell to be formatted. Like so: =A2&" was born on "&TEXT(B2,"dd/mm/yyyy")
That's slightly more complicated than the other formulas, so let's break it down:
- =A2 - merge cell A2.
- &" was born on " - add the text "was born on" with a space on both sides.
- &TEXT - add something with the text function.
- (B2,"dd/mm/yyyy") - merge cell B2, and apply the format of dd/mm/yyyy to the contents of that field.
You can switch out the format for whatever the number requires. For example, $#,##0.00 would show currency with a thousand separator and two decimals, # ?/? would turn a decimal into a fraction, H:MM AM/PM would show the time, and so on.
Tag; bine two columns in excel 2010, tutorial combine two columns in excel 2016, tutorial combine two columns in excel 365, tutorial combine two columns in excel 2007, tutorial combine two columns in excel using formula, tutorial combine two columns in excel, combine two columns in excel names, how to combine two columns in excel first name last name, combine text in excel, how to merge rows in excel, how to combine two columns in excel with a comma, how to combine 2 columns in excel with a space, how to merge two columns in excel without losing data, how to combine two columns in excel with a dash,
