Vind alle cijfers - Excel-tips

Excel - hoe u alleen de cijfers van een cel kunt behouden, niet de letters.

Bekijk video

• Controleer een kolom om te zien of er cijfers in de code staan
• Factuurmethode 1:
• Flash Invullen
• Mike-methode:
• Gebruik de functie SUBSTITUTE met een array-constante.
• U hoeft Ctrl + Shift + Enter niet te gebruiken omdat het een matrixconstante is
• Hiermee wordt één cijfer per keer verwijderd
• Gebruik de functie EN om te zien of elk item in de resulterende array gelijk is aan het oorspronkelijke item
• Factuurmethode 3:
• Gebruik een VBA-functie om op cijfers te controleren

Videotranscriptie

Bill: Hé. Welkom terug. Het is tijd voor nog een Dueling Excel Podcast. Ik ben Bill Jelen van. Ik zal worden vergezeld door Mike Girvin van ExcelIsFun. Dit is onze aflevering 186: ja als er cijfers in de cel staan. De vraag van vandaag is ingezonden door Jen, heeft 13.000 rijen met gegevens, moet door een cel kijken. Als een teken een cijfer is, markeer het dan als Ja, anders Nee. Goed. Mike, ik hoop dat je een geweldige manier hebt om dit te doen, want dat doe ik niet.

Ik ga flash fill gebruiken en, in flash fill, ga ik het hier gewoon een patroon geven met wat letters en cijfers. Ik wil ervoor zorgen dat ik alle mogelijke getallen opneem, zodat het begrijpt wat ik aan het doen ben, en 0 op die manier. Dus er zijn de originele gegevens, en dan ga ik het flash-vullen laten repareren voor mij, en in de vaste versie gaan we alle cijfers verwijderen. Dus ik zal kijken of er een cijfer is. Als dat het geval is, gooi het dan zo weg en druk dan op CONTROL + E om te knipperen, en wat we nu zouden moeten hebben zijn alleen de letters, alleen de letters.

En dan is de vraag: is het veranderd? Dus = ALS dit = is naar wat we zoeken, dan betekent dat dat er geen wijzigingen waren, geen cijfers, dan zeggen we Nee, anders Ja, zo, en dubbelklik, kopieer dat naar beneden, oké, en dan heeft alles met een nee daar geen cijfers. We verwijderen onze originele rij, en zodra we dit kopiëren, CONTROL + C, ALT + E, S, V, en we kunnen de flitsopvulling verwijderen. Oké, Mike. Eens kijken wat je hebt. (= ALS (A2 = B2, "Nee", "Ja"))

Mike: Wauw ,. Dat moet het meest verbazingwekkende creatieve en unieke gebruik van flitsopvulling zijn dat ik ooit heb gezien. Je bedacht een tekst, haalde de werkelijke getallen eruit, CONTROL + E, en het haalde onmiddellijk alle getallen eruit en leverde een nieuwe tekstreeks zonder de getallen, en toen deed je je IF. Absoluut prachtig.

Oké. Ik ga hier naar dit blad toe en ik ga de functie VERVANGEN gebruiken. Nu, SUBSTITUUT, ik ga het vertellen om daar naar die tekst te kijken, en de OLD_TEXT die ik wil vinden en verwijderen, nou, het zijn allemaal cijfers. Dus ik ga een matrixconstante maken (1, 2, 3, 4, 5 alle cijfers en). Dat is nu een arrayconstante en het zit in OLD_TEXT omdat ik daar geen enkel item plaats, maar in plaats daarvan een aantal items. Dit is een array-bewerking van een functieargument. Er zijn hier 10 verschillende items die SUBSTITUTE de opdracht geven om 10 afzonderlijke items correct af te leveren, en als het een van die items vindt, wat wil ik dan? "". Dat zal de functie vertellen om daar niets te plaatsen). (= VERVANGEN (A2; (1,2,3,4,5,6,7,8,9,0), ""))

Nu is mijn cursor aan het einde. Toen ik op de F9-toets drukte, zeker omdat er maar een 0 is, zijn deze allemaal precies gelijk behalve de laatste. Voor de laatste vond de SUBSTITUUT de 0 en zette niets op zijn plaats. Nu, CONTROL-Z, CONTROL-ENTER en ik ga het naar hier kopiëren, F2 en F9. Dus als we naar de 6 gaan, is er de 5-versie, hij heeft de 5 daar verwijderd, de 6 daar verwijderd en de 8 daar verwijderd, dus er zullen 1, 2, 3 verschillende items zijn die anders zijn. Alleen als alle items exact gelijk zijn aan het originele item, zal het ons vertellen dat er geen cijfers zijn. ONTSNAPPEN.

