One of the things that Excel is missing is a way of joining a range of cell values with a common delimiter such as a comma. Here is an example of a range of cells that would be nice to be able to join together with a comma:
In order to join all of the values in the range A1 to A7 without a UDF (user-defined function) you can do the following:
=$A$1into cell B1.
=CONCATENATE($B1," ",$A2)into cell B2.
- Re-select cell B2 and then use the CTRL + C key combination to copy it.
- Select cells B3 to B7 and then use CTRL + V key combination to paste the formula into those cells.
|3||a||This is a|
|4||strange||This is a strange|
|5||and||This is a strange and|
|6||weird||This is a strange and weird|
|7||sentence.||This is a strange and weird sentence.|
Voilà! Now your desired concatenation of cells A1 to A5 can be seen in cell B5. It is important to note that this could have been done with much more than just five cells as well. If it bothers you having cells B1 to B4 filled with progressive listings of the values in column A, you can do the following:
- Select and copy cell B5.
- Select the cell into which you would like to paste the value.
- Go to the menu and select Edit and then Paste Special….
- In the dialog box, choose the paste Values option and then click OK.
- Clear the cells that you no longer want to be filled.