Haddii aad wax badan isticmaasho Excel , waxay u badan tahay inaad ku soo martay xaalad aad magac ku leedahay hal unug oo aad u baahan tahay inaad magaca u kala saarto unugyo kala duwan. Tani waa arrin aad ugu badan gudaha Excel waxaana laga yaabaa inaad Google ka raadiso oo aad soo dejiso 100 macros oo kala duwan oo ay qoreen dad kala duwan si ay kuugu sameeyaan.

Si kastaba ha ahaatee, qoraalkan, waxaan ku tusi doonaa sida loo habeeyo qaacido si aad adigu u sameyso oo aad dhab ahaantii u fahanto waxa socda. Haddii aad wax badan isticmaasho Excel, waxay u badan tahay inay fikrad fiican tahay inaad barato qaar ka mid ah hawlaha aadka u horumarsan si aad u sameyso waxyaabo badan oo xiiso leh xogtaada.

Haddii aadan jeclayn qaacidooyinka oo aad rabto xal degdeg ah, hoos ugu dhaadhac qoraalka ilaa tirooyinka^{(Text to Columns)} , kaas oo ku baraya sida loo isticmaalo astaanta Excel si aad u samayso wax la mid ah. Intaa waxaa dheer, qoraalka to tiirarka astaanta ayaa sidoo kale ka wanaagsan in la isticmaalo haddii aad haysato wax ka badan laba shay oo unug ah oo aad u baahan tahay inaad kala soocdo. Tusaale ahaan, haddii hal tiir uu leeyahay 6 goobood oo la isku daray, ka dib isticmaalka hababka hoose waxay noqon doontaa mid qasan oo dhib badan.

Magacyo kala duwan oo ku jira Excel

Si aan u bilowno, aan aragno sida magacyada badanaa loogu kaydiyo xaashida xaashida ee Excel . Labada dariiqo ee ugu caansan ee aan arkay waa magaca koowaad^(lastname) ee magaca dambe oo leh meel bannaan iyo magac dambe, magaca koowaad oo leh wadhso^(lastname) kala saaraya ^(firstname)labada^(firstname) . Mar kasta oo aan arko bilowga hore, badanaa waa magaca ^(firstname)koowaad ee dhexe ee ^(midinitial)dambe^(lastname) sida hoose :

Adigoo isticmaalaya qaar ka mid ah qaaciidooyinka fudud iyo isku-darka labo ka mid ah, waxaad si fudud u kala saari kartaa magaca koowaad, magaca dambe iyo bilowga dhexe unugyo kala duwan oo Excel ah . Aan ku bilowno soo saarista qaybta hore ee magaca. Xaaladeyda, waxaan isticmaaleynaa laba hawlood: bidix iyo raadinta. Macquul ahaan waa kan waxa aan u baahanahay inaan sameyno:

Ka raadi^(Search) qoraalka ku jira unugga meel bannaan ama comma, hel booska ka dibna ka soo bixi dhammaan xarfaha dhanka bidix ee booskaas.

Halkan waxaa ah qaacido fudud oo shaqada si sax ah u guta: =LEFT(NN, SEARCH(” “, NN) – 1) , halkaasoo NN uu yahay unugga magaca ku kaydsan. -1-ku wuxuu u jiraa inuu meesha ka saaro booska dheeraadka ah ama comma dhamaadka xadhigga.

Sida aad arki karto, waxaan ku bilaabeynaa shaqada bidix, taas oo qaadata laba doodood: xargaha iyo tirada xarfaha aad rabto inaad ka soo qaadato bilawga xargaha. Xaaladda kowaad, waxaan ku raadinnaa meel bannaan annagoo adeegsanayna laba-geesood oo gelinaya meel bannaan ah. Xaaladda labaad, waxaan raadineynaa kooma halkii meel bannaan. Haddaba maxaa ka soo baxay 3-da dhacdo ee aan soo sheegay?

Magaca hore waxaan ka helnay safka 3, magaca dambe wuxuu ka yimid safka 5 iyo magaca koowaad safka 7. Wayn! Markaa iyadoo ku xidhan sida xogtaada loo kaydiyo, waxaad hadda la soo baxday magaca koowaad ama magaca dambe. Haddaba qaybta xigta. Waa kan waxa aan u baahanahay inaan hadda si macquul ah u sameyno:

– Ka raadi^(Search) qoraalka ku jira unugga meel bannaan ama hakad, hel booska ka dibna ka jar booska dhererka guud ee xadhigga. Waa kan sida qaacidadadu u ekaan doonto:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

