Excel: Amalgamating data to one column.

  John B 14:55 05 Jan 07

I have a sheet with 100 rows and several columns of data. Column A contains names. Column B has data in some of the cells, column C also has data but not in the same rows as the data in column B. Column D also has data in some of the cells (but not in the rows where column B and C have data.

The data consists of a numbers and letters, e.g. 25Q9/AB and are always in this format.

I would like to amalgamate the data from columns B C D into column E

Is there a way to achieve this please? I have tried using filters and copy/paste to column E but no joy. (Excel 2003).

Thanks in anticipation


  VoG II 14:58 05 Jan 07

In E1 enter


and copy the formula down.

  John B 15:02 05 Jan 07

What a rapid response! Problem solved. I've not used & in a formula before.

Thanks again


  daba 19:42 06 Jan 07

from what you have said, VoG's solution only works and gives results because the "data" you refer to will only exist in column B, or C, or D, in any given row.

in other worksheets, you may wish to "amalgamate" (the proper word is "concatenate") your data from multiple columns using a formula like VoGs, but the data in the cells will be just concatenated "as is".

For example, if you had :-

A1="Albert Squire"




then =B1&C1&D1 would give "27/01/1963PISCESVegan"

you could embellish your output by concatenating other text and spaces:-

try =A1&", born on "&B1&", is birthsign "&C1&". He is also a "&D1&"."

if I typed it right, it should give:-

"Albert Squire, born on 27/01/1963, is birthsign PISCES. He is also a Vegan."

You can concatenate cell values, any text in quotes, other formulas, etc., for example you could calculate Alberts age instead of his bithrdate and put that in the resulting cell.


  GroupFC 19:51 06 Jan 07

Book marked (for that very helpful info. from daba - thank you)!

  VoG II 19:54 06 Jan 07

"from what you have said, VoG's solution only works and gives results because the "data" you refer to will only exist in column B, or C, or D, in any given row."

Exactly - that's why such a simple solution was possible. Using & (or CONCATENATE) will just insert a NUL where the cell is empty.

  daba 20:14 06 Jan 07

I wasn't criticising your helpful solution, I wouldn't dream of it :lol: In fact I'd already seen that John B was delighted with the results.

I was simply giving John B, and anyone else who reads the thread, an insight into what is possible.

John B had indicated he had not used, or thought of using & before. I'm sure that with the extra info it will widen his horizons and allow him to explore Excel's capabilities further.

No offense intended....


  VoG II 20:18 06 Jan 07

Sorry, daba, I didn't think that you were criticising - I was just trying to explain why the simple & solution worked.

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Animator Emanuele Kabu’s psychedelic video is a stunning tribute to Lisbon city

Best Mac antivirus 2017