Kidokezo: Lugha zingine ni Google-Ilitafsiriwa. Unaweza kutembelea English toleo la kiungo hiki.
Ingia
x
or
x
x
Daftari
x

or

Jinsi ya kugawanya jina kamili kwa jina la kwanza na la mwisho katika Excel?

Ikiwa una jina la majina kama skrini ya kwanza inaonyesha kwenye safu moja chini, na unahitaji kugawanya jina kamili kwa safu ya kwanza ya jina, safu ya jina la kati na safu ya jina la mwisho kama skrini iliyofuata inavyoonyeshwa. Hapa kuna mbinu zenye mashaka kukusaidia kutatua tatizo hili:


Split majina kamili kwa majina ya kwanza na ya mwisho kwa wachapishaji wa comma au wengine:

Ikiwa majina yako kamili yanatenganishwa na comma au watangazaji wengine katika kiini,Kutools kwa Excel' Split seli kipengele pia kinaweza kukufaidi, angalia skrini:

doc kupasua majina kamili 15

Kutools kwa Excel: na zaidi ya 200 handy Excel inserts, huru kujaribu na hakuna kikomo katika siku 60. Pakua na jaribio la bure Sasa!


Piga jina kamili kwa jina la kwanza na la mwisho na fomu


Split majina kamili kwa majina ya kwanza na ya mwisho:

Unaweza kutumia kazi ya kushoto, kazi ya kulia na kupata kazi ili kupiga jina kamili kwa jina la kwanza na jina la mwisho kwa hatua zifuatazo:

1. Ingiza formula ya = KIFUNA (A2, FIND ("", A2,1) -1) katika kiini tupu, anasema Cell C2 katika kesi hii, na kisha gurudisha kujaza chini kwenye seli ambazo unataka kujaza fomu hii, na majina yote ya kwanza yameondolewa mara moja, angalia skrini:

doc kupasua majina kamili 2

2. Kisha ingiza fomu ya = HUDUMA (A2, LEN (A2) -FIND ("", A2,1)) katika kiini kingine chochote, Kiini D2, na gurudisha kujaza chini kwenye seli ambazo unataka kutumia fomu hii, na majina yote ya mwisho yamepatikana kwenye safu ya D kama skrini ifuatayo iliyoonyeshwa:

doc kupasua majina kamili 3


Split majina kamili kwa majina ya kwanza, ya kati na ya mwisho:

Ikiwa unataka kupasua majina kamili kwa majina ya kwanza, ya kati na ya mwisho katika nguzo tofauti, fomu zifuatazo zinaweza kukufaidi.

1. Ingiza formula hii: = LEFT (A2, SEARCH ("", A2)) katika kiini tupu C2, na kisha gurudisha kujaza chini kwenye seli ili kuomba fomu hii, na majina yote ya kwanza yamegawanyika kwenye safu C, angalia skrini:

doc kupasua majina kamili 4

2. Kisha ingiza formula hii: = "MID" (A2, SEARCH ("", A2,1) + 1, SEARCH ("", A2, SEARCH ("", A2,1) + 1) -SEARCH ("", A2,1)) katika kiini tupu D2, na kisha gurudisha kujaza chini kwenye seli ili kuomba fomu hii, na majina yote ya kati yamegawanyika kwenye safu ya D, angalia skrini:

doc kupasua majina kamili 5

3. Hatimaye, ingiza fomu hii: = HUDUMA (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2, SEARCH ("", A2) + 1))) katika kioo tupu E2, na kisha gurudisha kujaza chini kwenye seli ili kuomba fomu hii, na majina yote ya kwanza yamegawanyika kwenye safu ya E, angalia skrini:

doc kupasua majina kamili 6


Piga jina kamili kwa jina la kwanza na la mwisho na amri ya Nakala ya Column

Si rahisi kukumbuka kazi. Tafadhali usijali kuhusu hilo. Njia ya pili itakuwezesha kugawanya safu kamili ya jina kwa urahisi.

1. Chagua safu ambayo utagawanywa, katika kesi hii ni A2: A13.

2. Bofya Nakala ya nguzo kifungo chini Data Tab.

doc kupasua majina kamili 7

3. Ndani ya Badilisha Nakala kwenye Mshale wa nguzo - Hatua ya 1 ya 3 sanduku la mazungumzo, angalia Ulimwenguni chaguo, na bofya next button.

doc kupasua majina kamili 8

4. Ndani ya Hatua 2 ya mchawi wa 3, angalia tu Nafasi chaguo katika Ulimwenguni sehemu, na bofya next button.

