Have you ever had information in multiple cells that you want to combine into one? Maybe you have a list of names where the first name is in one column and the last name is in another, or address information is split out as street address, city, state, and zip code.
In the list below, you can combine the elements of employee addresses with the formula =(B2&C2&D2&E2), but this would leave no spaces between the different element (e.g. HoltsvilleNY00501). To have a space between them, add &“ ” between each cell.
Click Enter, and you’ll get the result for the first address. You can them copy the formula down to the other rows. Select cell F2, click on the square in the lower right corner of the cell, and drag it down.
You can also go the opposite direction, and split one cell into 2 or more, for example if first and last names are in the same cell, and you want to split them into First Name and Last Name for an email marketing campaign.
Make sure there’s a blank column to the right of the column you’re splitting. Highlight the column you want to split, go to the Data tab, and select Text to Columns.
A popup window will appear that will guide you through a few steps. The first is choosing Delimited or Fixed Width. Use Fixed Width if every cell should be split after the same number of characters (e.g. after the first 5 letters in the cell). Use Delimited to split cells based on a certain character, e.g. when there’s a space between words, or a comma, or a semicolon. In this case, we’ll use Delimited and split cells when there’s a space between the first and last name.
In the next screen, choose the delimiter, which is the character at which Excel will split the cell. If you export data from certain programs, it will come into Excel with commas or tabs as delimiters. If you have a list of email addresses and want to split the username from the domain (@XYZ.com), you can choose Other and use @. In this case, we’ll use Space.
The next window will give you a preview of how the split cells will look like. You can add advanced formatting if you like. If the preview looks good, click Finish.
The cells with then be split into 2. This process will overwrite the column to the right of the original cells being split, which is why we wanted a blank column there when we started. In this case, the row with column titles now has Employee in column A and Name in column B (the name of the original column title). Renaming them First Name and Last Name will better define the columns’ use.
