How often have you found that you are working on a data file in preparation for importing into The Raiser’s Edge and realize that there is data in two separate columns that needs to be combined into the same column? It can be costly to modify the source system to merge the fields and maybe the person who used to do it isn’t with the organization anymore. Working directly with clients each day, I can attest that this is a common scenario!
You used to have only two choices:
- Manually put the two columns together by typing the value into the column with the value it should be paired with, by hand.
- Act like you didn’t see it and ask for forgiveness later!
Well, worry no more and leave both options in the dust by using this Excel tip: You can merge columns within your Excel file prior to importing using a function called “Concatenate.”
Let’s walk through how you can use the “Concatenate” function in Excel to perform this task.
- Open the Excel file that has fields that need to be merged.
- Create a new column where the merged value will live. Place your cursor in the row where the data begins in the empty column (example: If your data begins in row 2 because your header is in row one, you will place the cursor in row 2 in the new column).
- Select “Formulas” from the menu bar.
- Select “Function Library”
- Select “Text”
- Select “Concatenate”
- The “Function Arguments” screen will appear for Concatenate
- In “Text1” use the button at the end of the field to navigate to the spreadsheet and then click on the cell that contains the first field you would like to merge. The cell number (example A2) will appear in a smaller elongated window once selected. Click the white “x” in the upper right hand corner of this smaller window to go back to the “Function Arguments” screen.
- In the “Function Arguments” screen, place your cursor in the “Text2” field. This time when you select the button at the end of the field to navigate to the spreadsheet, you will select the next field that you want to merge at the end of the first field. The cell number (example B2) will appear in a smaller elongated window once selected. Click the white “x” in the upper right hand corner of this smaller window to go back to the “Function Arguments” screen. *Note: If you need a space between the merged values, place your cursor in the “Text3” field instead of the “Text2” field to allow the “Text2” field to be the space.
- When you return to the “Function Arguments” screen you can select “OK”. This will close the “Function Arguments” window and return you to the spreadsheet. You should then see the merged values in the new column in the cell that your cursor was in from step 1. (example: A2 + B2 = Data in column C2) The actual formula will show as =Concatenate(A2,B2) if no space is used between the values)
- To use the formula throughout the new column for all records. Hover your mouse over the lower right side of the cell that is populated with the merged value until a solid black box appears that will let you drag the value down the column.
- You will then want to highlight the entire final merge column and copy it. Then right click and select “Paste Special” and choose “Values” then re-paste within the same column to prevent the formula from shifting.
Hopefully you will be delighted with the results and the ease of utilizing this time-saving tip!