Have you ever wished that you could smash two cells together in Excel? Ever need to add a prefix or another bit of data to a cell? MS Excel has a function designed just for that purpose, and its called Concatenate. Its a funny name, but this little function can save you a ton of time and headache with a few simple clicks.
For example, let’s say you have the first 5 digits of a zip code in column A, followed by the last 4 digits in column B. To join them together:
- Insert a column where you need the merge to be
- Click in the cell where the first instance of your desired merge will go
- Create a formula by typing in the = sign, then click in the 5 digits zip cell (which adds the cell location to your formula)
- Enter the + sign, then click in the cell that contains the last 4 digits
- To add a – or any other symbol between your cells just add &”-“& between the 2 cell locations.
- At this point, your formula should look something like this: =A1&”-“&B1
- If you have rows zip codes to create, you can use the same formula in each merged cell by using the hovering your mouse on the bottom right hand corner of your merged zip code cell until you see the little + sign. Simple drag it down to auto-fill the entire column with your merged zip formula.
- Lastly, highlight the entire merged zip code column and copy, then paste special , values. That will keep your merged zip code permanent, even if you move the column or delete any of the columns that contributed to it.
I use this concept weekly to add a bates prefix to a column that just contains bates numbers. For this situation I create a column with my bates prefix by typing it in one cell and dragging it to auto-fill the entire column. I then create a formulate that merges the letter prefix and the bates number into one cell.