Ik ga terug naar de top. Het klinkt als, F2, dit is een EN logische test. EN functie. Ik wil controleren of elk van die items in die resulterende array = is voor het oorspronkelijke item. Als ze allemaal waar zijn, zal het me vertellen dat er geen cijfers in die tekstring staan. ), CONTROL + ENTER, ik krijg een FALSE omdat een van hen intern die 0 mist. Ik ga dit hier kopiëren. Deze krijgt natuurlijk een TRUE - hetzelfde met deze - omdat alle intern gegenereerde items, als ik hier F2, al deze, F9, exact gelijk zijn aan het origineel. ONTSNAPPEN. Nu kom ik naar de top. Overigens hoefde ik CONTROL + SHIFT + ENTER niet te gebruiken, want als je deze matrixconstante in je matrixformule gebruikt, hoef je CONTROL + SHIFT + ENTER niet te gebruiken. (= AND (VERVANGEN (A2; (1,2,3,4,5,6,7,8,9,0), "") = A2))

Oké. Ik kom bij het begin. Dat is mijn logische test. Als al deze waarden waar blijken te zijn, waarde indien waar, in "NEE", voer anders JA in, "). CONTORL + ENTER. Dubbelklik daarop. Ik zal hierop moeten dubbelklikken en het naar beneden moeten sturen. Oké. Dat was een beetje leuk met de VERVANGING, een matrixconstante, een logische EN-test en de ALS, maar ik kan je wat zeggen, ik kan die flitsvulling nog steeds niet geloven, hoe je het gebruikte om in wezen alle nummers daarvan. Oké, ik gooi het terug naar u, meneer Excel. (= ALS (EN (VERVANGEN (A2, (1,2,3,4,5,6,7,8,9,0), "") = A2), "Nee", "Ja"))

Bill: Nou, die formule met SUBSTITUTE en de matrixconstante en AND, dat is geweldig geweldig. Ik moest flitsopvulling gebruiken omdat ik deze niet had kunnen achterhalen. Dat is geweldig. Nu heb ik een derde manier. Laten we daar eens naar kijken.

Dit is de manier waarop ik dit echt zou oplossen, een klein beetje VBA. Dus ik doe ALT + F11 om over te schakelen naar VBA, INSERT, MODULE en typ vervolgens deze code. We gaan een nieuwe functie maken genaamd HASNUMBERS en we gaan deze doorgeven aan celwaarde, en we beginnen met het woord ALPHA. We kijken naar elk afzonderlijk teken, en als die code, als de (ASC-code - 06:35) van dat teken tussen 48 en 57 ligt, dan zeggen we dat het NUMMERS is, FUNCTIE EXIT, en ga gewoon door. Juist, het kijkt totdat het een cijfer vindt. Als dit het geval is, retourneert het HASNUMBERS. Dus hier gaan we zeggen = HASNUMBERS, wijs naar die cel en dubbelklik om die naar beneden te kopiëren. Elke keer als het daar een cijfer ziet, krijgt het de CIJFERS, ALPHA, gemakkelijk te sorteren. (= HasNumbers (A2))

Oké, korte samenvatting van de aflevering. Doel: controleer de kolom om te zien of er cijfers in de tekencode, de code in de cel, staan. Ik heb flitsvulling gebruikt om de cijfers te verwijderen en vervolgens de lengtefunctie om te zien of deze is gewijzigd of niet. Mike had een briljante formule, de functie SUBSTITUTE met een matrixconstante. U hebt CONTROL + SHIFT + ENTER niet nodig. U moet één cijfer tegelijk verwijderen en vervolgens de EN-functie gebruiken om alle 10 resultaten te bekijken om te zien of elk gelijk is aan het originele item. Briljante manier om te gaan, en dan, mijn terugval, gebruik een VBA-functie om te controleren op cijfers.

Hallo daar. Ik wil iedereen bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van en ExcelIsFun.

Download bestand

Download het voorbeeldbestand hier: Duel186.xlsm