Markaa hadda waxaan isticmaalnaa shaqada saxda ah. Tani waxay sidoo kale qaadanaysaa laba doodood: xadhigga iyo tirada xarfaha aad rabto inaad soo qabato oo ka bilaabmaya dhammaadka xadhigga bidix. Markaa waxaan rabnaa dhererka xadhigga oo laga jaray booska booska ama comma. Taasi waxay ina siin doontaa wax walba midigta booska koowaad ama comma.

Way fiicantahay^(Great) , hadda waxaan haynaa qaybta labaad ee magaca! Labada kiis ee hore, si qurux badan ayaa laguu qabtay, laakiin haddii uu jiro bilawga dhexe ee magaca, waxaad arki kartaa in natiijadu ay weli ku jirto magaca dambe oo leh bilawga dhexe. Haddaba sidee ku heli karnaa magaca dambe oo aan uga takhalusi karnaa bilowga dhexe? Fudud! Kaliya^(Just) samee isla qaacido isku mid ah mar kale oo aan isticmaalnay si aan u helno qaybta labaad ee magaca.

Markaa waxa aanu samaynaynaa xaq kale oo markan waxa aanu ku dabaqaynaa qaacidada unugga bilowga iyo magaca dambe ee la isku daray. Waxay heli doontaa booska ka dib bilowga dhexe ka dibna waxay qaadan doontaa dhererka laga jaray booska tirada xarfaha ee ka baxsan dhamaadka xargaha.

Markaa halkaas ayaad haysataa! Waxaad hadda u kala qaybisay magaca koowaad iyo magaca dambe tiirar kala duwan adiga oo isticmaalaya dhawr nooc oo fudud oo Excel ah ! Sida iska cad, qof kastaa uma qaabaysan doono qoraalkiisa habkan, laakiin si fudud ayaad u habeyn kartaa si aad ugu habboonaato baahiyahaaga.

Qoraal ku socda Tiirarka

Waxa kale oo jira hab kale oo fudud oo aad ku kala saari karto qoraalka la isku daray oo aad u kala saari karto tiirar kala duwan oo Excel ah . Waa sifada loo yaqaan Text to Columns oo si fiican ayey u shaqeysaa. Waxa kale oo aad u waxtar badan haddii aad leedahay tiir leh wax ka badan laba qaybood oo xog ah.

Tusaale ahaan, hoos waxaan ku hayaa xog uu hal saf leeyahay 4 xabbo oo xog ah, safka kalena uu leeyahay 5 xog. Waxaan jeclaan lahaa inaan taas u kala qaybiyo 4 tiir iyo 5 tiir, siday u kala horreeyaan. Sida aad arki karto, isku dayga inaad isticmaasho qaacidooyinka sare waxay noqon doontaa mid aan macquul ahayn.

Excel , marka hore dooro tiirka aad rabto inaad kala saarto. Kadibna, sii soco oo dhagsii xogta^(Data) tab ka dibna dhagsii qoraalka ku socda tirooyinka^{( Text to Columns)} .

Tani waxay soo saari doontaa saaxiraha Qoraalka ilaa Tiirarka^(Columns) . Tallaabada 1, waxaad dooranaysaa in garoonku yahay mid xaddidan ama ballac go'an. Xaaladeena , waxaan dooran doonaa Xadidan^(Delimited) .

Shaashadda xigta, waxaad dooran doontaa xadeeyaha. Waxaad ka soo qaadan kartaa tab, semicolon, comma, meel bannaan ama ku qor mid caado ah gudaha.

Ugu dambeyntii, waxaad dooranaysaa qaabka xogta ee tiirka. Caadiyan, Guud^(General) waxa uu si fiican ugu shaqayn doonaa badi noocyada xogta. Haddii aad haysato shay gaar ah sida taariikhaha, markaas dooro qaabkaas.

Guji dhame^(Finish) oo daawo sida xogtada sixir ahaan loogu kala soocay tiirar. Sida aad arki karto, hal saf ayaa loo rogay shan tiir, ka kalena wuxuu noqday afar tiir. Tilmaamaha Qoraalka Tirooyinka^(Columns) waa mid aad u awood badan oo noloshaada ka dhigi kara mid aad u fudud.

Haddii ay dhibaato kaa haysato kala saarista magacyada ee aan ahayn qaabka aan kor ku hayo, faallo ku dheji xogtaada waxaanan isku dayi doonaa inaan ku caawiyo. Ku raaxayso!

How to Separate First and Last Names in Excel

If you use Excel а lot, you have probably run acrоss а situation where you have a name in a single cell and you need to separate the nаme into different cells. This is a very common issue in Excel and you can probably do a Google search and download 100 dіfferent macros written by varіous people to do it for you.

However, in this post, I’ll show you how to setup a formula so you can do it yourself and actually understand what is going on. If you use Excel a lot, it’s probably a good idea to learn some of the more advanced functions so that you can do more interesting things with your data.

