Finn eventuelle sifre - Excel-tips

Excel - hvordan holder du bare sifrene fra en celle, ikke bokstavene.

Se på video

• Sjekk en kolonne for å se om det er noen sifre i koden
• Regningsmetode 1:
• Flash Fill
• Mike Metode:
• Bruk SUBSTITUTE-funksjonen med en Array Constant.
• Du trenger ikke å bruke Ctrl + Shift + Enter fordi det er en matrisekonstant
• Dette fjerner ett siffer om gangen
• Bruk AND-funksjonen til å se om hvert element i den resulterende matrisen er lik det opprinnelige elementet
• Regningsmetode 3:
• Bruk en VBA-funksjon for å se etter sifre

Videoutskrift

Bill: Hei. Velkommen tilbake. Det er på tide med nok en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får følge av Mike Girvin fra ExcelIsFun. Dette er vår episode 186: ja hvis noen sifre i cellen. Dagens spørsmål sendes inn av Jen, har 13 000 rader med data, må se gjennom en celle, hvis noe tegn er et siffer, merker det som Ja, ellers Nei OK. Vel, Mike, jeg håper du har en fantastisk måte å gjøre dette på, fordi jeg ikke gjør det.

Jeg skal bruke flash fill, og i flash fill vil jeg bare gi det et mønster her med noen bokstaver og tall. Jeg vil forsikre meg om at jeg inkluderer alle mulige tall bare slik at den forstår hva jeg gjør, og 0 slik. Så det er de originale dataene, og så skal jeg få flashfyll til å fikse det for meg, og i den faste versjonen skal vi kvitte oss med alle sifrene. Så jeg skal se og se om det er et siffer. Hvis det er, kvitt deg sånn, og trykk deretter på CONTROL + E for å fylle blits, og det vi burde ha, er nå bare bokstavene, bare bokstavene.

Og så er spørsmålet, endret det seg? Så = HVIS dette er = det vi leter etter, så betyr det at det ikke var noen endringer, ingen sifre, så sier vi Nei, ellers Ja, slik, og dobbeltklikk, kopier det ned, ok, og så har alt med nei ingen sifre der. Vi kvitter oss med den originale raden, og når vi først kopierer denne, CONTROL + C, ALT + E, S, V, og vi kan bli kvitt blitsfyllingen. OK, Mike. La oss se hva du har. (= HVIS (A2 = B2, "Nei", "Ja"))

Mike: Wow ,. Det må være den mest fantastiske kreative unike bruken av flashfyll jeg tror jeg noensinne har sett. Du oppfant en tekst, tok ut de faktiske tallene, CONTROL + E, og den hentet øyeblikkelig ut alle tallene og leverte en ny tekststreng uten tallene, og så gjorde du IF. Helt nydelig.

Ok. Jeg kommer til dette arket her, og jeg vil bruke SUBSTITUTE-funksjonen. Nå, ERSTATTER, skal jeg si at den skal se på den teksten der, og OLD_TEXT jeg vil finne og fjerne, vel, de er alle sifrene. Så jeg skal lage en matrisekonstant (1, 2, 3, 4, 5 alle sifrene og). Nå er det en matrisekonstant, og den sitter i OLD_TEXT fordi jeg ikke legger et eneste element der, men i stedet legger jeg en haug med ting. Dette er en funksjonsargument array operasjon. Det er 10 forskjellige varer her som vil instruere SUBSTITUTE om å levere 10 separate varer, rett, og hvis den finner en av disse artiklene, hva vil jeg da? “”. Det vil fortelle funksjonen å ikke sette noe der,). (= SUBSTITUT (A2, (1,2,3,4,5,6,7,8,9,0), “”))

Nå er markøren på slutten. Når jeg trykker på F9-tasten, sikkert nok fordi det bare er en 0, er alle disse nøyaktig like bortsett fra den siste. For det siste fant SUBSTITUTE 0 og satte ingenting på plass. Nå, CONTROL-Z, CONTROL-ENTER, og jeg kommer til å kopiere den rett ned hit, F2 og F9. Så hvis vi går til 6, er det 5-versjonen, den fjernet 5 der, den fjernet 6 der, og den fjernet 8 der, så det vil være 1, 2, 3 forskjellige ting som er forskjellige. Først når alle varene er nøyaktig like originale vil det fortelle oss at det ikke er noen sifre. FLUKT.

