Skip to main content

Conas colúin chill a chomhchuibhiú agus dath an téacs a choinneáil in Excel?

Údar: Xiaoyang Athraithe Deiridh: 2023-02-15

Mar is eol dúinn uile, agus iad ag teacht le chéile nó ag comhcheangal colúin cille in aon cholún amháin, caillfear formáidiú na gceall (mar shampla dath cló téacs, formáidiú uimhreacha, srl). An t-alt seo, tabharfaidh mé roinnt cleasanna isteach chun na colúin chill a chomhcheangal i gceann amháin agus dath an téacs a choinneáil chomh héasca agus is féidir in Excel.

Comhchruinnigh colúin cille agus coinnigh dath cló téacs le cód VBA


Comhchruinnigh colúin cille agus coinnigh dath cló téacs le cód VBA

Chun an tasc seo a chríochnú in Excel, féadfaidh an cód VBA seo a leanas fabhar a thabhairt duit, déan é seo le do thoil:

1. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Fuinneog an Mhodúil.

Cód VBA: colúin cealla comhtháthaithe agus dath an téacs a choinneáil:

Sub MergeFormatCell()
'Updateby Extendoffice
    Dim xSRg As Range
    Dim xDRg As Range
    Dim xRgEachRow As Range
    Dim xRgEach As Range
    Dim xRgVal As String
    Dim I As Integer
    Dim xRgLen As Integer
    Dim xSRgRows As Integer
    Dim xAddress As String
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xSRg = Application.InputBox("Please select cell columns to concatenate:", "KuTools For Excel", xAddress, , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSRgRows = xSRg.Rows.Count
    Set xDRg = Application.InputBox("Please select cells to output the result:", "KuTools For Excel", , , , , , 8)
    If xDRg Is Nothing Then Exit Sub
    Set xDRg = xDRg(1)
    For I = 1 To xSRgRows
        xRgLen = 1
        With xDRg.Offset(I - 1)
            .Value = vbNullString
            .ClearFormats
			.NumberFormat = "@"
            Set xRgEachRow = xSRg(1).Offset(I - 1).Resize(1, xSRg.Columns.Count)
            For Each xRgEach In xRgEachRow
                .Value = .Value & Trim(xRgEach.Value) & " "
            Next
            For Each xRgEach In xRgEachRow
                xRgVal = xRgEach.Value
                With .Characters(xRgLen, Len(Trim(xRgVal))).Font
                .Name = xRgEach.Font.Name
                .FontStyle = xRgEach.Font.FontStyle
                .Size = xRgEach.Font.Size
                .Strikethrough = xRgEach.Font.Strikethrough
                .Superscript = xRgEach.Font.Superscript
                .Subscript = xRgEach.Font.Subscript
                .OutlineFont = xRgEach.Font.OutlineFont
                .Shadow = xRgEach.Font.Shadow
                .Underline = xRgEach.Font.Underline
                .ColorIndex = xRgEach.Font.ColorIndex
                End With
                xRgLen = xRgLen + Len(Trim(xRgVal)) + 1
            Next
        End With
    Next I
End Sub

3. Ansin brúigh F5 eochair chun an cód seo a rith, agus tá bosca pras popped amach chun a mheabhrú duit an raon sonraí a theastaíonn uait a chomhcheangal le dath an téacs a roghnú, féach an scáileán:

4. Agus ansin cliceáil OK, tá bosca dialóige eile le feiceáil, roghnaigh na cealla inar mian leat na torthaí comhcheangailte a aschur, féach an scáileán:

5. Ansin cliceáil OK cnaipe, tá na colúin curtha i dtoll a chéile gan dath an téacs a chailleadh mar a leanas an pictiúr a thaispeántar:

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
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 ...

Tuairisc


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 (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi Team,

Thanks a lot for this code! Noticed that it would not take in the text colour if the colour is obtained through conditional formatting. Is there a workaround for it?
This comment was minimized by the moderator on the site
Hello, hack,
I'm sorry, at present, there is no better way to preserve conditional formatting colors when merging cells.

If anyone else has a solution, please share it here as a comment.

Thank you!
This comment was minimized by the moderator on the site
5. Note: If the data which needed to be combine includes numbers, then macro code cannot work correctly.

However, if you insert code:
.NumberFormat = "@"
(after:
.ClearFormats
) betwen 24-25 line numbers, maybe work.
This comment was minimized by the moderator on the site
Hello, Mariusz
Thanks a lot for your comment, it works well. We have updated the code in this article.
Thanks again! 👍
This comment was minimized by the moderator on the site
You're welcome! I like 🥰 the very different and interesting tips on this site.
This comment was minimized by the moderator on the site
thanks for the code!
Can it be automatically update the concatenate column if any change make?
This comment was minimized by the moderator on the site
Hi, Kathy,
Sorry, at present, there is no good way for doing that, you need to run the code again after changing the cell content. 😂
Thank you!
This comment was minimized by the moderator on the site
Alright, thanks for your reply
This comment was minimized by the moderator on the site
Thanks for the code.
If the text has "( )",  for example "tomate (tomates)". It doesn't work as well. Is there something to modify in order to work with "( ) "?
This comment was minimized by the moderator on the site
Hello Rah,
How are you? If the text has "( )", we don't need to change the VBA code. Please see the screenshot, the text "tomato (tomatoes)" can be successfully combined with other cell columns into one column. Hope it may help you.
Sincerely,
Mandy
This comment was minimized by the moderator on the site
Habría manera de hacerlo con números?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations