Skip to main content

Conas raon / cealla a chomhchuibhiú le camóg, spás, briseadh líne in Excel?

Tá an t-alt seo ag caint ar shraith / cholún a chur le chéile i gcill amháin agus iad a scaradh le camóg nó le spás, chomh maith le raon a chur i gcomórtas le chéile i gciseal / colún / cill le deighilteoirí de chineál ar bith a scaradh, mar shampla camóg, spás, briseadh líne, srl. in Excel.


Concatenate a row with comma/space by formula

For example you have a row of names as below screenshot shown, and you need to concatenate this row into one cell and separate cell contents with comma or space. You can apply the CONCATENATE function as follows:
doc concatenate range with comma space linebreak 0

1. Select a blank cell you will place the concatenation result in, and enter the formula =CONCATENATE(B1:J1&",") isteach ann.

2. Aibhsigh an B1:J1&"," in the formula and press F9 key to replace cell references with cell contents.

3. Remove the curly braces { agus } ón bhfoirmle, agus brúigh an Iontráil eochair. Féach an pictiúr:
doc concatenate range with comma space linebreak 1

nótaí: In above formula, B1:J1 is the row you will concatenate, and "," means you will separate each cell content by comma. For separating by space, please replace the "," with “ “ in the formula.

Comhcheanglaíonn baisc ilchealla / sraitheanna / colúin gan sonraí agus formáid dáta / uimhreach a chailleadh in Excel

De ghnáth nuair a dhéantar cealla a chumasc leis an ngné Cumaisc in Excel, bainfidh sé gach ábhar cille seachas an chéad ábhar cille. Mar sin féin, le Kutools for Excel's Comhcheangail (Sraitheanna & Colúin)fóntais, is féidir leat iliomad cealla / sraitheanna / colúin a chur le chéile gan sonraí a chailleadh. Rud eile, tacaíonn an fóntais seo le formáid dáta agus formáidí uimhreacha bunaidh atá fágtha sna torthaí teaglaim.


coinníonn sraitheanna colúin le chéile formáidiú

Concatenate a column with comma/space by formula

Supposing you have a column of names as below screenshot shown, you can also apply the CONCATENATE function to combine this column/list into one cell and separate each cell content by comma or space in Excel.

doc concatenate range with comma space linebreak 3

1. Select a blank cell you will place the concatenation result in, and enter the formula = CONCATENATE (TRANSPOSE (A2: A16) & ",") isteach ann.

2. Aibhsigh an TRANSPOSE (A2: A16) & "," san fhoirmle, agus brúigh an F9 key to replace cell reference with cell contents.

3. Remove curly braces { agus } ón bhfoirmle, agus brúigh an Iontráil key. See above screenshot:

nótaí: In above formula, A2:A16 is the column/list you will concatenate, and '" means you will separate each cell content by comma. For separating by space, please replace the '" le ““ san fhoirmle.


Concatenate a range/row/column with comma/space/line break by Kutools for Excel

For example, you have a range as below screenshot shown, you can concatenate this range into one column, one row, or one cell easily with Kutools for Excel’s Chomhcheangail fóntais.

Kutools le haghaidh Excel - Supercharge Excel le níos mó ná 300 uirlisí riachtanacha. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Get sé anois

1. Roghnaigh an raon a bheidh tú ag teacht le chéile, agus cliceáil Kutools > Cumaisc & Scoilt> Comhcheangail Sraitheanna, Colúin nó Cealla gan Sonraí a chailleadh. Féach an pictiúr:
doc combine rows columns cells

2. Sa bhosca dialóige tosaigh, déan mar a leanas:
doc concatenate range with comma space linebreak 5
(1) Sa Cealla roghnaithe a chur le chéile de réir na roghanna seo a leanas section, specify a combination option based on your need. In our case, we check the Comhcheangail Colúin rogha;
(2) Sa Sonraigh deighilteoir section, check a separator as you need. In our case, we check the Líne nua rogha;
(3) Specify an option from the Cuir na torthaí chuig liosta anuas. Inár gcás féin, roghnaímid an Cill Clé.
(4) Sa Roghanna alt le do thoil seiceáil an Scrios ábhar na gcealla comhcheangailte rogha. Féach an pictiúr:

Kutools le haghaidh Excel - Supercharge Excel le níos mó ná 300 uirlisí riachtanacha. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Get sé anois

3. Cliceáil ar an Ok cnaipe.

And then you will see selected columns are merged into one column, and cells in each row are concatenated into the first cell of this row. See screenshot:
doc concatenate range with comma space linebreak 6

Roghnaigh Comhcheangail sraitheanna rogha sa To combine selected cells according to following option roinn:
doc concatenate range with comma space linebreak 7

Select Combine into single cell option in the To combine selected cells according to following option roinn:
doc concatenate range with comma space linebreak 9


Demo: concatenate range/cells with comma, space, line break in Excel


Kutools le haghaidh Excel: Breis is 300 uirlis láimhe ar do mhéar! Cuir tús le do thriail saor in aisce 30-lá gan aon srianta gné inniu. Íosluchtaigh Anois!

Earraí gaolmhara:

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
...happens when 'Remove curly braces { and } from the formula'. I also getting feedback from Excel there's a limit of number of number of cells, I'm doing 1,289.
This comment was minimized by the moderator on the site
Hi there,

Yes, 1,289 items are too much for CONCATENATE.
Are you using Excel 2019 or a newer versions? If so, please use Excel's TEXTJOIN function, for example, =TEXTJOIN(", ",TRUE,A1:F1000). Please click here to see the details of the function: https://www.extendoffice.com/excel/functions/excel-textjoin-function.html
If not, you can use Kutools for Excel by following the third method in this page: Concatenate a range/row/column with comma/space/line break

Amanda
This comment was minimized by the moderator on the site
I get an error for this formula, Excel doesn't recognize it's a formula, throws up a pop-up to add an (') before the = sign??

1. Select a blank cell you will place the concatenation result in, and enter the formula =CONCATENATE(TRANSPOSE(A2:A16)&",") into it.

2. Highlight the TRANSPOSE(A2:A16)&"," in the formula, and press the F9 key to replace cell reference with cell contents.

3. Remove curly braces { and } from the formula, and press the Enter key.
This comment was minimized by the moderator on the site
Super, and this is very easy shortcut for concatenation.. Concate(A2:A10&","). thankyou
This comment was minimized by the moderator on the site
Is there any way of removing the last comma?
This comment was minimized by the moderator on the site
Hi there,

Is the last comma the last character of a string? For example: "a, b, c, d,". If so, you can use the formula: =IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1) from the tutorial: https://www.extendoffice.com/documents/excel/2102-excel-remove-last-comma.html
Note: Please change A1 to the actual cell reference of your case.

Amanda
This comment was minimized by the moderator on the site
This didnt help at all thanks for wasting my time and energy on this.
This comment was minimized by the moderator on the site
I tried the methods, they worked perfect for me. Did you follow the exactl steps for your data?
This comment was minimized by the moderator on the site
Exactly what I was looking for - THANK YOU!!
This comment was minimized by the moderator on the site
Thanks a lot . This post is very helpful to reduce a lot of time in our day to day tasks.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations