Find alle cifre - Excel-tip

Excel - hvordan man kun holder cifrene fra en celle, ikke bogstaverne.

Se video

• Kontroller en kolonne for at se, om der er cifre i koden
• Regningsmetode 1:
• Flashfyld
• Mike Metode:
• Brug SUBSTITUTE-funktionen med en Array Constant.
• Du behøver ikke bruge Ctrl + Shift + Enter, fordi det er en matrixkonstant
• Dette fjerner et ciffer ad gangen
• Brug AND-funktionen til at se, om hvert element i det resulterende array er lig med det originale element
• Regningsmetode 3:
• Brug en VBA-funktion til at kontrollere cifre

Videoudskrift

Bill: Hej. Velkommen tilbage. Det er tid til endnu en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får følgeskab af Mike Girvin fra ExcelIsFun. Dette er vores episode 186: ja, hvis der er cifre i cellen. Dagens spørgsmål sendes ind af Jen, har 13.000 rækker data, skal se gennem en celle, hvis et tegn er et ciffer, skal du markere det som Ja, ellers Nej. Okay. Nå, Mike, jeg håber, at du har en fantastisk måde at gøre dette på, fordi jeg ikke gør det.

Jeg skal bruge flashfyld, og i flashfyld skal jeg bare give det et mønster her med nogle bogstaver og tal. Jeg vil sørge for, at jeg inkluderer alle mulige tal, så det forstår, hvad jeg laver, og 0 sådan. Så der er de originale data, og så får jeg flash-udfyld til at rette det for mig, og i den faste version skal vi slippe af med alle cifrene. Så jeg vil se om der er et ciffer. Hvis der er, skal du slippe af med det på den måde og derefter trykke på CONTROL + E for at udfylde flash, og hvad vi skulle have, er nu bare bogstaverne, bare bogstaverne.

Og så er spørgsmålet, ændrede det sig? Så = HVIS dette er = til det, vi leder efter, betyder det, at der ikke var nogen ændringer, ingen cifre, så siger vi Nej, ellers Ja, sådan, og dobbeltklik, kopier det ned, okay og så har noget med et nej ingen cifre der. Vi slipper af med vores originale række, og når vi først har kopieret dette, CONTROL + C, ALT + E, S, V, og vi kan slippe af med flashudfyldningen. Okay, Mike. Lad os se, hvad du har. (= HVIS (A2 = B2, "Nej", "Ja"))

Mike: Wow ,. Det skal være den mest fantastiske kreative unikke brug af flashfyld, jeg tror jeg nogensinde har set. Du opfandt en tekst, tog de faktiske tal ud, CONTROL + E, og den ekstraherede øjeblikkeligt alle numrene og leverede en ny tekststreng uden tallene, og så gjorde du din IF. Absolut smuk.

I orden. Jeg kommer over til dette ark lige her, og jeg vil bruge SUBSTITUTE-funktionen. Nu, SUBSTITUT, jeg vil fortælle det at se på den tekst lige der, og den OLD_TEXT, jeg vil finde og fjerne, ja, de er alle cifre. Så jeg vil oprette en matrixkonstant (1, 2, 3, 4, 5 alle cifrene og). Nu er det en matrixkonstant, og den sidder i OLD_TEXT, fordi jeg ikke lægger en enkelt vare der, men i stedet lægger jeg en masse ting. Dette er en funktionsargument array-operation. Der er 10 forskellige varer her, som vil instruere SUBSTITUTE om at levere 10 separate varer, lige, og hvis den finder en af ​​disse varer, hvad vil jeg så have? “”. Det vil fortælle funktionen at sætte intet der). (= UDSKIFTNING (A2, (1,2,3,4,5,6,7,8,9,0), “”))

