Formula to add multiple cells into one cell with semicolon added after every entry

[Troyicus]

Hello Everyone and Happy Holidays,

I am trying to write a formula that would be able to add 5 to even 100 cells into one cell. I know I could use concat but I dont want to have to type out a formula each time that could have possibly 50 or more entries.
In addition I want each entry/value that is in the cell to have a semicolon to follow it. I need to have all cells in one string so I can copy it as one entry.

I have attached a spreadsheet but here is what I am trying to do

I have 5 numbers listed in A2, A3, A4, A5, A6

01131340
01140877
01119873
01137540
01139856

I want the formula in whatever cell but I need it to generate this: 01131340;01140877,01119873,01137540;01139856

Again I want something so that if I have 50 values the formula can pickup on that and do the 50 entries or if there is 10 entries do ten. But I need them in one cell with each entry followed by the semicolon.

I heard this could be done but having a difficult time on what I should use and do.

Thanks in advance.Combining all values into one cell example.xlsx

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[Glenn Kennedy]

If you have Excel 2021 or O365 you can use:

=TEXTJOIN("; ",,FILTER(A2:A100,A2:A100<>""))

If you have Excel 2007 (still)... life is more complicated!

[Troyicus]

@Glenn Kennedy,
THANK YOU SO MUCH SIR. This is exactly what I wanted. Have a Merry Christmas or whatever holiday you may celebrate. I sincerely appreciate the help and how fast you were able to help me.

[Troyicus]

Thank you Glenn. I have Microsoft 365 Apps fort Enterprise. I have the most current version of Excel but it says "Version 2022 (Build 14931.20506 click-to-run)
I also updated my profile as you have asked.

[Glenn Kennedy]

Cheers. Post 3 should do it for you...

[Glenn Kennedy]

You're welcome.

Christmas.... although I'm a bit of an agnostic...

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.