doc kupasua majina kamili 9

5. Katika mchawi ufuatayo, angalia Nakala chaguo katika Faili ya data ya safu sehemu, na taja Marudio kiini ambapo unataka kuweka matokeo. Angalia skrini:

doc kupasua majina kamili 10

6. Bofya Kumaliza kifungo. Kisha utaona safu kamili ya jina imetengwa kwa safu ya kwanza ya jina, safu ya jina la kati na safu ya jina la mara moja.


Piga jina kamili kwa jina la kwanza na la mwisho na Kutools kwa Excel haraka na kwa urahisi

Ikiwa umechoka na mbinu mbili zilizo juu, hapa, naweza kukupendekeza chombo muhimu - Kutools kwa Excel, Pamoja na wake Split Majina kipengele, unaweza haraka kupasua majina kamili kwa majina ya kwanza na ya mwisho, au kupasua majina kamili kwa majina ya kwanza, ya kati na ya mwisho kama unahitaji.

Kutools kwa Excel : na zaidi ya 200 handy Excel add-ins, bure kujaribu na hakuna kikomo katika siku 60.

Baada ya kufunga Kutools kwa Excel, tafadhali fanya ifuatavyo:

1. Chagua aina ya jina unayotaka kupasuliwa.

2. Bonyeza Kutools > Nakala > Split Majina, Katika Split Majina boja la mazungumzo, taja Aina ya kupasuliwa unataka, ikiwa unataka kugawanya jina kamili kwa jina la kwanza na la mwisho, tafadhali angalia Jina la kwanza na Jina la mwisho, angalia skrini:

doc kupasua majina kamili 11

3. Kisha bonyeza OK kifungo, na mazungumzo mengine yataonekana, tafadhali chagua kiini ambapo unataka kuweka matokeo katika mazungumzo, angalia skrini:

doc kupasua majina kamili 12

4. Na click OK kifungo, majina kamili yamegawanywa katika safu ya kwanza ya jina na safu ya jina la mwisho, angalia skrini:

doc kupasua majina kamili 13

Kumbuka: Kama jina lako kamili lina majina ya kwanza, ya kati na ya mwisho yanahitajika kugawanywa katika nguzo tatu tofauti, unahitaji tu kuangalia Jina la kwanza, Katikati jina na Jina la mwisho katika Split Majina dialog box na utapata matokeo yafuatayo:

doc kupasua majina kamili 14

Pakua na jaribio la bure Kutools kwa Excel Sasa!


Piga jina kamili kwa jina la kwanza na la mwisho na Kutools kwa Excel haraka

Kutools kwa Excel: na zaidi ya 200 handy Excel inserts, huru kujaribu na hakuna kikomo katika siku 60. Pakua na jaribio la bure Sasa!


Zilizopendekeza Vifaa vya Uzalishaji

Ribbon ya Excel (pamoja na Kutools kwa Excel imewekwa)

Features 300 + Zinazoongeza Kuongeza Uzalishaji wako na 70%, na Kukusaidia Kusimama Kutoka kwenye Umati!

Ungependa kukamilisha kazi yako ya kila siku haraka na kwa ukamilifu? Kutools Kwa Excel huleta Vipengele vya juu vya 300 + vyema na vyema (Jumuisha vitabu vya kazi, jumla kwa rangi, kupangilia maudhui ya kiini, tarehe ya kubadilisha, na kadhalika ...) kwa Matukio ya kazi ya 1500 +, husaidia kutatua Matatizo ya 80% ya Excel.

  • Ushiriki na kazi zote ngumu katika sekunde, usaidie kuimarisha uwezo wako wa kazi, kupata mafanikio kutoka kwa ushindani mkali, na usiwe na wasiwasi juu ya kufukuzwa.
  • Hifadhi muda mwingi wa kazi, kuondoka muda mwingi kwa kupenda na kutunza familia na kufurahia maisha mazuri sasa.
  • Kupunguza maelfu ya keyboard na panya ikichunguza kila siku, kupunguza macho yako na uchovu wako, na kukupa mwili mzuri.
  • Kuwa mtaalam wa Excel katika dakika ya 3, na uone mtazamo wa wenzako au marafiki.
  • Hakuna tena haja ya kukumbuka kanuni zenye maumivu na nambari za VBA, kuwa na akili ya kupumzika na yenye kupendeza, kukupa furaha ambayo haujawahi kuwa nayo kabla.
  • Tumia $ 39 tu, lakini thamani ya mafunzo ya wengine ya $ 4000. Kutumiwa na wasomi wa 110,000 na makampuni ya maalumu ya 300.
  • Siku ya 60 ya jaribio la bure bila ukomo. Dhamana ya fedha ya siku ya 60. Uboreshaji bure na usaidizi kwa miaka 2. Kununua mara moja, tumia milele.
  • Badilisha jinsi unavyofanya kazi sasa, na kukupa maisha bora mara moja!

