Skip to main content

Conas téacs a roinnt de réir spáis / camóg / teimpléad in Excel?

Má tá liosta sonraí colún agat agus gur mhaith leat iad a roinnt i roinnt colúin le teorantóir ar leith díreach cosúil leis na scáileáin scáileáin thíos a thaispeántar, conas is féidir leat iad a roinnt in Excel? B’fhéidir go smaoiníonn cuid d’úsáideoirí ar an bhfeidhm Téacs go Colún amháin, ach anois tabharfaidh mé isteach ní amháin feidhm Téacs do Cholúin, ach cód VBA duitse freisin.

Roinn teaghráin téacs i gcolúin iolracha de réir spáis / camóg / teimpléad de réir gné Téacs go Colúin

Tá gné Téacs go Colúin an-úsáideach chun liosta a roinnt ar ilcholúin in Excel. Tá an modh seo ag caint ar conas sonraí a roinnt de réir teorantóra sonraithe le gné Text to Column in Excel. Déan mar a leanas le do thoil:

1. Roghnaigh an liosta colún is mian leat a roinnt de réir teorann, agus cliceáil Dáta > Téacs chuig na Colúin. Féach an pictiúr:

2. Ansin a Tiontaigh Téacs go Draoi na gColún pops dialóg amach, agus seiceáil Teoranta rogha, agus cliceáil Ar Aghaidh cnaipe. Féach an pictiúr:

3. Sa bhosca dialóige Tiontaigh go Téacs go Colún - Céim 2 de 3 bhosca dialóige, seiceáil le do thoil an teorantóir a theastaíonn uait chun na sonraí a roinnt.

nótaí: Más gá duit do shreang téacs a roinnt le teorantóir speisialta, seiceáil an Eile rogha, agus ansin clóscríobh an teorantóir sa bhosca seo a leanas.

4. cliceáil Críochnaigh. Anois is féidir leat a fheiceáil go bhfuil an liosta colún atá á roghnú roinnte ina ilcholúin ag an teorantóir sonraithe.

Sliocht uimhreacha (nó téacs) ó chill / cholún amháin go colúin / sraitheanna éagsúla

Feabhsaíonn Kutools for Excel a Cealla Scoilt fóntais agus tacaíochtaí chun gach carachtar nó uimhir téacs a bhaint as cill / colún amháin ina dhá cholún / shraith.

Faigh triail saor in aisce 30-lá iomlán anois!


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

Roinn teaghráin téacs ina sraitheanna / colúin iolracha de réir spáis / camóg / teimpléad le Kutools for Excel

Ní féidir leis an modh thuas ach teaghráin téacs a roinnt ina gcolúin iolracha. Tabharfaidh an modh seo Kutools isteach le haghaidh Excel Cealla Scoilt fóntais chun teaghráin téacs a roinnt ina sraitheanna nó i gcolúin iolracha de réir teorantóra shonraithe in Excel go héasca.

Kutools le haghaidh Excel - Pacáilte le breis agus 300 uirlis riachtanach le haghaidh Excel. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Íoslódáil anois!

1. Roghnaigh an raon a roinnfidh tú teaghráin téacs (inár gcás féin roghnóimid an raon A1: A9), agus cliceáil Kutools > Téacs > Cealla Scoilt.
téacs scoilte doc 01

2. Sa bhosca dialóige oscailt Cealla Scoilt, seiceáil an Scoilt go Sraitheanna rogha nó Scoilt go Colúin roghanna mar is gá duit sa cineál an chéad chuid eile, sonraigh teorantóir sa Sonraigh deighilteoir alt, agus cliceáil ar an Ok cnaipe. Féach an pictiúr thuas:

Anois nuair a thagann bosca dialóige Cealla Scoilt eile amach, roghnaigh an chéad chill de raon ceann scríbe agus cliceáil an OK cnaipe.
roinntear téacs doc le colúin sraitheanna 10

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

Taispeántas: Roinn teaghráin téacs ina sraitheanna / colúin iolracha de réir spáis / camóg / teorannaithe


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!

Ailt Choibhneasta:

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 (29)
No ratings yet. Be the first to rate!
Kanchan
Kanchan
This comment was minimized by the moderator on the site
Hi
I have a string like a, b,c,d,,a,d,e,f,a,b,f... etc. I want to replace a value of 'd' which having a Position of 7th after delimiter. Can you suggest how to find a position after delimiter and replace exact Position value.
Reply
0
0
Sunny
Sunny     Kanchan
This comment was minimized by the moderator on the site
Hi, Kanchan, here is a code you can try:
Sub changeText()
    Dim xSplit, xStr As String
    Dim xPos As Integer
    Dim xArr As Variant
    Dim xRng, xSetRng As Range
    On Error Resume Next
    Set xRng = Application.InputBox("Select the cell you want to extract：", "Kutools for Excel", , , , , , 8)
    xSplit = Application.InputBox("Type the delimiter：", "Kutools for Excel", , , , , , 2)
    xPos = Application.InputBox("Type nth delimiter：", "Kutools for Excel", , , , , , 1)
    xStr = Application.InputBox("Type the string or character you want to replace with：", "Kutools for Excel", , , , , , 2)
    Set xSetRng = Application.InputBox("Select the cell to place result：", "Kutools for Excel", , , , , , 8)
    xArr = Split(xRng.Text, xSplit)
    xArr(xPos) = xStr
    xSetRng.Value = Join(xArr, ",")
