Έχουμε μάθει πώς να αφαιρούμε αριθμητικές τιμές από ένα κελί στο Excel 2016 και παλαιότερα. Οι τύποι που χρησιμοποιήσαμε ήταν λίγο περίπλοκοι, αλλά τώρα το Excel 2019 και το 365 είναι στο παιχνίδι με νέα παιχνίδια, εννοώ λειτουργίες.
Το Excel 2019 και το 365 εισάγουν μερικές νέες συναρτήσεις (TEXTJOIN και SEQUENCE) που μπορούν να διευκολύνουν το έργο της αφαίρεσης αριθμητικών χαρακτήρων και να ανακτήσουν μόνο μη αριθμητικές τιμές σε ένα νέο κελί. Θα χρησιμοποιήσουμε τύπους που μπορούν να μας βοηθήσουν σε αυτό, πιο βολικά.
Γενικός τύπος
| =TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ(ISERROR(ΣΤΑ ΜΕΣΑ(jumbled_text,ΑΛΛΗΛΟΥΧΙΑ(NumChars), 1) +0),ΣΤΑ ΜΕΣΑ(jumbled_text,ΑΛΛΗΛΟΥΧΙΑ(NumChars), 1), ""))) |
Jumbled_text: Αυτό είναι το κείμενο προέλευσης από το οποίο θέλετε να εξαγάγετε όλες τις αριθμητικές τιμές.
NumChars: Αυτός είναι ο συνολικός αριθμός χαρακτήρων που θέλετε να επεξεργαστείτε. Το jumbled_text δεν πρέπει να έχει περισσότερους χαρακτήρες από αυτόν τον αριθμό (συνδυασμοί χαρακτήρων και αριθμών).
Ας δούμε ένα παράδειγμα για να ξεκαθαρίσουμε τα πράγματα.
Παράδειγμα: Αφαίρεση αριθμητικών χαρακτήρων και εξαγωγή όλων των αλφαβήτων και μη αριθμητικών χαρακτήρων.
Εδώ λοιπόν έχουμε κάποιο αλφαριθμητικό κείμενο. Αυτό το κείμενο περιέχει μερικούς αριθμούς και μερικούς μη αριθμητικούς χαρακτήρες. Πρέπει να απαλλαγούμε από αριθμητικούς χαρακτήρες και να λάβω αλφάβητα και άλλες τιμές χαρακτήρων μόνο, στη στήλη D.
Δεν αναμένω ότι ο συνολικός αριθμός χαρακτήρων στο μπερδεμένο κείμενο θα είναι πάνω από 100. Επομένως, η τιμή του NumChars είναι 100 εδώ. Μπορείτε να αυξήσετε ή να μειώσετε αυτόν τον αριθμό εάν χρειαστεί.
Εφαρμόστε τον παραπάνω γενικό τύπο εδώ για να διαγράψετε τους αριθμητικούς χαρακτήρες.
| =TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ(ISERROR(ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1)+0),ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1),""))) |
Όταν πατήσετε το κουμπί εισαγωγής, αφαιρείτε όλους τους αριθμητικούς χαρακτήρες και απομένουν μόνο αλφαβητικές τιμές. Σύρετε προς τα κάτω αυτόν τον τύπο για να αφαιρέσετε αριθμούς από συμβολοσειρά από όλα τα κελιά της στήλης Β.
Πώς λειτουργεί;
Αρχικά ας δούμε πώς λύνεται αυτός ο τύπος βήμα προς βήμα.
| 1-> TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ(ISERROR(ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1)+0),ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1),""))) |
| 2-> TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ(ISERROR(ΣΤΑ ΜΕΣΑ("Αυτός είναι 1 … ιστότοπος",{1,2,3,… 100},1)+0),ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1),""))) |
| 3-> TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ(ISERROR({"T"; "h"; "i"; "s"; "", "1"… ""; ""}+0),ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1),""))) |
| 4-> TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ(ISERROR({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}),ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1),""))) |
| 5-> TEXTJOIN("",ΑΛΗΘΗΣ,ΑΝ({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1),""))) |
| 6-> TEXTJOIN("",ΑΛΗΘΗΣ,{"Αυτό";""… .;""}) |
| 7-> "Αυτός είναι ο νούμερο ένα ιστότοπος" |
Για ευκολία στην ανάγνωση δεν έχω γράψει ολόκληρη τη σειρά. Χρησιμοποίησα τελείες (…) για να υποδείξω μεγάλους πίνακες.
Όπως μπορείτε να δείτε, ο τύπος αρχίζει να λύνει από μέσα. Αρχικά λύνεται η συνάρτηση SEQUENCE. Δεδομένου ότι έχουμε περάσει το 100 ως τον αριθμό των χαρακτήρων. Επιστρέφει μια σειρά αριθμών που ξεκινούν από 1 έως 100.
Αυτός ο πίνακας εμφανίζεται στη λειτουργία MID ως ο αριθμός εκκίνησης. Η συνάρτηση mid πηγαίνει σε κάθε ευρετήριο σε συμβολοσειρά και χωρίζει κάθε χαρακτήρα σε έναν πίνακα. Μπορείτε να το δείτε στο βήμα 3. Δεν έχω δείξει ολόκληρο τον πίνακα καθώς θα πάρει πολύ χώρο. {"T"; "h"; "i"; "s"; "", "1"… ""; ""}
Στη συνέχεια προσθέτουμε 0 σε κάθε χαρακτήρα. Στο Excel, αν προσπαθήσετε να προσθέσετε αριθμό σε μη αριθμητικούς χαρακτήρες, προκύπτει #VALUE! Λάθος. Παίρνουμε λοιπόν μια σειρά από αριθμούς και #ΑΞΙΑ! Σφάλματα. {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}
Αυτός ο πίνακας εξυπηρετείται στο ISERROR λειτουργία. Όπως γνωρίζετε, η συνάρτηση ISERROR επιστρέφει TRUE για σφάλματα και FALSE για τιμές μη σφάλματος. Ως εκ τούτου, έχουμε μια σειρά από ΑΛΗΘΙΝΟ και ΛΑΘΟΣ. TRUE για μη αριθμητικούς χαρακτήρες και FALSE των αριθμών. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Το ίδιο συμβαίνει με τις αληθινές δηλώσεις ενότητας του IF (ΣΤΑ ΜΕΣΑ(Β3,ΑΛΛΗΛΟΥΧΙΑ(100),1)). Επιστρέφει μια σειρά από όλους τους χαρακτήρες αλφαριθμητικής συμβολοσειράς στο Β3.
Τώρα για κάθε TRUE πρόταση, η συνάρτηση IF επιστρέφει τον αντίστοιχο χαρακτήρα από τον πίνακα της αληθινής ενότητας. Για FALSE, IF επιστρέφει κενό (""). Τώρα θα έχετε έναν πίνακα που δεν περιέχει αριθμητικό χαρακτήρα. {"Αυτό";""… .;""}.
Τέλος, αυτός ο πίνακας εξυπηρετείται από το TEXTJOIN λειτουργία. Αυτή η συνάρτηση ενώνει δεδομένα κείμενα μεταξύ τους, αγνοώντας τις κενές τιμές, με ένα δεδομένο οριοθέτη. Ως εκ τούτου, έχουμε μια συμβολοσειρά που δεν περιέχει αριθμητικούς χαρακτήρες. Αυτός είναι ο νούμερο ένα ιστότοπος.
Ενίσχυση της φόρμουλας
Ο παραπάνω τύπος χρησιμοποιεί έναν σκληρό κωδικοποιημένο αριθμό για την επεξεργασία του αριθμού των χαρακτήρων (πήραμε 100). Αλλά μπορεί να θέλετε να είναι δυναμικό. Σε αυτή την περίπτωση, το μαντέψατε σωστά, μπορείτε να χρησιμοποιήσετε τη λειτουργία LEN. Θα χρειαστεί ο ακριβής αριθμός χαρακτήρων για επεξεργασία. Ο τύπος λοιπόν θα είναι.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE)LEN(jumbled_text), 1) +0), MID (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
Εδώ, η συνάρτηση LEN θα εντοπίσει αυτόματα τους ακριβείς αριθμούς χαρακτήρων στην αλφαριθμητική συμβολοσειρά. Αυτό θα απαλλάξει από το βάρος του καθορισμού του μέγιστου αριθμού χαρακτήρων.
Εναλλακτική της συνάρτησης ΑΚΟΛΟΥΘΙΑ
Εάν δεν θέλετε να χρησιμοποιήσετε τη συνάρτηση ΑΚΟΛΟΥΘΙΑ τότε μπορείτε να χρησιμοποιήσετε έναν συνδυασμό συνάρτησης ROW και INDIRECT για τη δημιουργία διαδοχικών αριθμών.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text,ΣΕΙΡΑ(ΕΜΜΕΣΟΣ("1:"&LEN(NumChars))), 1) +0), MID (jumbled_text,ΣΕΙΡΑ(ΕΜΜΕΣΟΣ("1:"&LEN(NumChars))),1),""))) |
Το INDIRECT θα μετατρέψει το κείμενο ("1: 100") σε πραγματικό εύρος και στη συνέχεια η συνάρτηση ROW θα εμφανίσει όλους τους αριθμούς γραμμών από 1 έως 100. (Το 100 είναι απλώς ένα παράδειγμα. Μπορεί να είναι οποιοσδήποτε αριθμός).
Λοιπόν, ναι, έτσι μπορείτε να αντιγράψετε τους μη αριθμητικούς χαρακτήρες από μια αλφαριθμητική συμβολοσειρά στο excel. Ελπίζω να ήμουν αρκετά επεξηγηματικός και αυτό το άρθρο σας βοήθησε. Εάν έχετε οποιεσδήποτε ερωτήσεις σχετικά με αυτό το θέμα ή οποιοδήποτε άλλο θέμα excel/VBA. Μέχρι τότε συνεχίστε το Excelling.
Πώς να αφαιρέσετε μη αριθμητικούς χαρακτήρες από κελιά στο Excel 2019: Για να αφαιρέσουμε μη αριθμητικούς χαρακτήρες από μια αλφαριθμητική συμβολοσειρά στο Excel, χρησιμοποιούμε τη νέα συνάρτηση TEXTJOIN. Η λωρίδα μη αριθμητικών χαρακτήρων από μια συμβολοσειρά μπορεί να μας βοηθήσει να καθαρίσουμε τα δεδομένα μας για καλύτερη ανάλυση δεδομένων. Ιδού λοιπόν πώς το κάνετε
Διαχωρισμός αριθμών και κειμένου από συμβολοσειρά στο Excel 2016 και παλαιότερα: Όταν δεν είχαμε συνάρτηση TEXTJOIN, χρησιμοποιήσαμε τις ΑΡΙΣΤΕΡΕΣ και ΔΕΞΙΕΣ συναρτήσεις με κάποιες άλλες συναρτήσεις για να χωρίσουμε αριθμητικούς και μη αριθμητικούς χαρακτήρες από μια συμβολοσειρά.
Εξαγωγή κειμένου από μια συμβολοσειρά στο Excel χρησιμοποιώντας την αριστερή και δεξιά συνάρτηση του Excel: Για να αφαιρέσετε κείμενο στο Excel από συμβολοσειρά, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση ΑΡΙΣΤΕΡΑ και ΔΕΞΙΑ του excel. Αυτές οι λειτουργίες μας βοηθούν να κόψουμε δυναμικά τις συμβολοσειρές. \
Αφαιρέστε τα κενά και τα κενά από το κείμενο στο Excel: Οι κεντρικοί και οι πίσω χώροι είναι δύσκολο να αναγνωριστούν οπτικά και μπορούν να καταστρέψουν τα δεδομένα σας. Η απομάκρυνση αυτών των χαρακτήρων από τη συμβολοσειρά είναι μια βασική και πιο σημαντική εργασία στον καθαρισμό δεδομένων. Δείτε πώς μπορείτε να το κάνετε εύκολα στο Excel.
Αφαίρεση χαρακτήρων από δεξιά: Για να αφαιρέσετε χαρακτήρες από τα δεξιά μιας συμβολοσειράς στο Excel, χρησιμοποιούμε τη συνάρτηση ΑΡΙΣΤΕΡΑ. Ναι, η λειτουργία ΑΡΙΣΤΕΡΑ. Η συνάρτηση LEFT διατηρεί τον δεδομένο αριθμό χαρακτήρων από το LEFT και αφαιρεί τα πάντα από τα δεξιά της.
Κατάργηση ανεπιθύμητων χαρακτήρων στο Excel: Για να αφαιρέσετε ανεπιθύμητους χαρακτήρες από μια συμβολοσειρά στο Excel, χρησιμοποιούμε τη συνάρτηση SUBSTITUTE. Η συνάρτηση SUBSTITUTE αντικαθιστά τους δεδομένους χαρακτήρες με έναν άλλο δεδομένο χαρακτήρα και παράγει μια νέα τροποποιημένη συμβολοσειρά.
Πώς να αφαιρέσετε κείμενο στο Excel ξεκινώντας από μια θέση στο Excel: Για να αφαιρέσουμε κείμενο από μια αρχική θέση σε μια συμβολοσειρά, χρησιμοποιούμε τη συνάρτηση ΑΝΤΙΚΑΤΑΣΤΑΣΗ του Excel. Αυτή η συνάρτηση μας βοηθά να καθορίσουμε την αρχική θέση και τον αριθμό των χαρακτήρων για απογύμνωση.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση Excel VLOOKUP| Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε το Excel Λειτουργία COUNTIF| Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.