Haddii aad ku shaqaynayso xog ku jirta Excel oo aad ilo kale ka soo dejisay, mararka qaarkood waa in aad ku shaqeysaa xog aan ku jirin qaab aad rabto. Tani waxay si gaar ah run u tahay qoraalka kooma- xaddidan ee soo gala unugya keliya.

Sida kaliya ee xogtaas lagula tacaali karo waa in la kala qaybiyo unug ku jira Excel . Waxaa jira siyaabo kala duwan oo tan loo sameeyo, iyadoo ku xiran qaabka xogta. 

Maqaalkan waxaad ku baran doontaa sida unugga loo qaybiyo, sida loo rogo tiirka oo dhan, iyo goorta aad dooranayso ikhtiyaar kasta.

U beddelo qoraalka tiirarka^{(Convert Text To Columns)}

Mid ka mid ah hababka ugu caansan ee lagu kala qaybiyo unugga gudaha Excel waa isticmaalka qoraalka ilaa tirooyinka^(Columns) . Tani waxay kuu ogolaanaysaa inaad kala qaybiso tiirka unugyada oo dhan adoo isticmaalaya xeerar kasta oo aad jeceshahay. 

Sifada sidoo kale waxaa ku jira saaxir si fudud loo isticmaali karo, waana sababta dadka intooda badan ay doorbidaan isticmaalkiisa. Waxa kale oo ay gacanta ku haysaa qaab kasta oo qoraal ah, hadday qoraalka kala saarayaa yahay meel bannaan, tab ama comma.

Aan eegno tusaale ku saabsan sida loo isticmaalo sifada qoraalka ilaa tiirarka^{(Text to Columns feature)} ee Excel. 

Tusaalahan, waxaan rabnaa inaan u kala qaadno tiirka Magaca laba unug, magaca koowaad iyo magaca dambe ee iibiyaha.

Si tan loo sameeyo:

1. Dooro liiska xogta^(Data) . Ka dib dooro Text to Columns ee kooxda Qalabka Xogta^{(Data Tools)} ee ribbon.

2. Tani waxay furi doontaa saaxir saddex-tallaabo ah. Daaqadda kowaad, hubi in la xaddiday^(Delimited) la doortay oo dooro Next . 

3. Daaqadda Wizard ee soo socota, ka saar ^(Wizard)tab oo hubi in booska^(Space) la doortay. Dooro Xiga^(Next) si aad u sii wadato.

4. Daaqadda soo socota, dooro goobta la aadayo^{(Destination)} . Kadib, xaashida faafinta, dooro unugga meesha aad rabto in magaca koowaad aado. Tani waxay cusboonaysiin doontaa unugga goobta Jihaynta^{(Destination)} ilaa meesha aad dooratay.

5. Hadda, dooro dhame^(Finish) si aad u dhamaystirto Wizard.

Waxa aad arkaysaa in unuggii ka koobnaa magaca hore iyo magaca dambeba loo kala qaybiyey laba unug oo mid walba goonidiisa u kooban yahay. 

Fiiro gaar^(Note) ah: Habka kor ku xusan wuxuu shaqeeyaa sababtoo ah xogta lagu kala qaybiyay unugga waxay lahayd meel bannaan oo kala saaraysa qoraalka. Qaabkan qoraalka-ilaa-qoraalka ah waxa kale oo uu xamili karaa kala qaybinta unugga Excel haddii qoraalka lagu kala saaro tab, semicolon, comma, ama dabeecad kasta oo kale oo aad sheegtay.

Isticmaal Hawlaha Qoraalka ee Excel^{(Use Excel Text Functions)}

Hab kale oo lagu kala qaybiyo unugga Excel waa iyadoo la isticmaalayo hawlo qoraal oo^{(text functions)} kala duwan . Hawlaha qoraalku waxay kuu ogolaanayaan inaad soo saarto qaybo ka mid ah unug aad u soo saari karto unug kale.

