Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Kolommen samenvoegen en combineren zonder gegevens te verliezen in Excel

Als u meerdere kolommen met gegevens samenvoegt in Excel (ongeacht welke Excel-versie u gebruikt), wordt alleen de linkerkolom met gegevens bewaard en worden de gegevens van andere kolommen verwijderd als u de "Samenvoegen en centreren"commando van Home tab op de Uitlijning groep. Deze tutorial heeft het over het samenvoegen of combineren van verschillende kolommen met gegevens in één cel of kolom in Excel.


Voeg kolommen met gegevens samen in één cel zonder gegevens te verliezen op het klembord

Als u meerdere kolommen in slechts één cel wilt samenvoegen zonder gegevens in Excel te verliezen, kunt u het klembord gebruiken om het probleem eenvoudig op te lossen.

1. Schakel allereerst het klembord in door op de knop Anker te klikken  in de rechterbenedenhoek van klembord groep op de Home tabblad. Zie screenshot:

2. Selecteer de kolommen die u wilt samenvoegen en druk op Ctrl + C sleutels om ze te kopiëren.
Opmerking: u kunt ook op klikken Home > Kopiëren om ze te kopiëren.

Nu worden de kolommen gekopieerd en meteen op het klembord weergegeven.

3. Dubbelklik in een lege cel waar u de gekopieerde kolommen gaat plaatsen en klik vervolgens op het gekopieerde item in het klembord. Nu worden de gekopieerde items in de actieve cel ingevuld zoals hieronder afgebeeld.

Tot nu toe zijn alle geselecteerde kolommen samengevoegd en in de opgegeven cel geplaatst, zoals hieronder wordt getoond.

Combineer meerdere kolommen zonder verlies van gegevens en getalnotatie in Excel

Normaal gesproken wordt bij het samenvoegen van cellen met de functie Samenvoegen in Excel alle celinhoud verwijderd, behalve de eerste celinhoud. Met Kutools for Excel's Combineren (rijen en kolommen)hulpprogramma, kunt u eenvoudig meerdere cellen / rijen / kolommen batchgewijs combineren zonder gegevens te verliezen. Bovendien ondersteunt dit hulpprogramma ook de resterende originele datumnotaties en getalnotaties in de combinatieresultaten. Gratis proefperiode van 30 dagen met volledige functionaliteit!
advertentie combineren rijen kolommen blijven opmaken

Kutools for Excel - Bevat meer dan 300 handige tools voor Excel. Gratis proefperiode van 30 dagen met volledige functionaliteit, geen creditcard vereist! Snap het nu

Voeg kolommen met gegevens samen in één kolom met formule

Als u meerdere kolommen met gegevens in één kolom moet samenvoegen zonder dat er gegevens verloren gaan, kunt u formules toepassen om ermee om te gaan in Excel.

Formule 1: = A2 & B2 & C2 & ...

Voer in cel D2 de formule in = A2 & B2 & C2en sleep vervolgens de hendel voor Automatisch aanvullen naar beneden om de formule toe te passen op andere cellen in de actieve kolom.

En de kolommen met gegevens zijn samengevoegd tot één kolom. Zie screenshot:

Formule 2: = A2 & "" & B2 & "" & C2 & ...

Voer in cel D2 de formule in = A2 & "" & B2 & "" & C2en sleep vervolgens de hendel voor Automatisch aanvullen naar beneden om de formule toe te passen op andere cellen in de actieve kolom.

Opmerkingen:
(1) In beide formules zijn A2, B2 en C2 cellen in de eerste rij die u samenvoegt tot één rij en u kunt ze naar behoefte wijzigen.
(2) Om formules uit de samenvoegresultaten te verwijderen, kunt u eerst de samenvoegresultaten kopiëren, met de rechtermuisknop op het bereik van samenvoegresultaten klikken en Plakken> Waarden selecteren in het contextmenu zoals onderstaand screenshot, of Toepassen Kutools > Naar feitelijk om formules te verwijderen, maar de samenvoegresultaten met slechts één klik te behouden. Lees verder...

(3) Beide formules zullen de nummeropmaak in de samenvoegresultaten wissen. De datum "2-15-2019" wordt bijvoorbeeld samengevoegd als "43511", het percentage "5.79%" wordt samengevoegd als "0.0579" enz.


Voeg kolommen met gegevens samen zonder gegevens te verliezen door CONCATENATE

Als er meerdere cellen in elke rij staan, zal het nogal vervelend zijn om de formule = A1 & "" & B1 & "" & C1 toe te passen. Hier zal ik de CONCATENATE-functie introduceren om dit probleem op te lossen.

