# Combining multiple cells into one

#### Kobi Merrikin

##### New Member
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### 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:
Code:
``="!" & TEXTJOIN("; !",TRUE,A1:A4) & ";"``

#### kpfoote

##### New Member
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.

1,101,865
Messages
5,483,390
Members
407,395
Latest member
Sakshine

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...