Excel Cov Qauv Rau Kev Ntxuav Cov Ntaub Ntawv
Tau ntau xyoo, kuv tau siv cov ntawv tshaj tawm los piav qhia yuav ua li cas thiab khaws cov ntaub ntawv rau kuv tus kheej kom saib tom qab! Ib tug neeg muab peb cov ntaub ntawv cov neeg siv khoom uas yog kev puas tsuaj. Zoo tshaj txhua daim teb tau misformatted, thiab vim li ntawd, peb tsis tuaj yeem import cov ntaub ntawv. Thaum muaj qee qhov zoo ntxiv-ons rau Excel los ua qhov ntxuav siv Visual Basic, peb khiav Office rau Mac, uas yuav tsis txhawb macros. Hloov chaw, peb nrhiav cov qauv ncaj qha los pab. Kuv xav tias kuv yuav qhia qee qhov ntawm no kom koj siv tau.
Tshem Cov Neeg Tsis Muaj Yam ntxwv
Cov kab ke feem ntau xav tau cov lej xov tooj tso rau hauv cov qauv 11 tus lej tshwj xeeb nrog lub teb chaws chaws thiab tsis muaj cov cim. Txawm li cas los xij, tib neeg feem ntau nkag mus rau cov ntaub ntawv no nrog dashes thiab lub sijhawm hloov. Nov yog ib qho qauv zoo heev 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))
Koj tuaj yeem luam cov kab ntawv tshwm sim 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 tshem tawm cov ntaub ntawv tsis tiav los ntawm kev ntshuam. Cov neeg siv tsis paub tias koj tsis tas yuav sau cov qauv hierarchical nyuaj thiab koj tuaj yeem sau OR nqe lus hloov. Kuv xav kuaj A2, B2, C2, D2, lossis E2 rau cov ntaub ntawv ploj hauv qhov piv txwv hauv qab no. Yog tias muaj cov ntaub ntawv ploj lawm, kuv yuav rov qab 0; txwv tsis pub, a 1. Qhov ntawd yuav cia kuv txheeb cov ntaub ntawv thiab tshem 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 npe thiab lub xeem, tab sis koj cov khoom xa tuaj muaj lub npe tag nrho, koj tuaj yeem sib txuas cov teb ua ke zoo siv lub built-in Excel Function Concatenate, tab sis nco ntsoov siv TRIM tshem tawm qhov chaw khoob ua ntej lossis tom qab lub ntawv nyeem. Peb qhwv tag nrho daim teb nrog TRIM yog tias ib qho ntawm cov teb tsis muaj cov ntaub ntawv:
=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))
Txheeb Siv Chaw Nyob Email Siv
Ib qho yooj yim zoo nkauj mis uas zoo rau ob qho tib si @ thiab . hauv email chaw nyob (tsis yog RFC txuj
):=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))
Sau npe Lub Npe thiab Lub Xeem
Qee zaum, qhov teeb meem yog qhov txawv. Koj cov ntaub ntawv muaj lub npe tag nrho, tab sis koj yuav tsum txheeb xyuas cov npe thiab lub xeem. Cov qauv no nrhiav qhov chaw nruab nrab ntawm lub npe thiab lub xeem thiab rub cov ntawv thaum tsim nyog. Nws kuj tseem ua haujlwm yog tias tsis muaj lub xeem lossis ib qho chaw nkag 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 lus piav qhia meta? Yog tias koj xav rub cov ntsiab lus rau hauv Excel thiab txiav cov ntsiab lus siv hauv Meta Description (150 txog 160 cim), koj tuaj yeem ua qhov ntawd siv cov qauv no. Nws ntxuav cov lus 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)
Tau kawg, cov no tsis yog txhais tau tias yuav tsum tau ua tiav… tsuas yog qee cov qauv nrawm los pab koj pib dhia! Lwm cov qauv twg koj pom koj tus kheej siv? Ntxiv rau lawv hauv cov lus, thiab kuv yuav muab credit rau koj thaum kuv hloov kho cov kab lus no.