Το Excel διαθέτει ένα σύνολο λειτουργιών TEXT που μπορούν να κάνουν θαύματα. Μπορείτε να κάνετε όλα τα είδη λειτουργιών αποκοπής κειμένου και ζαριών χρησιμοποιώντας αυτές τις λειτουργίες.
Μια από τις συνηθισμένες εργασίες για άτομα που εργάζονται με δεδομένα κειμένου είναι η εξαγωγή μιας υποσύμβασης στο Excel (δηλ. Λήψη psrt του κειμένου από ένα κελί).
Δυστυχώς, δεν υπάρχει καμία συνάρτηση υποσύμβασης στο Excel που να μπορεί να το κάνει εύκολα. Ωστόσο, αυτό θα μπορούσε ακόμα να γίνει χρησιμοποιώντας τύπους κειμένου καθώς και κάποιες άλλες ενσωματωμένες δυνατότητες του Excel.
Ας ρίξουμε πρώτα μια ματιά σε μερικές από τις λειτουργίες κειμένου που θα χρησιμοποιήσουμε σε αυτό το σεμινάριο.
Λειτουργίες ΚΕΙΜΕΝΟΥ Excel
Το Excel διαθέτει μια σειρά από λειτουργίες κειμένου που θα καθιστούσαν πολύ εύκολη την εξαγωγή μιας υποσύμβασης από το αρχικό κείμενο στο Excel. Ακολουθούν οι λειτουργίες κειμένου Excel που θα χρησιμοποιήσουμε σε αυτό το σεμινάριο:
- Λειτουργία RIGHT: Εξάγει τον καθορισμένο αριθμό χαρακτήρων από τα δεξιά της συμβολοσειράς κειμένου.
- Συνάρτηση ΑΡΙΣΤΕΡΑ: Εξάγει τον καθορισμένο αριθμό χαρακτήρων από τα αριστερά της συμβολοσειράς κειμένου.
- Λειτουργία MID: Εξάγει τον καθορισμένο αριθμό χαρακτήρων από την καθορισμένη αρχική θέση σε μια συμβολοσειρά κειμένου.
- Λειτουργία FIND: Βρίσκει την αρχική θέση του καθορισμένου κειμένου στη συμβολοσειρά κειμένου.
- Συνάρτηση LEN: Επιστρέφει τον αριθμό των χαρακτήρων στη συμβολοσειρά κειμένου.
Εξαγάγετε μια υποχορδή στο Excel χρησιμοποιώντας λειτουργίες
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:
Αυτά είναι μερικά τυχαία (αλλά υπερήρωα) αναγνωριστικά ηλεκτρονικού ταχυδρομείου (εκτός από το δικό μου) και στα παρακάτω παραδείγματα, θα σας δείξω πώς να εξαγάγετε το όνομα χρήστη και το όνομα τομέα χρησιμοποιώντας τις συναρτήσεις κειμένου στο Excel.
Παράδειγμα 1 - Εξαγωγή ονομάτων χρήστη από αναγνωριστικά email
Κατά τη χρήση συναρτήσεων κειμένου, είναι σημαντικό να προσδιορίσετε ένα μοτίβο (εάν υπάρχει). Αυτό καθιστά πολύ εύκολη την κατασκευή ενός τύπου. Στην παραπάνω περίπτωση, το μοτίβο είναι το σύμβολο @ μεταξύ του ονόματος χρήστη και του ονόματος τομέα και θα το χρησιμοποιήσουμε ως αναφορά για να λάβουμε τα ονόματα χρήστη.
Εδώ είναι ο τύπος για να λάβετε το όνομα χρήστη:
= ΑΡΙΣΤΕΡΑ (A2, FIND ("@", A2) -1)
Ο παραπάνω τύπος χρησιμοποιεί τη συνάρτηση LEFT για την εξαγωγή του ονόματος χρήστη προσδιορίζοντας τη θέση του σημείου @ στο αναγνωριστικό. Αυτό γίνεται χρησιμοποιώντας τη συνάρτηση FIND, η οποία επιστρέφει τη θέση του @.
Για παράδειγμα, στην περίπτωση του [email protected], το FIND ("@", A2) θα επέστρεφε 11, που είναι η θέση του στη συμβολοσειρά κειμένου.
Τώρα χρησιμοποιούμε τη συνάρτηση ΑΡΙΣΤΕΡΑ για να εξαγάγουμε 10 χαρακτήρες από τα αριστερά της συμβολοσειράς (μία λιγότερη από την τιμή που επιστρέφει η συνάρτηση ΑΡΙΣΤΕΡΑ).
Παράδειγμα 2 - Εξαγωγή του ονόματος τομέα από τα αναγνωριστικά ηλεκτρονικού ταχυδρομείου
Η ίδια λογική που χρησιμοποιείται στο παραπάνω παράδειγμα μπορεί να χρησιμοποιηθεί για να λάβετε το όνομα τομέα. Μια μικρή διαφορά εδώ είναι ότι πρέπει να εξάγουμε τους χαρακτήρες από τα δεξιά της συμβολοσειράς κειμένου.
Εδώ είναι ο τύπος που θα το κάνει αυτό:
= ΔΕΞΙΑ (A2, LEN (A2) -FIND ("@", A2))
Στον παραπάνω τύπο, χρησιμοποιούμε την ίδια λογική, αλλά την προσαρμόζουμε για να βεβαιωθούμε ότι λαμβάνουμε τη σωστή συμβολοσειρά.
Ας πάρουμε ξανά το παράδειγμα του [email protected]. Η συνάρτηση FIND επιστρέφει τη θέση του σημείου @, η οποία είναι 11 στην περίπτωση αυτή. Τώρα, πρέπει να εξάγουμε όλους τους χαρακτήρες μετά το @. Έτσι προσδιορίζουμε το συνολικό μήκος της συμβολοσειράς και αφαιρούμε τον αριθμό των χαρακτήρων μέχρι το @. Μας δίνει τον αριθμό των χαρακτήρων που καλύπτουν το όνομα τομέα στα δεξιά.
Τώρα μπορούμε απλά να χρησιμοποιήσουμε τη συνάρτηση ΔΕΞΙΑ για να λάβουμε το όνομα τομέα.
Παράδειγμα 3 - Εξαγωγή του ονόματος τομέα από τα αναγνωριστικά ηλεκτρονικού ταχυδρομείου (χωρίς .com)
Για να εξαγάγετε μια υπο -συμβολοσειρά από τη μέση μιας συμβολοσειράς κειμένου, πρέπει να προσδιορίσετε τη θέση του δείκτη ακριβώς πριν και μετά την υπο -συμβολοσειρά.
Για παράδειγμα, στο παρακάτω παράδειγμα, για να λάβετε το όνομα τομέα χωρίς το τμήμα .com, ο δείκτης θα είναι @ (που βρίσκεται ακριβώς πριν από το όνομα τομέα) και. (που είναι αμέσως μετά).
Ακολουθεί ο τύπος που θα εξαγάγει μόνο το όνομα τομέα:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Η λειτουργία MID MID εξαγάγει τον καθορισμένο αριθμό χαρακτήρων από την καθορισμένη αρχική θέση. Σε αυτό το παραπάνω παράδειγμα, το FIND ("@", A2) +1 καθορίζει την αρχική θέση (η οποία βρίσκεται αμέσως μετά το@) και το FIND (".", A2) -FIND ("@", A2) -1 προσδιορίζει το αριθμός χαρακτήρων μεταξύ του "@' και το '.‘
Εκσυγχρονίζω: Ένας από τους αναγνώστες William19 ανέφερε ότι ο παραπάνω τύπος δεν θα λειτουργούσε σε περίπτωση που υπάρχει μια τελεία (.) Στο αναγνωριστικό email (για παράδειγμα, [email protected]). Εδώ είναι ο τύπος για την αντιμετώπιση τέτοιων περιπτώσεων:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Χρήση κειμένου σε στήλες για εξαγωγή μιας υποχορδής στο Excel
Η χρήση συναρτήσεων για την εξαγωγή μιας υποσύμβασης στο Excel έχει το πλεονέκτημα ότι είναι δυναμική. Εάν αλλάξετε το αρχικό κείμενο, ο τύπος θα ενημερώσει αυτόματα τα αποτελέσματα.
Εάν αυτό είναι κάτι που μπορεί να μην χρειάζεστε, τότε η χρήση της δυνατότητας Κείμενο σε στήλες θα μπορούσε να είναι ένας γρήγορος και εύκολος τρόπος για να χωρίσετε το κείμενο σε υποσύμβολα με βάση συγκεκριμένους δείκτες.
Εδώ είναι πώς να το κάνετε αυτό:
- Επιλέξτε τα κελιά όπου έχετε το κείμενο.
- Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Κείμενο σε στήλες.
- Στον Οδηγό κειμένου σε στήλη Βήμα 1, επιλέξτε Οριοθετημένος και πατήστε Επόμενο.
- Στο βήμα 2, ελέγξτε την επιλογή Άλλα και εισαγάγετε @ στο πλαίσιο δεξιά σε αυτήν. Αυτό θα είναι το οριοθέτημά μας που θα χρησιμοποιούσε το Excel για να χωρίσει το κείμενο σε υποσύμβολα. Μπορείτε να δείτε την προεπισκόπηση δεδομένων παρακάτω. Κάντε κλικ στο Επόμενο.
- Στο βήμα 3, η γενική ρύθμιση λειτουργεί καλά σε αυτήν την περίπτωση. Μπορείτε, ωστόσο, να επιλέξετε μια διαφορετική μορφή εάν χωρίζετε αριθμούς/ημερομηνίες. Από προεπιλογή, το κελί προορισμού είναι εκεί που έχετε τα αρχικά δεδομένα. Εάν θέλετε να διατηρήσετε ανέπαφα τα αρχικά δεδομένα, αλλάξτε τα σε κάποιο άλλο κελί.
- Κάντε κλικ στο Τέλος.
Αυτό θα σας δώσει αμέσως δύο σύνολα υποστοιχείων για κάθε αναγνωριστικό ηλεκτρονικού ταχυδρομείου που χρησιμοποιείται σε αυτό το παράδειγμα.
Εάν θέλετε να διαιρέσετε περαιτέρω το κείμενο (για παράδειγμα, διαχωρίστε το batman.com σε batman και com), επαναλάβετε την ίδια διαδικασία με αυτό.
Χρήση FIND και REPLACE για εξαγωγή κειμένου από κελί στο Excel
FIND and REPLACE μπορεί να είναι μια ισχυρή τεχνική όταν εργάζεστε με κείμενο στο Excel. Στα παρακάτω παραδείγματα, θα μάθετε πώς να χρησιμοποιείτε το FIND και το REPLACE με χαρακτήρες μπαλαντέρ για να κάνετε εκπληκτικά πράγματα στο Excel.
Δείτε επίσης: Μάθετε τα πάντα για τους χαρακτήρες μπαλαντέρ στο Excel.
Ας πάρουμε τα ίδια παραδείγματα αναγνωριστικών ηλεκτρονικού ταχυδρομείου.
Παράδειγμα 1 - Εξαγωγή ονομάτων χρήστη από αναγνωριστικά email
Ακολουθούν τα βήματα για την εξαγωγή ονομάτων χρήστη από αναγνωριστικά email χρησιμοποιώντας τη λειτουργία Εύρεση και αντικατάσταση:
- Αντιγράψτε και επικολλήστε τα αρχικά δεδομένα. Δεδομένου ότι η Εύρεση και αντικατάσταση λειτουργεί και αλλάζει τα δεδομένα στα οποία εφαρμόζονται, είναι καλύτερο να έχετε αντίγραφο ασφαλείας των αρχικών δεδομένων.
- Επιλέξτε τα δεδομένα και μεταβείτε στην Αρχική σελίδα -> Επεξεργασία -> Εύρεση & επιλογή -> Αντικατάσταση (ή χρησιμοποιήστε τη συντόμευση πληκτρολογίου Ctrl + H).
- Στο παράθυρο διαλόγου Εύρεση και αντικατάσταση, πληκτρολογήστε τα εξής:
- Βρείτε τι: @*
- Αντικατάσταση με: (αφήστε αυτό το κενό)
- Κάντε κλικ στο Αντικατάσταση όλων.
Αυτό θα καταργήσει αμέσως όλο το κείμενο πριν από το @ στα αναγνωριστικά email. Θα έχετε το αποτέλεσμα όπως φαίνεται παρακάτω:
Πώς λειτουργεί αυτό ;; - Στο παραπάνω παράδειγμα, χρησιμοποιήσαμε συνδυασμό @ και *. Ο αστερίσκος (*) είναι ένας χαρακτήρας μπαλαντέρ που αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων. Ως εκ τούτου, @* θα σήμαινε, μια συμβολοσειρά κειμένου που ξεκινά με @ και μπορεί να έχει οποιονδήποτε αριθμό χαρακτήρων μετά από αυτήν. Για παράδειγμα, στο [email protected], το @* θα ήταν @batman.com. Όταν αντικαθιστούμε το @* με κενό, αφαιρεί όλους τους χαρακτήρες μετά το @(συμπεριλαμβανομένου του @).
Παράδειγμα 2 - Εξαγωγή του ονόματος τομέα από τα αναγνωριστικά ηλεκτρονικού ταχυδρομείου
Χρησιμοποιώντας την ίδια λογική, μπορείτε να τροποποιήσετε τα κριτήρια «Βρείτε τι» για να λάβετε το όνομα τομέα.
Εδώ είναι τα βήματα:
- Επιλέξτε τα δεδομένα.
- Μεταβείτε στην Αρχική σελίδα -> Επεξεργασία -> Εύρεση & επιλογή -> Αντικατάσταση (ή χρησιμοποιήστε τη συντόμευση πληκτρολογίου Ctrl + H).
- Στο παράθυρο διαλόγου Εύρεση και αντικατάσταση, πληκτρολογήστε τα ακόλουθα:
- Βρείτε τι: *@
- Αντικατάσταση με: (αφήστε αυτό το κενό)
- Κάντε κλικ στο Αντικατάσταση όλων.
Αυτό θα καταργήσει αμέσως όλο το κείμενο πριν από το @ στα αναγνωριστικά email. Θα έχετε το αποτέλεσμα όπως φαίνεται παρακάτω:
