Combining multiple cells into one

Kobi Merrikin

New Member
Joined
Oct 18, 2019
Messages
6
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!
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,620
Office Version
365
Platform
Windows
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
Joined
Oct 17, 2019
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top