Combining multiple cells into one

[Kobi Merrikin]

Hello!

This is my first post on Mr Excel
I am trying to collate a list of numbers so they have an exclamation mark at the start and a semi-colon at the end. I wish for these to all end up in one cell so I can copy and paste them into a business intelligence system that requires this format.

For example;
2001358 would become !2001358;

In a list of cells:
2001358
2001737
2001876
2002219
Would become:
!2001358; !2001737; !2001876; !2002219;

Obviously it's pretty easy to just copy and paste a few of them. I have used the concatenate function but can't work out how to get them into one cell in a line to do the copy and paste for my system.
Any help would be gratefully appreciated for a young girl who can't quite master this one!

Thanks in advance!

 

[Joe4]

Welcome to the Board!

What version of Excel are you using? If you are using Excel 2016 or newer, I think this can be done easily with the TEXTJOIN function (see: https://exceljet.net/excel-functions/excel-textjoin-function)
So the formula may look something like:

="!" & TEXTJOIN("; !",TRUE,A1:A4) & ";"

 

[kpfoote]

If you know Concatenate, you should be able to do this pretty easily. It's just a couple extra steps.

If your data is in column A, with the first being in cell A2, try the following...
First, insert a new column next to A, this will be column B. Next, in cell B2, type the following formula: =CONCATENATE("!",A2,"; "). Next, you can drag/copy that formula all the way down the sheet for the whole set of data.
Then, to get all this data in one cell, find a blank cell, then use this formula =CONCATENATE(B:B).
Finally, copy the cell above, then right click & select "Paste Special", then select "Values" and click OK.