Hawlaha qoraalka ee Excel waxaa ka mid ah:

• Bidix^(Left) (): Ka soo saar tiro xarfo dhanka bidix ee qoraalka
• Midig^(Right) (): Ka soo saar tiro xarfo dhanka midig ee qoraalka
• Mid (): Ka soo saar tiro xarfo oo ka mid ah xargaha dhexe
• Soo hel^(Find) (): Ka hel^(Find) xardh-hoosaad gudaha xadhig kale
• Len (): Soo celi wadarta tirada xarfaha ee xargaha qoraalka

Si loo kala qaybiyo unugyada, waxaa laga yaabaa inaadan u baahnayn inaad isticmaasho dhammaan hawlahan. Si kastaba ha ahaatee, waxaa jira siyaabo badan oo aad u isticmaali karto kuwan si aad u fuliso wax la mid ah.

Tusaale ahaan, waxaad isticmaali kartaa shaqada Bidix^(Left) iyo Raadi^(Find) si aad u soo saarto magaca koowaad. Shaqada Raadinta^(Find) ayaa caawisa sababtoo ah waxay kuu sheegi kartaa halka uu ku yaal dabeecadda xaddidaysa. Xaaladdan oo kale, waa meel bannaan.

Markaa shaqadu waxay u ekaan doontaa sidan:

=LEFT(C3,FIND(” “,C3))

Markaad riixdo geli ka dib markaad ku dhejiso shaqadan, waxaad arki doontaa in magaca koowaad laga soo saaray xadhigga unugga C3.

Tani way shaqeysaa, sababtoo ah shaqada bidix^(Left) waxay u baahan tahay tirada xarfaha si loo soo saaro. Maadaama meeraha boosku uu ku yaal dhamaadka magaca koowaad, waxaad isticmaali kartaa shaqada FIND si aad u heshid booska, kaas oo soo celinaya tirada xarfaha aad u baahan tahay si aad u hesho magaca koowaad.

Waxaad soo saari kartaa magaca dambe adiga oo isticmaalaya midkood function Right ama Mid function.

Si aad u isticmaasho shaqada saxda ah:

=RIGHT(C3,LEN(C3)-FIND(” “,C3))

Tani waxay soo saari doontaa magaca dambe adoo helaya booska booska, ka dibna ka jaraya dhererka guud ee xadhigga. Tani waxay siinaysaa shaqada saxda ah tirada xarfaha ay u baahan tahay si ay u soo saarto magaca dambe.

Farsamo ahaan, waxaad samayn kartaa wax la mid ah kan saxda^(Right) ah adoo isticmaalaya Mid function, sida tan:

=MID(C3,FIND(” “,C3),LEN(C3)-FIND(” “,C3))

Xaaladdan oo kale function Find wuxuu siinayaa shaqada Midka^(Mid) barta bilowga, Len oo lagu daray Find wuxuu bixiyaa tirada xarfaha la soo saarayo. Tani waxay sidoo kale soo celin doontaa magaca dambe.

Isticmaalka hawlaha qoraalka ee Excel si loo kala qaybiyo unug ku jira Excel waxay u shaqeysaa sidoo kale xallinta Text-To-Column , laakiin sidoo kale waxay kuu ogolaaneysaa inaad buuxiso dhammaan tiirka hoose ee natiijooyinkaas adoo isticmaalaya isla hawlo isku mid ah.

U kala qaybi unugga Excel Adigoo isticmaalaya Flash Fill^{(Split Cell in Excel Using Flash Fill)}

Xulashada ugu dambeysa ee lagu kala qaybin karo unugga Excel waa adeegsiga qaabka Fill Flash^{(Flash Fill feature)} . Tani waxay u baahan tahay in unugyada aad u kala qaybinayso midka asalka ah ay dhinaciisa saxan yihiin.