If you don’t like formulas and want a quicker solution, scroll down to the Text to Columns section, which teaches you how to use an Excel feature to do the same thing. In addition, the text to columns feature is also better to use if you have more than two items in a cell you need to separate. For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated.

Separate Names in Excel

To get started, let’s see how names are usually stored in a Excel spreadsheet. The most common two ways I have seen are firstname lastname with just a space and lastname, firstname with a comma separating the two. Whenever I have seen a middle initial, it’s usually firstname midinitial lastname like below:

Using some simple formulas and combining a couple of them together, you can easily separate the first name, last name and middle initial into separate cells in Excel. Let’s start with extracting the first part of the name. In my case, we’re going to use two functions: left and search. Logically here’s what we need to do:

Search the text in the cell for a space or comma, find the position and then take out all the letters to the left of that position.

Here’s a simple formula that gets the job done correctly: =LEFT(NN, SEARCH(” “, NN) – 1), where NN is the cell that has the name stored in it. The -1 is there to remove the extra space or comma at the end of the string.

As you can see, we start out with the left function, which takes two arguments: the string and the number of characters you want to grab starting from the beginning of the string. In the first case, we search for a space by using double quotes and putting a space in-between. In the second case, we are looking for a comma instead of a space. So what is the result for the 3 scenarios I have mentioned?

We got the first name from row 3, the last name from row 5 and the first name from row 7. Great! So depending on how your data is stored, you have now extracted either the first name or the last name. Now for the next part. Here’s what we need to do logically now:

– Search the text in the cell for a space or comma, find the position and then subtract the position from total length of the string. Here’s what the formula would look like:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

So now we use the right function. This takes two arguments also: the string and the number of characters you want to grab starting from the end of the string going left. So we want the length of the string minus the position of the space or comma. That will give us everything to the right of the first space or comma.

Great, now we have the second part of the name! In the first two cases, you’re pretty much done, but if there is a middle initial in the name, you can see that the result still includes the last name with the middle initial. So how do we just get the last name and get rid of the middle initial? Easy! Just run the same formula again that we used to get the second section of the name.

So we are just doing another right and this time applying the formula on the combined middle initial and last name cell. It will find the space after the middle initial and then take the length minus the position of the space number of characters off the end of the string.

So there you have it! You have now split the first name and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have their text formatted in this way, but you can easily edit it to suit your needs.

Text to Columns

There is also another easy way you can separate combined text into separate columns in Excel. It’s a featured called Text to Columns and it works very well. It’s also much more efficient if you have a column that has more than two pieces of data.

For example, below I have some data where one row has 4 pieces of data and the other row has 5 pieces of data. I would like to split that into 4 columns and 5 columns, respectively. As you can see, trying to use the formulas above would be impractical.

In Excel, first select the column you want to separate. Then, go ahead and click on the Data tab and then click on Text to Columns.

This will bring up the Text to Columns wizard. In step 1, you choose whether the field is delimited or fixed width. In our case, we’ll choose Delimited.

On the next screen, you will choose the delimiter. You can pick from tab, semicolon, comma, space or type a custom one in.

Finally, you choose the data format for the column. Normally, General will work just fine for most types of data. If you have something specific like dates, then choose that format.

Click Finish and watch how your data is magically separated into columns. As you can see, one row turned into five columns and the other one into four columns. The Text to Columns feature is very powerful and can make your life a lot easier.

If you are having problems separating names not in the format I have above, post a comment with your data and I’ll try to help. Enjoy!

Related posts

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida loo xafido erayga sirta ah ee Excel File

• Sida Loo Sameeyo Jadwalka socodka gudaha Word iyo Excel

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Balacaha Saarka AutoFit iyo Dhismayaasha Saf ee Excel

• Sida loo sameeyo garaafka fudud ama jaantuska gudaha Excel

• Hagaha Dhammaan Kordhinta Faylka Excel & Waxa ay ula jeedaan

• Sida loo duubo Macro gudaha Excel

• Sida Looga Daro Baararka Cilada ee Excel

• Sida Loo Isticmaalo Shaqada YEARFRAC gudaha Excel

• Sida loo Furo Tusaalooyin Badan oo Excel ah

• Sida loo isticmaalo Excel AutoRecover iyo AutoBackup Features

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• Jadwalka xogtaada Excel

• Isticmaal daaqada daawashada Excel si aad ula socoto unugyada muhiimka ah ee buug-shaqeedka

• Sida Loo Sameeyo Liiska hubinta gudaha Excel

• Sida Loo Sameeyo Liiska Hoos-u-dhaca ee Excel

• Sida loo saaro, loo jebiyo, ama loo jebiyo erayga sirta ah ee Excel XLS ee la iloobay

• Sida loo barbar dhigo labada faylal ee Excel oo loo muujiyo faraqa u dhexeeya