Shot ya Screen ya Excel (iliyo na Tab ya Ofisi imewekwa)

Kitabu cha Ofisi kinaleta Tabs za Ufanisi na Handy kwa Ofisi (ikiwa ni pamoja na Excel), Kama Chrome, Firefox, na IE Mpya

  • Inaongeza uzalishaji wako kwa 50% wakati wa kutazama na kuhariri nyaraka nyingi.
  • Kupunguza vifungo vya panya kwa kila siku kwa kila siku, sema kwaheri kwa panya mkono.
  • Fungua na uunda hati katika tabo mpya za dirisha sawa, badala ya madirisha mapya.
  • Kukusaidia kufanya kazi kwa haraka na kwa urahisi kusimama kutoka kwa umati! Jambo moja la kubadili kati ya hati nyingi za wazi!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Adam Pearce · 1 years ago
    Thanks so much! Worked perfectly!
  • To post as a guest, your comment is unpublished.
    PCM · 1 years ago
    Brilliant!
  • To post as a guest, your comment is unpublished.
    kmrod · 1 years ago
    these are great, but!!! after splitting the names i need to remove the column with both names, and that makes the calculation throw an error.

    how can i split (columnB) Bob Smith into (columnC) Bob then (columnD) Smith and then delete columnB, so i'm left with just the FName and LName columns?
    • To post as a guest, your comment is unpublished.
      KB · 1 years ago
      Hi kmrod,

      From my understanding, you put your formulas into columnC and D to get Fname(columnC) and Lname(columnD). Once you do this you try delete columnB and then C and D just say errors?

      If this is the case, sounds like your formulas are still active when you delete columnB. Have you tried copying columnC and columnD then right click over columnC choose -> paste special -> Values -> OK. Then go ahead and delete columnB and that should keep C and D intact.

      Hopefully this fixes your issue.
  • To post as a guest, your comment is unpublished.
    guest · 1 years ago
    thanks! this has been helpful
  • To post as a guest, your comment is unpublished.
    Tanya · 1 years ago
    Hi: I have the following naming convention "035849 - Doe, John"
    and I need to be able to have the name separated out as Doe John. Can you please help me. Thank you
    • To post as a guest, your comment is unpublished.
      KB · 1 years ago
      Click on data -> Text to Columns -> Delimited (Next>) then only tick the Comma box and click finish. Hope this helped.
      • To post as a guest, your comment is unpublished.
        Tanya · 1 years ago
        Hi KB: Thank you for your response but unfortunately the command returns: 035849 - Doe, John. What I am looking for is some kind of formula that will extract from the above, as I have thousands of these names, so that the end result is one the name: Doe John, with no comma. Thanks
        • To post as a guest, your comment is unpublished.
          Miemie Yang · 1 years ago
          Hello, Tanya,


          First, you can apply the Text to Columns > Delimited > other(-) to split the text strings to numbers and names, then use the Find and Replace function to replace the commas to nothing. And you will only get the names with no comma.


          Hope it can help you. Thank you!
        • To post as a guest, your comment is unpublished.
          KB · 1 years ago
          Hi Tanya, are you able to use the above code =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2)+1))) then select the new column with "Doe, John" in it and use a find and replace on the comma, or does it need to be in a formula?
          • To post as a guest, your comment is unpublished.
            Tanya · 1 years ago
            Hi KB: This is wonderful, thank you. I definitely wanted it in a formula. Now, when I use this formula, what I get is Doe, John" with a remaining " at the end. I can certainly do another find/replace to remove it along with the comma, but I am wondering if there is a way within the formula to get rid of the remaining "? Thank you so much!!!!!!
            • To post as a guest, your comment is unpublished.
              KB · 1 years ago
              Hi Tanya, sorry I didn't realise the speech marks were also part of the text. In that case perhaps try this code =LEFT(MID(A1, FIND("-", A1) + 2, 255), LEN(MID(A1, FIND("-", A1) + 2, 255)) - 1) then do a find and replace on the comma (Sorry can't think of any way to get rid of this through a formula). Hopefully that should make life easier :)