Haddi ay arintu sidaas tahay, waxa kaliya ee ay tahay inaad sameyso waa inaad ku qortaa qaybta unugga asalka ah ee aad rabto inaad kala baxdo. Kadibna u jiid geeska midig ee hoose ee unugga si aad u buuxiso unugga hoostiisa. Markaad tan sameyso, waxaad arki doontaa summada buuxinta unugga yar oo soo baxaysa oo ay ku dheggan tahay calaamad yar oo lagu daray.

Dooro astaantan oo waxaad arki doontaa menu soo booda. Ka dooro Fill Flash^{(Flash Fill)} liiskan.

Markaad tan sameyso, waxaad arki doontaa in qaabka Flash Fill uu si toos ah u ogaanayo sababta aad u qortay waxaad qortay, oo ay ku celin doonto habka unugga xiga. Waxay tan ku samayn doontaa iyada oo ogaanaysa oo buuxinaysa magaca koowaad ee unugga asalka ah ee bidixda.

Waxaad dhab ahaantii samayn kartaa habkan oo kale markaad buuxiso tiirka oo dhan. Dooro isla astaanta oo dooro Flash Fill . Waxay ka buuxin doontaa tiirka oo dhan magaca koowaad ee saxda ah min unugyada ilaa bidix.

Waxaad markaas koobi kartaa tiirkan oo dhan oo aad ku dhejin kartaa tiir kale, ka dibna ku celceli isla habkan si aad u soo saarto magacyada dambe. Ugu dambayntii, koobiy oo dheji tiirkaas oo dhan meesha aad rabto in uu galo xaashida faafinta. Kadibna tirtir tiirarka asalka ahaa ee aad isticmaashay si aad u qabato hawsha Buuxinta Flash^{(Flash Fill)} .

Unugyada kala baxa ee Excel

Sida aad arki karto waxaa jira dhowr siyaabood oo lagu fulin karo isla shay. Sida aad u kala qaybiso unug ku jira Excel waxa ay hoos ugu dhacaysaa halka aad doonayso in natiijada ugu dambaysa ay tagto iyo waxa aad qorshaynayso in aad ku samayso. Doorasho kastaa way shaqeysaa, markaa dooro midka macnaha ugu fiican u yeelaya xaaladaada oo isticmaal.

3 Ways To Split a Cell In Excel

If you’re working with data in Excеl that yоu’ve imported from other sоurces, sometimeѕ yоu haνe to work with data that isn’t in a format that you want. This is especially true with comma-delimitеd text that comes intо sіngle cells.

The only way to deal with that data is to split a cell in Excel. There are different ways to do this, depending on the format of the data. 

In this article you’ll learn how to split a cell, how to roll that out to an entire column, and when you should choose each option.

Convert Text To Columns

One of the most common methods to split a cell in Excel is using the Text to Columns tool. This lets you split an entire column of cells using whatever rules you like. 

The feature also includes an easy-to-use wizard, which is why most people prefer using it. It also handles any text format, whether the separating text is a space, a tab, or a comma.

Let’s look at an example of how to use the Text to Columns feature in Excel. 

In this example, we want to split the Name column into two cells, the first name and the last name of the salesperson.

To do this:

1. Select the Data menu. Then select Text to Columns in the Data Tools group on the ribbon.

2. This will open a three-step wizard. In the first window, make sure Delimited is selected and select Next. 

3. On the next Wizard window, deselect Tab and make sure Space is selected. Select Next to continue.

4. On the next window, select the Destination field. Then, in the spreadsheet, select the cell where you want the first name to go. This will update the cell in the Destination field to where you’ve selected.

5. Now, select Finish to complete the Wizard.

You’ll see that the single cell that contained both the first name and last name has been split into two cells that contain each one individually. 

Note: The process above works because the data to split in the cell had a space separating the text. This text-to-column feature can also handle splitting a cell in Excel if the text is separated by a tab, semicolon, comma, or any other character you specify.

Use Excel Text Functions

