Skip i'r prif gynnwys

Sut i rannu testun yn ôl gofod / coma / amffinydd yn Excel?

Os oes gennych restr colofnau o ddata a'ch bod am eu rhannu'n sawl colofn gan amffinydd penodol yn union fel y sgrinluniau isod a ddangosir, sut allwch chi eu rhannu yn Excel? Efallai bod rhai defnyddwyr yn meddwl am y swyddogaeth Testun i Golofn yn unig, ond nawr byddaf yn cyflwyno nid yn unig swyddogaeth Testun i Golofnau, ond hefyd cod VBA i chi.

Rhannwch dannau testun yn sawl colofn yn ôl gofod / coma / amffinydd yn ôl nodwedd Testun i Golofnau

Mae nodwedd Testun i Golofnau yn ddefnyddiol iawn i rannu rhestr i sawl colofn yn Excel. Mae'r dull hwn yn sôn am sut i rannu data yn ôl terfynwr penodedig â nodwedd Testun i Golofn yn Excel. Gwnewch fel a ganlyn:

1. Dewiswch y rhestr golofnau rydych chi am ei rhannu â delimiter, a chlicio Dyddiad > Testun i Colofnau. Gweler y screenshot:

2. Yna a Trosi Testun yn Dewin colofnau deialog pops allan, a gwirio Wedi'i ddosbarthu opsiwn, a chlicio Digwyddiadau botwm. Gweler y screenshot:

3. Yn y blwch deialog Trosi Testun i Ddewin Colofnau - Cam 2 o 3 blwch deialog, gwiriwch y amffinydd sydd ei angen arnoch i rannu'r data.

Nodyn: Os oes angen i chi rannu llinyn eich testun â therfynydd arbennig, gwiriwch y Arall opsiwn, ac yna teipiwch y delimiter i'r blwch canlynol.

4. Cliciwch Gorffen. Nawr gallwch weld bod y rhestr colofnau wrth ddethol wedi ei rhannu'n golofnau lluosog gan y amffinydd penodedig.

Tynnwch rifau (neu destun) yn unig o un gell / colofn i mewn i wahanol golofnau / rhesi

Kutools for Excel yn gwella ei Celloedd Hollt cyfleustodau ac yn cefnogi i swp-dynnu pob nod testun neu rif o un gell / colofn yn ddwy golofn / rhes.

Gael 30-diwrnod llawn-ymddangos treial am ddim nawr!


Kutools for Excel - Supercharge Excel gyda dros 300 o offer hanfodol. Mwynhewch sioe llawn sylw 30- treial AM DDIM diwrnod heb angen cerdyn credyd! Get It Now

Rhannwch linynnau testun yn rhesi/colofnau lluosog yn ôl gofod/coma/amffinydd Kutools for Excel

Gall y dull uchod rannu llinynnau testun yn golofnau lluosog yn unig. Bydd y dull hwn yn cyflwyno Kutools for Excel's Celloedd Hollt cyfleustodau i rannu llinynnau testun yn sawl rhes neu golofn gan amffinydd penodedig yn Excel yn hawdd.

Kutools for Excel - Yn llawn dros 300 o offer hanfodol ar gyfer Excel. Mwynhewch sioe llawn sylw 30- treial AM DDIM diwrnod heb angen cerdyn credyd! Lawrlwytho nawr!

1. Dewiswch yr ystod y byddwch chi'n rhannu llinynnau testun (yn ein hachos ni, rydyn ni'n dewis yr ystod A1: A9), a chlicio Kutools > Testun > Celloedd Hollt.
testun hollt doc 01

2. Yn y blwch deialog Celloedd Hollt agoriadol, gwiriwch y Hollti i Rhesi opsiwn neu Hollti i Golofnau opsiynau fel y mae eu hangen arnoch yn y math adran nesaf, nodwch amffinydd yn yr Nodwch wahanydd adran, a chliciwch ar y Ok botwm. Gweler y screenshot uchod:

Nawr bod blwch deialog Celloedd Hollt arall yn ymddangos, dewiswch y gell gyntaf o ystod cyrchfan a chliciwch ar y OK botwm.
doc rhannu testun â rhesi colofnau 10

Kutools for Excel - Supercharge Excel gyda dros 300 o offer hanfodol. Mwynhewch sioe llawn sylw 30- treial AM DDIM diwrnod heb angen cerdyn credyd! Get It Now

Demo: Rhannwch dannau testun yn sawl rhes / colofn yn ôl gofod / coma / amffinydd


Kutools for Excel: Dros 300 o offer defnyddiol ar flaenau eich bysedd! Dechreuwch eich 30- treial am ddim diwrnod heb unrhyw gyfyngiadau nodwedd heddiw. Lawrlwytho Nawr!

Erthyglau Perthynas:

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
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