End Sub

Hope it can help you.
Reply
Report
0
0
kanchan
kanchan
This comment was minimized by the moderator on the site
hi ,

i have flat file which having delimiter so i want to read the value of passing position having before and after delimiter. is there any way to get these vale
Reply
0
0
Sunny
Sunny     kanchan
This comment was minimized by the moderator on the site
Hi, I do not understand your problem clearly. If you want to get the values before and after a specific delimiter separately, the both of methods mentioned in this tutorial can help you, you just need to change the delimiter to your need, and follow the steps to finish. Or you can give me more details about your problem for me to get it clearly.
Reply
Report
0
0
AbdulAzeez
AbdulAzeez
This comment was minimized by the moderator on the site
Thank you This Saved my time !
Reply
0
0
Juan
Juan
This comment was minimized by the moderator on the site
thanks so much, this really made my life easier!
Reply
0
0
Sunny
Sunny
This comment was minimized by the moderator on the site
How do we split kyzwell5konfgwell3k into columns. I need only the on has to be selected as delimit and separate into two words.
Reply
0
0
kellytte
kellytte     Sunny
This comment was minimized by the moderator on the site
Hi Sunny,
The Text to Columns (Excel built-in feature) supports to split a cell to columns by a given character, and the Split Cells feature of Kutools for Excel also supports to split cells by one character.
If you need to split cells by a string of characters, you’d better apply a VBA macro.
Reply
Report
0
0
Bob
Bob
This comment was minimized by the moderator on the site
This just shows how horrible excel is...how about a SPLIT function?? Duh...
Reply
0
0
kellytte
kellytte     Bob
This comment was minimized by the moderator on the site
Hi Bob,
Kutools for Excel has released this feature – Split Cells. This feature can not only split cells by all kinds of delimiters to columns, but also split cells to rows. What’s more, it supports to split number and text from selected cells too.
Reply
Report
0
0
Emorin30
Emorin30
This comment was minimized by the moderator on the site
Ok, so here is a problem I am stuck on that I cannot seem to resolve. I am looking to break up a long string of text with the following requirements:
1) Text can only be broken up at a space (not in the middle of a word)
2) The total length for each parsed cell must be as close to 40 characters as it can get using the space rule above.

For example, this comes in a single cell: "It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief". In this example, I would expect the formula to break up this text into 4 cells:

It was the best of times, it was the
worst of times, it was the age of
wisdom, it was the age of foolishness,
it was the epoch of belief

I keep getting wrapped around the axle trying to use a combo of left,mid,right and search/find formulas, but not only is it very inelegant, but as soon as I run into a non-unique word, my find formula breaks down.
Reply
Report
0
0
Guest
Guest
This comment was minimized by the moderator on the site
Thanks. It is really helpful.
Reply
0
0
cktugatt@gmail.com
This comment was minimized by the moderator on the site
Hello, How do i separete a long sentence into columns like


12534_MK_EC0102-Kanyuambora_OUTA 172.22.118.13 255.255.255.192 172.22.118.1. I need the process of separating to columns of: Site ID 12534_MK_EC0102; Site Name Kanyuambora_OUTA;IP 172.22.118; Mask 255.255.255; Gateway 172.22.118.1. Kindly assist.
Reply
Report
0
0
kellytte
kellytte    
This comment was minimized by the moderator on the site
Hi Cosmas,
How do you want to split the lone sentences by? In your example, you can split the sentence by the delimiter “_” or “;” or “.”
Just try them as you need!
Reply
Report
0
0
TxCentrist
TxCentrist    
This comment was minimized by the moderator on the site
You can use the FIND and LEN commands to separate fields, for example :
A1: 12534_MK_EC0102-Kanyuambora_OUTA 172.22.118.13 255.255.255.192 172.22.118.1.
B1: =FIND(" ",$A$1) B2: =FIND(" ",$A$1,B1+1) B3: "Fill Down From B2"
C1: =LEFT($A$1,B1) C2: =RIGHT(LEFT($A$1,B2),B2-B1) C3: "Fill Down From C2"
Reply
0
0
kellytte
kellytte    
This comment was minimized by the moderator on the site
Hi Cosmas,
You can apply the Text to Columns feature on the Data tab in Excel Ribbon. In the Wizard, please set both delimiters of space and custom delimiters -, and it will split your long cell content to desired columns.
View Attachments (1 / 5)
Reply
Report
0
0
Suraj Gupta
Suraj Gupta
This comment was minimized by the moderator on the site
Thank you so much for posting this, it's really very helpful at times.
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations