Algemene formule vir opruiming van data in Excel

uitblink formules

Ek gebruik die publikasie al jare lank as 'n bron om nie net te beskryf hoe om dinge te doen nie, maar om ook vir myself 'n rekord te hou om later op te soek! Vandag het ons 'n kliënt gehad wat 'n kliëntedata-lêer aan ons gegee het wat 'n ramp was. Feitlik elke veld is verkeerd geformateer en; gevolglik kon ons nie die data invoer nie. Alhoewel daar 'n paar uitstekende byvoegings vir Excel is om die skoonmaak met Visual Basic uit te voer, gebruik ons ​​Office for Mac wat nie makro's ondersteun nie. Ons soek eerder reguit formules om te help. Ek het gedink ek wil sommige hiervan deel, net sodat ander dit kan gebruik.

Verwyder nie-numeriese karakters

Stelsels vereis dat telefoonnommers in 'n spesifieke 11-syferformule met die landkode en geen leestekens ingevoeg moet word nie. Mense voer hierdie data egter dikwels met streepies en tydperke in. Hier is 'n uitstekende formule vir verwyder alle nie-numeriese karakters in Excel. Die formule hersien die data in sel 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))

Nou kan u die resulterende kolom kopieer en gebruik Wysig> Plak waardes om die data oor te skryf met die behoorlik geformateerde resultaat.

Evalueer verskeie velde met 'n OF

Ons maak dikwels onvolledige rekords van 'n invoer skoon. Gebruikers besef nie dat u nie altyd ingewikkelde hiërargiese formules hoef te skryf nie en dat u eerder 'n OF-stelling kan skryf. In hierdie onderstaande voorbeeld wil ek A2, B2, C2, D2 of E2 nagaan vir ontbrekende data. As daar geen data ontbreek nie, gaan ek 'n 0 teruggee, anders 'n 1. Dit sal my in staat stel om die data te sorteer en die onvolledige rekords te verwyder.

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

Knip en saambind velde

As u data 'n voor- en vannaam-velde het, maar u invoer 'n volledige naamveld het, kan u die velde netjies saamvat met behulp van die ingeboude Excel-funksie-aaneenskakeling, maar gebruik TRIM om leë spasies voor of na die teks. Ons wikkel die hele veld met TRIM in die geval dat een van die velde nie data het nie:

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

Kyk vir geldige e-posadres

'N Redelik eenvoudige formule wat beide die @ en. in 'n e-pos adres:

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

Pak voor- en laaste name uit

Soms is die probleem die teenoorgestelde. U gegewens het 'n volledige naamveld, maar u moet die voor- en vanname ontleed. Hierdie formules soek die spasie tussen die voor- en vannaam en gryp teks waar nodig. Dit word ook hanteer as daar geen van is nie, of as daar 'n leë inskrywing in A2 is.

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

En die van:

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

Beperk die aantal karakters en voeg by…

Wou u ooit u metabeskrywings opruim? As u die inhoud in Excel wil oplaai en dan die inhoud wil snoei vir gebruik in 'n Meta-beskrywingsveld (150 tot 160 karakters), kan u dit doen met behulp van hierdie formule vanaf My kol. Dit breek die beskrywing op 'n spasie skoon en voeg dan die ... by:

=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)

Dit is natuurlik nie bedoel om omvattend te wees nie ... net 'n paar vinnige formules om u te help! Watter ander formules gebruik u? Voeg dit by die opmerkings en ek gee u krediet as ek hierdie artikel opdateer.

Wat dink jy?

Hierdie webwerf gebruik Akismet om spam te verminder. Leer hoe jou opmerking verwerk is.