Jeg kommer tilbake til toppen. Det høres ut som, F2, dette er en OG logisk test. OG-funksjon. Jeg vil sjekke om hvert enkelt av disse elementene i den resulterende matrisen er = til det opprinnelige elementet. Når alle er sanne, vil det fortelle meg at det ikke er noen tall i den tekstringen. ), CONTROL + ENTER, jeg får FALSK fordi en av dem internt mangler 0. Jeg skal kopiere dette her nede. Denne vil selvfølgelig få SANN - det samme med disse - fordi alle de internt genererte elementene, hvis jeg F2 akkurat her, er alle disse, F9, nøyaktig lik originalen. FLUKT. Nå kommer jeg opp til toppen. For øvrig slapp jeg å bruke CONTROL + SHIFT + ENTER fordi når du bruker denne arraykonstanten i arrayformelen din, trenger du ikke å bruke CONTROL + SHIFT + ENTER. (= OG (SUBSTITUT (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))

Ok. Jeg kommer til begynnelsen. Det er min logiske test. Hvis alle disse viser seg å være sanne,, verdi hvis de er sanne, i "NEI", ellers sett et JA, "). CONTORL + ENTER. Dobbeltklikk på det. Jeg er nødt til å dobbeltklikke på denne og sende den ned. Ok. Det var litt moro med SUBSTITUTE, en matrisekonstant, en OG logisk test og IF, men jeg sier deg hva, jeg kan fremdeles ikke tro at flashfyll, hvordan du brukte den til å trekke ut alt tall fra det. Ok, jeg skal kaste den tilbake til deg, MrExcel. (= HVIS (OG (SUBSTITUT (A2, (1,2,3,4,5,6,7,8,9,0), "") = A2), "Nei", "Ja"))

Bill: Vel, den formelen med SUBSTITUTE og array konstant og AND, det er freaking fantastisk. Jeg måtte bruke flash fill fordi jeg ikke kunne ha funnet ut denne. Det er strålende. Nå har jeg en tredje vei. La oss se på det.

Nå, her er måten jeg virkelig ville løse dette, bare litt VBA. Så jeg gjør ALT + F11 for å bytte til VBA, INSERT, MODULE, og deretter skrive denne koden. Vi skal lage en ny funksjon kalt HASNUMBERS, og vi vil gi den til celleverdi, og vi skal begynne med ordet ALPHA. Vi ser på hvert eneste tegn, og hvis den koden, hvis (ASC-koden - 06:35) til det tegnet er mellom 48 til 57, så sier vi at det er ANTALL, AVSLUTNINGSFUNKSJON, og bare fortsett. Riktig, så ser det ut til den finner et siffer. Når den gjør det, returnerer den HASNUMBERS. Så her skal vi si = HASNUMBERS, pek på den cellen og dobbeltklikk for å kopiere den ned. Hver gang det ser et siffer der borte, vil tallene, ALPHA, være enkle å sortere ut. (= HasNumbers (A2))

Greit, rask episodeoppsummering. Mål: sjekk kolonnen for å se om det er noen sifre i tegnkoden, koden i cellen. Jeg brukte flashfyll for å fjerne sifrene, deretter lengdefunksjonen for å se om den endret seg eller ikke. Mike hadde en strålende formel, SUBSTITUTE-funksjon med en arraykonstant. Du trenger ikke CONTROL + SHIFT + ENTER. Du må fjerne ett siffer om gangen og deretter bruke AND-funksjonen til å se på alle de 10 resultatene for å se om hver og en er lik den opprinnelige varen. Strålende vei å gå, og deretter, min reserve, bruk en VBA-funksjon for å se etter sifre.

Vel hei. Jeg vil takke alle for at de var innom. Vi sees neste gang for en ny netcast fra og ExcelIsFun.

Last ned fil

Last ned eksempelfilen her: Duel186.xlsm