Nu er min markør i slutningen. Når jeg trykker på F9-tasten, helt sikkert fordi der kun er et 0, er alle disse nøjagtigt lige bortset fra den sidste. For det sidste fandt SUBSTITUTUT 0 og placerede intet på sin plads. Nu, CONTROL-Z, CONTROL-ENTER, og jeg vil kopiere det lige ned til her, F2 og F9. Så hvis vi går til 6, er der 5-versionen, den fjernede 5 der, den fjernede 6 der, og den fjernede 8 der, så der vil være 1, 2, 3 forskellige ting, der er forskellige. Først når alle varerne er nøjagtigt lig med den originale vare, fortæller den os, at der ikke er nogen cifre. FLUGT.

Jeg kommer tilbage til toppen. Det lyder som, F2, dette er en OG logisk test. OG funktion. Jeg vil kontrollere, om hver enkelt af disse emner i den resulterende matrix er = til den oprindelige vare. Når de alle er sande, så fortæller det mig, at der ikke er nogen tal i den tekstring. ), CONTROL + ENTER, jeg får en FALSK, fordi en af ​​dem internt mangler 0. Jeg vil kopiere dette hernede. Denne får naturligvis en SAND - det samme med disse - fordi alle de internt genererede genstande, hvis jeg F2 lige her, alle disse, F9, er nøjagtigt lig originalen. FLUGT. Nu kommer jeg op til toppen. Forresten behøvede jeg ikke at bruge CONTROL + SHIFT + ENTER, for når du bruger denne arraykonstant i din arrayformel, behøver du ikke bruge CONTROL + SHIFT + ENTER. (= OG (UDSKIFTNING (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))

I orden. Jeg kommer til begyndelsen. Det er min logiske test. Hvis alle disse viser sig at være sande, skal du værdi, hvis de er sande, i ”NEJ”, ellers sæt et JA, ”). CONTORL + ENTER. Dobbeltklik på det. Jeg bliver nødt til at dobbeltklikke på denne og sende den ned. I orden. Det var lidt sjovt med SUBSTITUTE, en arraykonstant, EN OG logisk test og IF, men jeg siger dig hvad, jeg kan stadig ikke tro, at flashfyld, hvordan du brugte det til i det væsentlige at udtrække alt tal fra det. Okay, jeg vil smide det tilbage til dig, MrExcel. (= HVIS (OG (UDSKIFTNING (A2, (1,2,3,4,5,6,7,8,9,0), "") = A2), "Nej", "Ja"))

Bill: Nå, den formel med SUBSTITUTE og array konstant og AND, det er freaking fantastisk. Jeg var nødt til at bruge flashfyld, fordi jeg ikke kunne have fundet ud af denne. Det er strålende. Nu har jeg en tredje vej. Lad os se på det.

Now, here's the way that I would really solve this, just a little bit of VBA. So, I do ALT+F11 to switch over to VBA, INSERT, MODULE, and then type this code. We're going to create a new function called HASNUMBERS and we're going to pass it to cell value, and we're going to start out with the word ALPHA. We look at every single character, and if that code, if the (ASC code - 06:35) of that character is between 48 to 57, then we say that it's NUMBERS, EXIT FUNCTION, and just keep going. Right, so, it looks until it finds a digit. When it does, it returns HASNUMBERS. So, here, we’re going to say =HASNUMBERS, point to that cell, and double-click to copy that down. Anytime it sees a digit over there, will get the NUMBERS, ALPHA, easy to sort them out. (=HasNumbers(A2))

Okay, hurtig episode sammenfatning. Mål: Tjek kolonnen for at se, om der er cifre i tegnkoden, koden i cellen. Jeg brugte flashfyld til at fjerne cifrene og derefter længdefunktionen for at se, om den ændrede sig eller ej. Mike havde en strålende formel, SUBSTITUTE-funktion med en arraykonstant. Du har ikke brug for CONTROL + SHIFT + ENTER. Du er nødt til at fjerne et ciffer ad gangen og derefter bruge AND-funktionen til at se på alle 10 resultater for at se, om hver enkelt er lig med den oprindelige vare. Strålende vej at gå, og brug derefter en VBA-funktion til at tjekke om cifre, min tilbageførsel.

Nå, hej. Jeg vil gerne takke alle for at komme forbi. Vi ses næste gang til endnu en netcast fra og ExcelIsFun.

Download fil

Download eksempelfilen her: Duel186.xlsm