Yuav ua li cas cais cov npe thiab xeem hauv Excel

Cov txheej txheem:

Yuav ua li cas cais cov npe thiab xeem hauv Excel
Yuav ua li cas cais cov npe thiab xeem hauv Excel
Anonim

Yuav Paub

  • Highlight tag nrho cov npe, tom qab ntawd mus rau Data tab thiab xaiv Text to Kum. Xaiv Delimited, ces xaiv tus delimiter teem ib lo lus uas peb.
  • Xwb, xaiv lub xov tooj khoob thiab siv LEFT muaj nuj nqi kom tau txais thawj lub npe thiab txoj haujlwm RIGHT kom tau lub xeem.
  • Los yog, xaiv lub xov tooj khoob thiab ntaus thawj lub npe ntawm thawj cov ntaub ntawv, tom qab ntawd mus rau Data > Flash Sau. Hauv lub cell tom ntej, rov qab nrog lub xeem.

Kab lus no piav qhia yuav ua li cas cais thawj lub npe thiab xeem hauv Microsoft Excel. Cov lus qhia siv rau Microsoft Excel 365, Microsoft Excel 2019, 2016, thiab 2013.

cais cov npe hauv Excel Siv Cov Ntawv rau Kab

Excel muaj ntau lub luag haujlwm thiab cov yam ntxwv uas ua rau koj muaj peev xwm khaws koj cov ntaub ntawv. Piv txwv li, koj tuaj yeem faib cov npe thiab lub xeem uas siv lub npe hu ua Text to Columns:

  1. Qhib cov ntaub ntawv Excel uas muaj cov ntaub ntawv koj xav cais.

    Image
    Image
  2. Xaiv cov ntaub ntawv koj xav cais, hauv qhov no, cov npe Tag Nrho.

    Image
    Image

    Yog tias koj cov ntaub ntawv suav nrog cov headers, tsis txhob xaiv lawv, txwv tsis pub, Excel tseem yuav sim cais cov ntaub ntawv hauv headers.

  3. Xaiv lub Data tab.

    Image
    Image
  4. Xaiv Text to Column hauv kab.

    Image
    Image
  5. Select Delimited, ces xaiv Next.

    Image
    Image
  6. Xaiv hom delimiter koj cov ntaub ntawv muaj, ces xaiv Tom ntej. Yog tias koj qhov kev xaiv delimiter tsis tau teev tseg, xaiv Lwm yam thiab nkag mus rau tus lej koj xav siv hauv cov ntawv sau tau muab.

    Image
    Image

    Nyob hauv peb cov ntaub ntawv teev, cov ntaub ntawv yog sib cais nrog qhov chaw, yog li ntawd, peb yuav xyuas qhov Spaces checkbox raws li peb delimiter.

  7. Los ntawm lub neej ntawd, Excel yuav sau cov ntaub ntawv uas twb muaj lawm. Yog hais tias koj tsis xav kom koj cov ntaub ntawv yuav overwritten koj yuav tau hloov lub Destination tus nqi. Xaiv qhov Destination thiab teem caij mus.

    Image
    Image
  8. Thaum koj paub tseeb tias koj qhov chaw nyob, xaiv Tiav.

    Image
    Image
  9. Excel yuav muab koj cov ntaub ntawv tso rau hauv lub hlwb.

    Image
    Image

Split First and Last Name Siv Excel Formulas

Txoj kev no yog qhov nyuaj me ntsis, tab sis nws kuj tso cai rau kev hloov pauv ntau dua ntawm kev sib cais cov ntaub ntawv, raws li koj tau hais meej cov ntaub ntawv koj yuav rho tawm siv cov qauv.

Yuav kom ua tiav cov ntaub ntawv xav tau, koj yuav siv txoj haujlwm sab laug, txoj haujlwm zoo, thiab tshawb nrhiav haujlwm.

Thaum siv cov qauv, koj yuav tau siv cov qauv sib txawv los faib lub npe thiab lub xeem, thiab nws yuav nyob ntawm seb hom ntawv ntawm cov ntaub ntawv qub.

  1. Qhib cov ntaub ntawv Excel uas muaj cov ntaub ntawv koj xav cais.

    Image
    Image
  2. Xaiv lub xovtooj uas koj xav tau Thawj lossis Lub Npe. Rau cov kauj ruam no, peb cov ntaub ntawv teev yog formatted xws li "Lub Npe + Chaw + Lub Npe." Yog li ntawd, peb yuav siv txoj haujlwm LEFT kom tau Thawj Lub Npe thiab Txoj Cai Txoj Cai kom tau Lub Npe.

    Yog tias koj cov ntaub ntawv teev nyob rau hauv ib hom ntawv sib txawv lossis muaj qhov sib txawv, ces koj yuav tau kho cov qauv raws li.

  3. Sau tus qauv rau Thawj Lub Npe thiab nias Enter.

    =LEFT(A2, SEARCH(" ", A2)-1)

    Image
    Image
  4. Nyob hauv lub xovtooj tom ntej, nkag mus rau tus qauv kom tau Lub Npe thiab nias Enter.

    =RIGHT(A2, LEN(A2)-SEARCH("", A2))

    Image
    Image
  5. Xaiv ob lub hlwb nrog cov qauv.

    Image
    Image
  6. ob-nias lub hauv qab txoj cai kaum ntawm cov hlwb xaiv. Qhov no yuav txuas ntxiv cov qauv mus txog thaum kawg ntawm cov ntaub ntawv.

    Image
    Image

Split npe hauv Excel Siv Flash Sau

Txoj kev no yog qhov yooj yim tshaj plaws ntawm lawv txhua tus, tab sis nws tsuas yog muaj nyob hauv Microsoft Excel 2016 thiab tom qab.

Txoj kev no tsis txhawb nqa hauv Microsoft Excel 2013 lossis cov laus dua ntawm Excel.

  1. Qhib cov ntaub ntawv Excel uas muaj cov ntaub ntawv koj xav cais.

    Image
    Image
  2. Xaiv lub xovtooj uas koj xav sau cov npe thawj zaug thiab ntaus tus thawj lub npe ntawm thawj cov ntaub ntawv.

    Image
    Image
  3. Xaiv lub Data tab.

    Image
    Image
  4. Select Flash Sau.

    Image
    Image
  5. Excel yuav cia li sau thawj cov npe ntawm tus so ntawm cov ntaub ntawv ntawm koj cov ntaub ntawv.

    Image
    Image
  6. Nyob hauv lub xovtooj tom ntej, ntaus lub xeem ntawm thawj cov ntaub ntawv ntawm koj cov ntaub ntawv manually.

    Image
    Image
  7. Rov ua kauj ruam 3 thiab 4.
  8. Excel yuav cia li sau cov npe xeem ntawm cov ntaub ntawv seem ntawm koj cov ntaub ntawv.

    Image
    Image

Pom zoo: