Thursday 15 October 2015

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 , Director, Naturally Sales Ltd.

No comments:

Post a Comment