Έχουμε μάθει πώς να αφαιρούμε αριθμητικές τιμές από ένα κελί στο Excel 2016 και παλαιότερα. Οι τύποι που χρησιμοποιήσαμε ήταν λίγο περίπλοκοι, αλλά τώρα το Excel 2019 και το 365 είναι στο παιχνίδι.
Το Excel 2019 και το 365 εισάγουν μερικές νέες συναρτήσεις που διευκολύνουν την εργασία κατάργησης μη αριθμητικών χαρακτήρων και ανάκτηση μόνο αριθμητικών τιμών σε ένα νέο κελί. Θα χρησιμοποιήσουμε τύπους που μπορούν να μας βοηθήσουν σε αυτό, πιο βολικά.
Γενικός τύπος
| =TEXTJOIN("",ΑΛΗΘΗΣ,IFERROR(ΣΤΑ ΜΕΣΑ(jumbled_text,ΑΛΛΗΛΟΥΧΙΑ(NumChars), 1) +0, "")) |
Jumbled_text: Αυτό είναι το κείμενο προέλευσης από το οποίο θέλετε να εξαγάγετε όλες τις αριθμητικές τιμές.
NumChars: Αυτός είναι ο συνολικός αριθμός χαρακτήρων που θέλετε να επεξεργαστείτε. Το jumbled_text δεν πρέπει να έχει περισσότερους χαρακτήρες από αυτόν τον αριθμό (συνδυασμοί χαρακτήρων και αριθμών).
Ας δούμε ένα παράδειγμα για να ξεκαθαρίσουμε τα πράγματα.
Παράδειγμα: Αφαίρεση μη αριθμητικών χαρακτήρων και εξαγωγή όλων των αριθμών
Εδώ λοιπόν έχουμε ένα μπερδεμένο κείμενο. Αυτό το κείμενο περιέχει μερικούς αριθμούς και μερικούς μη αριθμητικούς χαρακτήρες. Πρέπει να απαλλαγώ από μη αριθμητικούς χαρακτήρες και να λάβω αριθμητικές τιμές μόνο στη στήλη D.
Δεν αναμένω ότι ο συνολικός αριθμός χαρακτήρων στο μπερδεμένο κείμενο θα είναι πάνω από 20. Επομένως, η αξία του NumChars είναι 20 εδώ. Μπορείτε να αυξήσετε αυτόν τον αριθμό αν χρειαστεί.
Εφαρμόστε τον παραπάνω γενικό τύπο εδώ για να διαγράψετε τους μη αριθμητικούς χαρακτήρες.
| =TEXTJOIN("",ΑΛΗΘΗΣ,IFERROR(ΣΤΑ ΜΕΣΑ(C3,ΑΛΛΗΛΟΥΧΙΑ(20),1)+0,"")) |
Και όταν πατήσετε το κουμπί εισαγωγής. Καταργείτε όλους τους μη αριθμητικούς χαρακτήρες. Σύρετε προς τα κάτω αυτόν τον τύπο για να αφαιρέσετε χαρακτήρες από συμβολοσειρά από όλα τα κελιά της στήλης C3.
Πώς λειτουργεί;
Αρχικά ας δούμε πώς λύνεται αυτός ο τύπος βήμα προς βήμα.
| 1-> TEXTJOIN("",ΑΛΗΘΗΣ,IFERROR(ΣΤΑ ΜΕΣΑ(C3,ΑΛΛΗΛΟΥΧΙΑ(20),1)+0,"")) |
| 2-> TEXTJOIN("",ΑΛΗΘΗΣ,IFERROR(ΣΤΑ ΜΕΣΑ("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",ΑΛΗΘΗΣ,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; "" ; ""; ""; "" "" "" "" ";" ";" "}}+0,"")) |
| 4-> TEXTJOIN("",ΑΛΗΘΗΣ,IFERROR({1; 2;#VALUE!;#VALUE!;#VALUE!; 1; 2; #VALUE!; 1; 2; 3;#VALUE!;#VALUE!;…;#VALUE!}+0,"")) |
| 5-> TEXTJOIN("",ΑΛΗΘΗΣ,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Όπως μπορείτε να δείτε, ο τύπος αρχίζει να λύνει από μέσα. Αρχικά λύνεται η συνάρτηση SEQUENCE. Αφού έχουμε περάσει το 20. Επιστρέφει μια σειρά αριθμών που ξεκινούν από το 1 έως το 20.
Αυτός ο πίνακας εμφανίζεται στη λειτουργία MID ως ο αριθμός εκκίνησης. Η συνάρτηση mid πηγαίνει σε κάθε ευρετήριο σε συμβολοσειρά και χωρίζει κάθε χαρακτήρα. Μπορείτε να το δείτε στο βήμα 3.
Στη συνέχεια προσθέτουμε 0 σε κάθε χαρακτήρα. Στο Excel, αν προσπαθήσετε να προσθέσετε αριθμό σε μη αριθμητικούς χαρακτήρες, προκύπτει #VALUE! Λάθος. Παίρνουμε λοιπόν μια σειρά από αριθμούς και #ΑΞΙΑ! Σφάλματα. Οι μη αριθμητικοί χαρακτήρες έχουν φύγει τώρα.
Η επόμενη συνάρτηση IFERROR αντικαθιστά όλα τα σφάλματα #VALUE με "" (κενό). Τώρα μένουμε με αριθμητικές τιμές και κενά.
Τέλος, αυτός ο πίνακας εξυπηρετείται στη συνάρτηση TEXTJOIN. Η συνάρτηση TEXTJOIN τα συνδέει και παίρνουμε τη συμβολοσειρά που περιέχει μόνο αριθμούς σε αυτήν.
Ενίσχυση της φόρμουλας
Ο παραπάνω τύπος χρησιμοποίησε έναν σκληρό κωδικοποιημένο αριθμό για την επεξεργασία του αριθμού των χαρακτήρων (πήραμε είκοσι). Αλλά μπορεί να θέλετε να είναι δυναμικό. Σε αυτή την περίπτωση, το μαντέψατε σωστά, θα χρησιμοποιήσουμε τη συνάρτηση LEN. Θα χρειαστεί ένας ακριβής αριθμός χαρακτήρων για επεξεργασία. Ο τύπος λοιπόν θα είναι.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Εδώ, η συνάρτηση LEN θα εντοπίσει αυτόματα τους ακριβείς αριθμούς χαρακτήρων στην αλφαριθμητική συμβολοσειρά. Αυτό θα απαλλάξει το βάρος του καθορισμού του μέγιστου αριθμού χαρακτήρων.
Εναλλακτική της συνάρτησης ΑΚΟΛΟΥΘΙΑ
Εάν δεν θέλετε να χρησιμοποιήσετε τη συνάρτηση ΑΚΟΛΟΥΘΙΑ τότε μπορείτε να χρησιμοποιήσετε έναν συνδυασμό συνάρτησης ROW και INDIRECT για τη δημιουργία διαδοχικών αριθμών.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,ΣΕΙΡΑ(ΕΜΜΕΣΟΣ("1:"&LEN(jumbled_text))),1)+0,"")) |
Το INDIRECT θα μετατρέψει το κείμενο ("1:20") σε πραγματικό εύρος και στη συνέχεια η συνάρτηση ROW θα απαριθμήσει όλους τους αριθμούς γραμμών από 1 έως 20. (το 20 είναι μόνο για παράδειγμα. Μπορεί να είναι οποιοσδήποτε αριθμός).
Λοιπόν, ναι, έτσι μπορείτε να αντιγράψετε τους μη αριθμητικούς χαρακτήρες από μια αλφαριθμητική συμβολοσειρά στο excel. Ελπίζω να ήμουν αρκετά επεξηγηματικός και αυτό το άρθρο σας βοήθησε. Εάν έχετε οποιεσδήποτε ερωτήσεις σχετικά με αυτό το θέμα ή οποιοδήποτε άλλο θέμα excel/VBA. Μέχρι τότε συνεχίστε το Excelling.
Διαχωρισμός αριθμών και κειμένου από συμβολοσειρά στο 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 | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.