How to join multiple Excel cells together in one cell

Here's an email I received from a client the other day regarding a data problem she had:

"Hi Neil, I was wondering if you could help me with something. I have a column with over 1000 email addresses and I would like to join them in one cell, separated by a comma. Do you know how I can do this? The concatenate function seems to let me only add each cell at a time?"

Let's look at how we can solve this problem. It isn't really possible in Excel, but if we copy it into Word, we can achieve this quite easily as follows:

- Copy your rows into Word - they will appear in Word as a table (although the borders may not be visible)

- In Word, on the contextual toolbar "Table Tools", choose the "Layout" tab (see picture below) and "Convert to text" (on the far right-hand side)

- Choose to separate the text with paragraph marks (commas will not work at this stage)

You then have 1,000 rows of text separated by invisible paragraph marks. We now need to replace these with commas (and a space if required)

- Press CTRL+H (find & replace) and replace ^p with , (a comma, or a comma followed by a space if required – if you can’t find the ^ character, just copy if from this blog post)

There may be a few stray commas at the end which you'll need to delete.

This works in Word 2010 - if you have a different version of word there may be small differences. If it doesn't work for you, please comment on this post and I'll get in touch to help you out.

For more help with Excel data, check out our live e-learning course: Excel level 4 - Advanced Data

By Neil Shorney, Director, Naturally Sales Ltd.

Excel tip: combining a large number of values into one cell: http://t.co/zGryBGQurS

— Naturally Sales Ltd (@nsales_co_uk) October 15, 2015

Rounding up to the nearest 10 (or 100, or 1000)

We've looked in the past at how to round up (or down) to the nearest x decimal places, or whole number, but someone asked me the other day how to round up to the nearest £10 (or € or $). This is very easy to do, but first, a quick re-cap on the ROUNDUP (or ROUNDDOWN or ROUND) function…

The ROUND group of functions works like this: =ROUND(cell_ref, no_of_decimals), so =ROUND(A1, 2) would round the value in cell A1 to 2 decimal places, and ROUNDUP(B2, 0) would round upwards to the nearest whole number. Easy, huh?

To round to the nearest 10, we use the same format, but move into negative numbers for the second part of the formula, so =ROUND(C3, -1) would round the value in cell C3 to the nearest 10.

So there you go, a quick look at more advanced use of the ROUND group of functions.

By Neil Shorney

Did you know, we now offer live e-learning courses for all levels of Excel - visit http://www.nsales.co.uk/microsoft_excel_training for more info.