1. Selecteer een lege cel en voer de formule in = CONCATENATE (A2: C2 & ",")en markeer vervolgens A2: C2 & "," in de formule. (Opmerking:: In de formule, A2: C2 is het bereik in de eerste rij dat ik zal samenvoegen, en "" betekent dat de inhoud van elke cel wordt gescheiden door een komma. )

2. pers F9 -toets om het gemarkeerde gedeelte van de formule om te zetten in waarden.

3. Nu wordt het markeringsgedeelte van de formule automatisch vervangen door de celinhoud. Verwijder de accolades { als }.

4. druk de Enter toets om het samenvoegresultaat te krijgen.

Opmerking:: Deze formule wist de nummeropmaak in de samenvoegresultaten. De datum "2-15-2019" wordt bijvoorbeeld samengevoegd als "43511", het percentage "5.79%" wordt samengevoegd als "0.0579" enz.


Voeg snel kolommen met gegevens samen zonder gegevens te verliezen met Kutools voor Excel

U kunt de invoegtoepassing van derden gebruiken Kutools for Excel om snel meerdere kolommen samen te voegen.

Kutools for Excel - Bevat meer dan 300 handige tools voor Excel. Gratis proefperiode van 30 dagen met volledige functionaliteit, geen creditcard vereist! Snap het nu

1. Na het installeren Kutools for Excel, selecteer de gegevenskolommen die u wilt samenvoegen en klik op Kutools > Combineren. Zie screenshot:

2. In de Combineer kolommen of rijen dialoogvenster, specificeer de opties als volgt:
(1) Selecteer  Combineer kolommen voor Om geselecteerde cellen te combineren volgens de volgende opties;
(2) Specificeer een scheidingsteken voor de gecombineerde gegevens, hier selecteer ik het Tussenruimte keuze;
(3) Geef de cel op waarin u uw gecombineerde resultaat wilt plaatsen;
(4) Geef aan hoe u met de gecombineerde cellen wilt omgaan. U kunt de inhoud van die gecombineerde cellen behouden of verwijderen, en u kunt die gecombineerde cellen ook samenvoegen. Zie screenshot:

3. Dan klikken Ok om meerdere kolommen in één kolom te combineren zonder gegevens te verliezen. U krijgt de volgende resultaten:

Het Combineren (Kolommen of rijen) functie van Kutools for Excel ondersteunt meer combinatiescenario's in Excel. Probeer gratis!


Demo: combineer meerdere kolommen zonder gegevens te verliezen in Excel

