Cov Qauv Siv Cov Ntaub Ntawv Huv Huv Hauv Excel

zoo qauv ua qauv

Xyoo, Kuv tau siv cov ntawv tshaj tawm los ua cov khoom siv tsis yog piav qhia txog yuav ua li cas thiaj ua tau li cas, tab sis kuj tseem khaws cia rau kuv tus kheej saib tom qab! Niaj hnub no, peb muaj tus neeg tau muab cov ntaub ntawv cov neeg siv khoom pov tseg uas yog kev puas tsuaj. Yuav luag txhua daim teb tsis raug thiab; Vim li ntawd, peb tsis muaj peev xwm import cov ntaub ntawv. Thaum muaj qee qhov zoo ntxiv-ons rau Excel los ua kev siv kev siv Tej Duab Siv Ua Si, peb khiav lub Chaw Haujlwm rau Mac uas yuav tsis txhawb nqa macros. Hloov chaw, peb ntsia rau cov qauv ncaj qha los pab. Kuv xav tias kuv yuav qhia qee qhov ntawm no tsuas yog lwm tus tuaj yeem siv lawv.

Tshem Cov Neeg Tsis Muaj Yam ntxwv

Cov kab ke feem ntau xav tau cov naj npawb xov tooj rau hauv qhov tshwj xeeb, lej 11 tus lej nrog lub teb chaws code thiab tsis muaj cim. Txawm li cas los xij, folks feem ntau nkag mus rau cov ntaub ntawv no nrog kab txuas thiab lub sijhawm hloov. Nov yog tus qauv zoo rau tshem tag nrho cov tsis muaj lej cim hauv Excel. Cov qauv tshuaj xyuas cov ntaub ntawv hauv cell A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Tam sim no koj tuaj yeem luam tawm ntawm kab ntawv txiav tawm thiab siv Kho kom raug> Muab tshuaj Muaj nuj nqis kom sau dhau cov ntaub ntawv nrog qhov tsim nyog tshwm sim.

Soj ntsuam ntau cov liaj teb nrog IB LOSSIS

Peb feem ntau ntxuav tawm cov ntaub ntawv tsis tiav los ntawm kev ntshuam. Cov neeg siv khoom tsis paub tias koj tsis tas yuav tsum sau cov qauv hierarchical nyuaj thiab tias koj tuaj yeem sau OR nqe lus tawm. Hauv qhov ua piv txwv hauv qab no, Kuv xav tshawb xyuas A2, B2, C2, D2, lossis E2 rau cov ntaub ntawv uas ploj lawm. Yog tias muaj cov ntaub ntawv tsis txaus siab, Kuv yuav xa rov qab 0, tsis li a 1. Uas yuav cia kuv xaiv qhov kev txiav txim cov ntaub ntawv thiab rho tawm cov ntaub ntawv tsis tiav.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Luas thiab teb ua teb

Yog tias koj cov ntaub ntawv muaj Ua Ntej thiab Lub Xeem Lub Npe, tab sis koj cov ntshuam muaj tag nrho cov npe teb, koj tuaj yeem ua ke cov teb ua ke zoo zoo siv cov built hauv Excel Function Concatenate, tab sis nco ntsoov siv TRIM tshem txhua qhov chaw khoob ua ntej lossis tom qab ntawv nyeem. Peb qhwv tag nrho cov teb nrog TRIM thaum muaj ib qho ntawm cov liaj tsis muaj ntaub ntawv:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Txheeb Siv Chaw Nyob Email Siv

Mis zoo nkauj yooj yim uas zoo rau ob lub @ thiab. nyob hauv email chaw nyob:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Sau npe Lub Npe thiab Lub Xeem

Qee zaum, cov teeb meem tsis sib thooj. Koj cov ntaub ntawv muaj lub npe tag nrho tab sis koj yuav tsum tawm ntawm thawj lub npe thiab lub xeem. Cov qauv no yuav nrhiav qhov chaw ntawm lub npe thiab lub xeem thiab sau cov ntawv thaum tsim nyog. NWS kuj daws yog tias tsis muaj lub xeem los yog muaj kev nkag teb chaws hauv A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Thiab lub xeem lub npe:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Txwv tus naj npawb ntawm Cov Cim thiab Ntxiv…

Koj puas tau xav ntxuav koj cov meta cov lus piav qhia? Yog tias koj xav rub cov ntsiab lus rau hauv Excel thiab tom qab ntawd luas cov ntsiab lus rau siv nyob rau hauv kab lus piav qhia Meta (150 txog 160 cim), koj tuaj yeem ua qhov ntawd siv cov mis no los ntawm Kuv HnovCov. Nws huv si tawg qhov kev piav qhia ntawm qhov chaw thiab tom qab ntawd ntxiv cov…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Yog lawm, cov no tsis tau txhais tias yuav tsum muaj txhij txhua… tsuas yog qee cov qauv ceev los pab koj kom pib dhia! Puas muaj lwm yam qauv uas koj pom koj tus kheej siv? Ntxiv rau lawv hauv cov lus thiab kuv yuav muab koj qhov credit thaum kuv hloov cov ntawv no.

Yuav ua li cas koj xav hais tias?

Qhov Web site no siv Akismet los txo cov kev pabcuam. Kawm li cas koj cov ntaub ntawv tawm tswv yim tiav.