If, in Excel, you’ve ever wanted to be able to concatenate a bunch of cells or ranges into a cell by just selecting them all at once, this Nutility is for you. It also allows you to optionally concatenate a bit of text between each cell as well.

If in cell D1 you want this formula to be entered =A1&A2&A3&C1&C2 you would select all the cells for those ranges by first highlighting A1 to A3, then holding CTRL and highlight C1 to C2. Now that all the ranges you want in the formula are selected (you can use more than just 2), you hit CTRL + Shift + Alt + N and you will be met with a popup asking for three things: the range(s) to include; an optional text to put between each cell; and the address of the cell where you want the formula entered. The first item will be filled in from what you had selected when you ran the utility (TIP: try double clicking any of the white input boxes to capture the current sheet selection). The second item is optional — if you wanted your formula to look like this =A1&”_”&A2&”_”&A3&”_”&C1&”_”&C2 you would put _ in that box, otherwise leave it blank. The third item is where you want the formula placed; in this case you would put d1. If there is an error, either an error message will display, or nothing will happen.

Leave a Reply

Your email address will not be published. Required fields are marked *