Kutools for Excel bevat meer dan 300 handige tools voor Excel, gratis te proberen zonder beperking in 30 dagen. Download en gratis proef nu!

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Carl · 4 years ago
    JUMP not just.....another great invention spell check that changes real words to different words. Man the next generation relying on these faulty technologies is going to be lost without a parachute.
  • To post as a guest, your comment is unpublished.
    Carl · 4 years ago
    I cannot believe Microsoft makes it so difficult to use Excel. Aren't computer programs suppose to make life easier not more difficult. I mean we are talking about a simple merge of columns yet Excel makes you just through hoops to accomplish something that should take one keystroke. It is a joke, and the joke is on us, the users. I despise Microsoft.
    • To post as a guest, your comment is unpublished.
      User · 3 years ago
      I agree with you
  • To post as a guest, your comment is unpublished.
    Hesham · 4 years ago
    Use the concatenate option it's easier!
    Concatenate (A1,B1,C1)

    write conc.... and it will show up
    • To post as a guest, your comment is unpublished.
      VINOD · 3 years ago
      VERY GOOD. ITS WORKING. THANKS FOR THE SUPPORT
  • To post as a guest, your comment is unpublished.
    Maryam · 4 years ago
    It was Perfecttttt, Thanks
  • To post as a guest, your comment is unpublished.
    jim · 5 years ago
    Ineed to convert a string of numbers (06191948) in a column to read 06/19/1948 can anyone help me?
  • To post as a guest, your comment is unpublished.
    Adam Evans · 5 years ago
    Thank you!!! That worked perfectly!
  • To post as a guest, your comment is unpublished.
    Barry · 5 years ago
    I cannot get the space to appear. This is my formula =G4&" "&I4. I am using Excel 2013.
  • To post as a guest, your comment is unpublished.
    Vinay · 5 years ago
    I am trying to merge and combine 4 columns, with the data from each column being on a separate line.
    For Example, I want it to look like this when I have finished:

    Data from Column 1
    Data from Column 2
    Data from Column 3
    Data from Column 4

    How do I do that?

    I tried using the transpose function in "Paste Special," but I am still getting 4 separate cells (1 for each of the columns I'm trying to merge). How do I get the 4 columns of data merged into 1 cell, with 4 lines
  • To post as a guest, your comment is unpublished.
    Warom Hillary · 5 years ago
    Grate work......Thanks Big...
  • To post as a guest, your comment is unpublished.
    Abhi · 5 years ago
    Great work, Thank you
  • To post as a guest, your comment is unpublished.
    Kumaresan raina · 5 years ago
    thank for so much. great
  • To post as a guest, your comment is unpublished.
    jyothi babu · 5 years ago
    Thanks for information and it is very use full for me.
  • To post as a guest, your comment is unpublished.
    Rasheed · 5 years ago
    Thank you very much. I really appreciate you people worked so hard to make our job easy. Thanks again.
  • To post as a guest, your comment is unpublished.
    Ramann · 6 years ago
    Thanks a million.

    God Bless You.
  • To post as a guest, your comment is unpublished.
    Wazeem · 6 years ago
    Thank you very much :-*
  • To post as a guest, your comment is unpublished.
    ShalikRam Panth · 6 years ago
    Thank you very much! This was very helpful.
  • To post as a guest, your comment is unpublished.
    Praveen kumar Samikk · 6 years ago
    Thank you so much. Worked like magic!
    :-)
  • To post as a guest, your comment is unpublished.
    Sagar Soni · 6 years ago
    Thanks a lot, Sir! This is very helpful.
  • To post as a guest, your comment is unpublished.
    carol · 6 years ago
    thanks you so much this really helped me .
  • To post as a guest, your comment is unpublished.
    Momen · 6 years ago
    Thamk you very much , helped me a lot .
  • To post as a guest, your comment is unpublished.
    Cathy · 6 years ago
    What is the correct formula to concatenate data from columns C,D, E, and F? I would like a line break between each column's data. Is the correct formula for this:
    =CONCATENATE(",C2,",D2,"CHAR (10),E2,"CHAR (10),F2)?

    If not, what is the correct formula?

    PLEASE HELP!
  • To post as a guest, your comment is unpublished.
    Cathy · 6 years ago
    I have still not received an answer to my question. I think what I want to do is concatenate columns c, D, E, and F, with a line break between the data from each cell. What is the correct formula to do this? Is it:
    =CONCATENATE(",C2,",D2,"CHAR (10),E2,"CHAR (10),F2)?
    If not, what is the correct formula?
    PLEASE HELP!
  • To post as a guest, your comment is unpublished.
    jj · 6 years ago
    :-x :oops: :cry: okayyyyyyy
  • To post as a guest, your comment is unpublished.
    Cathy · 6 years ago
    To clarify my earlier request:

    I am trying to merge and combine 4 columns, with the data from each column being on a separate line.
    For Example, I want it to look like this when I have finished:

    Data from Cell C2 (new line)
    Data from Cell D2 (new line)
    Data from Cell E2 (new line)
    Data from Cell F2

    What do insert between the fields (=C2&" "&D2&" "&E2&" "&F2)to get the new line?

    I do not have Kutools.
  • To post as a guest, your comment is unpublished.
    Cathy · 6 years ago
    Thank you. I haven't had a chance to try this yet. I had surgery recently, and have had other things going on.
  • To post as a guest, your comment is unpublished.
    appugee · 6 years ago
    thanks a lot .the tip helped me very much
  • To post as a guest, your comment is unpublished.
    Thomas L · 6 years ago
    Thank you, that worked like a charm. Also it is possible to add information between or separate with comma.
  • To post as a guest, your comment is unpublished.
    Cathy · 7 years ago
    I am trying to merge and combine 4 columns, with the data from each column being on a separate line.
    For Example, I want it to look like this when I have finished:

    Data from Column 1
    Data from Column 2
    Data from Column 3
    Data from Column 4

    How do I do that?
    • To post as a guest, your comment is unpublished.
      Thomas L · 6 years ago
      You need to use the transpose function in "Insert special".
      • To post as a guest, your comment is unpublished.
        Cathy · 6 years ago
        Thomas L
        I tried using the transpose function in "Paste Special," but I am still getting 4 separate cells (1 for each of the columns I'm trying to merge). How do I get the 4 columns of data merged into 1 cell, with 4 lines
  • To post as a guest, your comment is unpublished.
    Graham · 7 years ago
    This was so useful I nearly fell off my chair at how simplistic I found the procedure. Many thanks!
  • To post as a guest, your comment is unpublished.
    Andrew · 7 years ago
    Awesome. But when I merge the two cells there is no space in between the text.
    • To post as a guest, your comment is unpublished.
      Asadullah · 7 years ago
      [quote name="Andrew"]Awesome. But when I merge the two cells there is no space in between the text.[/quote]
      Just try:

      = A2 & " " & B2

      Under the quotes is what you wanna get between them! :)
  • To post as a guest, your comment is unpublished.
    Vlada · 7 years ago
    Thank you very much! You saved me so much time.
  • To post as a guest, your comment is unpublished.
    Sara · 7 years ago
    Thank you so much. This saved me!!
  • To post as a guest, your comment is unpublished.
    Marcy · 7 years ago
    Hello, please disregard my question. I figured out the answer :-)

    Marcy
  • To post as a guest, your comment is unpublished.
    Marcy · 7 years ago
    Thank you very much!

    If I am merging two columns that have LNAME and FNAME, is there a way to add a comma between so as to have LNAME, FNAME?

    Thanks again!

    Marcy :D
  • To post as a guest, your comment is unpublished.
    sanjay das · 7 years ago
    it s helpfull... thanks
    :-)
  • To post as a guest, your comment is unpublished.
    guest · 7 years ago
    how do you merge two columns in sort/merge sense; eliminating repeats?
  • To post as a guest, your comment is unpublished.
    jen · 7 years ago
    I would think you could format your cells to text or use ' before each 0.

    I tried it with the columns formatted to text then use the =A1&B1&C1 worked just fine--did not drop the 0s
  • To post as a guest, your comment is unpublished.
    alcorp · 7 years ago
    How would I merge with leading zeros and keep the zeros?
    Such as 2007 + 001 + 024 to get 2007001024 but when use the formula I get 2007124. Thanks. (The columns merging are number fields)
    • To post as a guest, your comment is unpublished.
      jen · 7 years ago
      format columns to text
  • To post as a guest, your comment is unpublished.
    Vishal · 7 years ago
    Really, jabardust tool kutools is.. thnks god kutools is here. (y)
  • To post as a guest, your comment is unpublished.
    jonas · 7 years ago
    I have a follow up question: some of my cells are empty (in some rows, not in all). Using the above system, my final cell may look something like: "text, , ,text, text, text, ,). This is logic, as it copied the empty space of the empty cells. is there a way that excel only copies the text IF there is text, but leaves it blank otherwise?

    Much appreciate your help!!!
    • To post as a guest, your comment is unpublished.
      Max · 5 years ago
      Thanks for the formula.

      I have the same follow-up question as JONAS.

      Anyone out there that can help?
  • To post as a guest, your comment is unpublished.
    pili · 7 years ago
    Thak you very much! This was very helful
  • To post as a guest, your comment is unpublished.
    pappu · 7 years ago
    thanks a lot,didnt know this for years
  • To post as a guest, your comment is unpublished.
    Carl · 7 years ago
    How about Microsoft adds this is a feature instead of adding stupidity and just changing their programmers rather than making them more intuitive. Isn't the purpose of a computer program to do the work FOR you? Having to jump thru hoops to do what a program should do for users seems a bit pathetic.
  • To post as a guest, your comment is unpublished.
    Nitin · 7 years ago
    Thanks a lot...its very usefull
  • To post as a guest, your comment is unpublished.
    shashi · 7 years ago
    thanks a lot... :-) & i want 1 more thing if i do 3 cell merge & after that i have to select from that merge cell selected column or row through CTRL+Space or Shift+space on that time in merge case i m unable to select merge column or row. i can do it mouse but i required shortcut key for that.. ????
  • To post as a guest, your comment is unpublished.
    Niamatullah Faizi · 7 years ago
    Thanks a lot, very fantastic
  • To post as a guest, your comment is unpublished.
    Subhasis Dutta · 7 years ago
    Thanks for tipses, It is very helpful.
  • To post as a guest, your comment is unpublished.
    Ray · 7 years ago
    Fantastic bit of info. One question. One of my cells that is being combined has a date in it. How do you you transfer that as a date format into the combined cell? It just comes up as a number.

    Many thanks.
  • To post as a guest, your comment is unpublished.
    qrcca · 7 years ago
    Working in excel. I have columns a-bk and rows 1-133.
    I need to make another worksheet that has all the same info in each cell but have it in a single column, I am at a loss as how this might be done without cutting and pasting each column. How do i combine multiple columns into one column?
    (I should end up with one column and 4921 rows )
  • To post as a guest, your comment is unpublished.
    Markvdb · 7 years ago
    Thanks for the help!