Another way to split a cell in Excel is by using different text functions. Text functions let you extract pieces of a cell that you can output into another cell.

Text functions in Excel include:

• Left(): Extract a number of characters from the left side of the text
• Right(): Extract a number of characters from the right side of the text
• Mid(): Extract a number of characters from the middle of a string
• Find(): Find a substring inside of another string
• Len(): Return the total number of characters in a string of text

To split cells, you may not need to use all of these functions. However, there are multiple ways you can use these to accomplish the same thing.

For example, you can use the Left and Find function to extract the first name. The Find function helps because it can tell you where the delimiting character is. In this case, it’s a space.

So the function would look like this:

=LEFT(C3,FIND(” “,C3))

When you press enter after typing this function, you’ll see that the first name is extracted from the string in cell C3.

This works, because the Left function needs the number of characters to extract. Since the space character is positioned at the end of the first name, you can use the FIND function to find the space, which returns the number of characters you need to get the first name.

You can extract the last name either using either the Right function or the Mid function.

To use the Right function:

=RIGHT(C3,LEN(C3)-FIND(” “,C3))

This will extract the last name by finding the position of the space, then subtracting that from the length of the total string. This gives the Right function the number of characters it needs to extract the last name.

Technically, you could do the same thing as the Right function using the Mid function, like this:

=MID(C3,FIND(” “,C3),LEN(C3)-FIND(” “,C3))

In this case the Find function gives the Mid function the starting point, and the Len combined with Find provides the number of characters to extract. This will also return the last name.

Using Excel text functions to split a cell in Excel works as well as the Text-To-Column solution, but it also lets you fill the entire column beneath those results using the same functions.

Split Cell in Excel Using Flash Fill

The last option to split a cell in Excel is using the Flash Fill feature. This requires that the cells you’re splitting the original one into are right beside it.

If this is the case, all you have to do is type the part of the original cell that you want to split out. Then drag the lower right corner of the cell down to fill the cell beneath it. When you do this, you’ll see a small cell fill icon appear with a small plus sign next to it.

Select this icon and you’ll see a menu pop-up. Select Flash Fill in this menu.

When you do this, you’ll see that the Flash Fill feature automatically detects why you typed what you typed, and will repeat the process in the next cell. It’ll do this by detecting and filling in the first name in the original cell to the left.

You can actually do this same procedure when you fill the entire column. Select the same icon and select Flash Fill. It’ll fill the entire column with the correct first name from the cells to the left.

You can then copy this entire column and paste it into another column, then repeat this same process to extract the last names. Finally, copy and paste that entire column where you want it to go in the spreadsheet. Then delete the original column you used to perform the Flash Fill process.

Splitting Cells in Excel

As you can see there are a few ways to accomplish the same thing. How you split a cell in Excel boils down to where you want the final result to go and what you plan to do with it. Any options works, so choose the one that makes the most sense for your situation and use it.

Related posts

• 4 siyaabood oo Excel loogu badalo Google Sheets

• 5 siyaabood oo loogu badalo qoraalka nambarada gudaha Excel

• Ilaali Tixraaca Unugyada marka aad nuqul ka samaynayso foomamka gudaha Excel

• Sida loo tirtiro khadadka banaan ee Excel

• Microsoft Outlook ma furmi doono? 10 siyaabood oo loo hagaajiyo

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Si degdeg ah ugu qor nambarada Word iyo Excel

• Sida loo Furo Word iyo Excel si Badbaado ah

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• Sida Loo Sameeyo Shaxda Pivot Fudud ee Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• 2 siyaabood oo loo Isticmaalo Shaqada Transpose ee Excel

• Sida Loo Isticmaalo Shaqada PMT ee Excel

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo

• Sida loo isticmaalo Flash Fill ee Excel

• Sida Looga Saaro Safka Kooban Ee Excel

• Isticmaal kiiboodhka si aad u bedesho dhererka safka iyo ballaca tiirka gudaha Excel

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel