Όταν εργάζεστε με δεδομένα κειμένου στο Excel, μπορεί να χρειαστεί να αφαιρέσετε το κείμενο πριν ή μετά από έναν συγκεκριμένο χαρακτήρα ή συμβολοσειρά κειμένου.
Για παράδειγμα, εάν έχετε το όνομα και τα δεδομένα ονομασίας ατόμων, μπορεί να θέλετε να καταργήσετε την ονομασία μετά το κόμμα και να διατηρήσετε μόνο το όνομα (ή αντίστροφα όπου διατηρείτε τον προσδιορισμό και αφαιρείτε το όνομα).
Μερικές φορές, μπορεί να γίνει με έναν απλό τύπο ή μια γρήγορη εύρεση και αντικατάσταση, και μερικές φορές, χρειάζεται πιο σύνθετους τύπους ή λύσεις.
Σε αυτό το σεμινάριο, θα σας δείξω πώς να αφαιρέσετε το κείμενο πριν ή μετά από έναν συγκεκριμένο χαρακτήρα στο Excel (χρησιμοποιώντας διαφορετικά παραδείγματα).
Ας ξεκινήσουμε λοιπόν με μερικά απλά παραδείγματα.
Κατάργηση κειμένου μετά από χαρακτήρα χρησιμοποιώντας Εύρεση και αντικατάσταση
Εάν θέλετε να καταργήσετε γρήγορα όλο το κείμενο μετά από μια συγκεκριμένη συμβολοσειρά κειμένου (ή πριν από μια συμβολοσειρά κειμένου), μπορείτε να το κάνετε χρησιμοποιώντας Εύρεση και αντικατάσταση και χαρακτήρες μπαλαντέρ.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να καταργήσετε την ονομασία μετά τον χαρακτήρα κόμμα και να διατηρήσετε το κείμενο πριν από το κόμμα.
Παρακάτω είναι τα βήματα για να το κάνετε αυτό:
- Αντιγράψτε και επικολλήστε τα δεδομένα από τη στήλη Α στη στήλη Β (για να διατηρηθούν και τα αρχικά δεδομένα)
- Με επιλεγμένα τα κελιά στη στήλη Β, κάντε κλικ στην καρτέλα Αρχική σελίδα
- Στην ομάδα Επεξεργασία, κάντε κλικ στην επιλογή Εύρεση & επιλογή
- Στις επιλογές που εμφανίζονται στο αναπτυσσόμενο μενού, κάντε κλικ στην επιλογή Αντικατάσταση. Αυτό θα ανοίξει το παράθυρο διαλόγου Εύρεση και αντικατάσταση
- Στο πεδίο «Εύρεση τι», πληκτρολογήστε ,* (δηλ. κόμμα ακολουθούμενο από αστερίσκο)
- Αφήστε κενό το πεδίο «Αντικατάσταση με»
- Κάντε κλικ στο κουμπί Αντικατάσταση όλων
Τα παραπάνω βήματα θα βρουν το κόμμα στο σύνολο δεδομένων και θα αφαιρέσουν όλο το κείμενο μετά το κόμμα (συμπεριλαμβανομένου του κόμματος).
Δεδομένου ότι αυτό αντικαθιστά το κείμενο από τα επιλεγμένα κελιά, είναι καλή ιδέα να αντιγράψετε το κείμενο σε άλλη στήλη και στη συνέχεια να εκτελέσετε αυτήν τη λειτουργία εύρεσης και αντικατάστασης ή να δημιουργήσετε ένα αντίγραφο ασφαλείας των δεδομένων σας, ώστε να έχετε τα αρχικά δεδομένα ανέπαφα
Πως λειτουργεί αυτό?
* (σύμβολο αστερίσκου) είναι ένας χαρακτήρας μπαλαντέρ που μπορεί να αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων.
Όταν το χρησιμοποιώ μετά το κόμμα (στο πεδίο «Εύρεση τι») και μετά κάνω κλικ στο κουμπί Αντικατάσταση όλων, βρίσκει το πρώτο κόμμα στο κελί και το θεωρεί αντιστοίχιση.
Αυτό συμβαίνει επειδή το σύμβολο αστερίσκο (*) θεωρείται αντιστοίχιση ολόκληρης της συμβολοσειράς κειμένου που ακολουθεί το κόμμα.
Έτσι, όταν πατάτε το κουμπί αντικατάστασης όλων, αντικαθιστά το κόμμα και όλο το κείμενο που ακολουθεί.
Σημείωση: Αυτή η μέθοδος λειτουργεί καλά τότε έχετε μόνο ένα κόμμα σε κάθε κελί (όπως στο παράδειγμά μας). Σε περίπτωση που έχετε πολλά κόμματα, αυτή η μέθοδος θα έβρισκε πάντα το πρώτο κόμμα και στη συνέχεια θα αφαιρούσε τα πάντα μετά από αυτό. Επομένως, δεν μπορείτε να χρησιμοποιήσετε αυτήν τη μέθοδο εάν θέλετε να αντικαταστήσετε όλο το κείμενο μετά το δεύτερο κόμμα και να διατηρήσετε το πρώτο ως έχει.Σε περίπτωση που θέλετε να καταργήσετε όλους τους χαρακτήρες πριν από το κόμμα, αλλάξτε την καταχώριση εύρεσης τι πεδίου έχοντας το σύμβολο αστερίσκο πριν από το κόμμα (*, αντί για,*)
Κατάργηση κειμένου χρησιμοποιώντας τύπους
Εάν χρειάζεστε περισσότερο έλεγχο σχετικά με τον τρόπο εύρεσης και αντικατάστασης κειμένου πριν ή μετά από έναν συγκεκριμένο χαρακτήρα, είναι προτιμότερο να χρησιμοποιήσετε τους ενσωματωμένους τύπους κειμένου στο Excel.
Ας υποθέσουμε ότι έχετε το παρακάτω σύνολο δεδομένων Πού θέλετε να αφαιρέσετε όλο το κείμενο μετά το κόμμα.
Παρακάτω είναι ο τύπος για να το κάνετε αυτό:
= ΑΡΙΣΤΕΡΑ (A2, FIND (",", A2) -1)
Ο παραπάνω τύπος χρησιμοποιεί τη συνάρτηση FIND για να βρει τη θέση του κόμματος στο κελί.
Αυτός ο αριθμός θέσης χρησιμοποιείται στη συνέχεια από τη λειτουργία ΑΡΙΣΤΕΡΑ για την εξαγωγή όλων των χαρακτήρων πριν από το κόμμα. Δεδομένου ότι δεν θέλω κόμμα ως μέρος του αποτελέσματος, έχω αφαιρέσει το 1 από την τιμή που προκύπτει από τον τύπο Εύρεση.
Αυτό ήταν ένα απλό σενάριο.
Ας πάρουμε ένα λίγο περίπλοκο.
Ας υποθέσουμε ότι έχω αυτό το παρακάτω σύνολο δεδομένων όπου θέλω να αφαιρέσω όλο το κείμενο μετά το δεύτερο κόμμα.
Εδώ είναι ο τύπος που θα το κάνει αυτό:
= ΑΡΙΣΤΕΡΑ (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Δεδομένου ότι αυτό το σύνολο δεδομένων έχει πολλά κόμματα, δεν μπορώ να χρησιμοποιήσω τη συνάρτηση FIND για να πάρω τη θέση του πρώτου κόμματος και να εξαγάγω τα πάντα στα αριστερά του.
Πρέπει με κάποιο τρόπο να μάθω τη θέση του δεύτερου κόμματος και στη συνέχεια να εξάγω ό, τι βρίσκεται στα αριστερά του δεύτερου κόμματος.
Για να το κάνω αυτό, έχω χρησιμοποιήσει τη συνάρτηση SUBSTITUTE για να αλλάξω το δεύτερο κόμμα σε θαυμαστικό. Τώρα αυτό μου δίνει έναν μοναδικό χαρακτήρα στο κελί. Τώρα μπορώ να χρησιμοποιήσω τη θέση του θαυμαστικού για να εξαγάγω τα πάντα στα αριστερά του δεύτερου κόμματος.
Αυτή η θέση του θαυμαστικού χρησιμοποιείται στη λειτουργία ΑΡΙΣΤΕΡΑ για να εξαγάγει τα πάντα πριν από το δεύτερο κόμμα.
Μέχρι εδώ καλά!
Τι γίνεται όμως αν υπάρχει ασυνεπής αριθμός κόμματος στο σύνολο δεδομένων.
Για παράδειγμα, στο παρακάτω σύνολο δεδομένων ορισμένα κελιά έχουν δύο κόμματα και μερικά κελιά έχουν τρία κόμματα και πρέπει να εξαγάγω όλο το κείμενο πριν από το τελευταίο κόμμα.
Σε αυτή την περίπτωση, πρέπει με κάποιο τρόπο να καταλάβω τη θέση της τελευταίας εμφάνισης του κόμματος και στη συνέχεια να εξάγω τα πάντα στα αριστερά του.
Παρακάτω είναι ο τύπος που θα το κάνει αυτό
= ΑΡΙΣΤΕΡΑ (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Ο παραπάνω τύπος χρησιμοποιεί τη συνάρτηση LEN για να βρει το συνολικό μήκος του κειμένου στο κελί καθώς και το μήκος του κειμένου χωρίς κόμμα.
Όταν αφαιρώ αυτές τις δύο τιμές, μου δίνει τον συνολικό αριθμό κόμματος στο κελί.
Έτσι, θα μου έδινε 3 για το κελί Α2 και 2 για το κελί Α4.
Αυτή η τιμή χρησιμοποιείται στη συνέχεια στον τύπο SUBSTITUTE για να αντικαταστήσει το τελευταίο κόμμα με ένα θαυμαστικό. Στη συνέχεια, μπορείτε να χρησιμοποιήσετε την αριστερή συνάρτηση εξαγωγή όλων όσων βρίσκονται στα αριστερά του θαυμαστικού (που ήταν εκεί που ήταν το τελευταίο κόμμα)
Όπως μπορείτε να δείτε στα παραδείγματα, η χρήση ενός συνδυασμού τύπων κειμένου σας επιτρέπει να χειριστείτε πολλές διαφορετικές καταστάσεις.
Επίσης, δεδομένου ότι το αποτέλεσμα συνδέεται με τα αρχικά δεδομένα, όταν αλλάζετε τα αρχικά δεδομένα, το αποτέλεσμα θα ενημερώνεται αυτόματα.
Κατάργηση κειμένου χρησιμοποιώντας Flash Fill
Το Flash Fill είναι ένα εργαλείο που εισήχθη στο Excel 2013 και είναι διαθέσιμο σε όλες τις εκδόσεις μετά από αυτό.
Λειτουργεί με τον προσδιορισμό μοτίβων όταν εισάγετε μη αυτόματα τα δεδομένα και, στη συνέχεια, παρέχεται για να σας δώσει τα δεδομένα για ολόκληρη τη στήλη.
Έτσι, εάν θέλετε να αφαιρέσετε το κείμενο πριν ή μετά από έναν συγκεκριμένο χαρακτήρα, απλά πρέπει να δείξετε τη νεράιδα flash πώς θα ήταν το αποτέλεσμα (με την εισαγωγή του χειροκίνητα μερικές φορές) και το φλας γεμίζει αυτόματα το μοτίβο και σας δίνει τα αποτελέσματα.
Επιτρέψτε μου να σας το δείξω με ένα παράδειγμα.
Παρακάτω έχω το σύνολο δεδομένων όπου θέλω να αφαιρέσω όλο το κείμενο μετά το κόμμα.
Ακολουθούν τα βήματα για να το κάνετε αυτό χρησιμοποιώντας το Flash Fill:
- Στο κελί Β2, που είναι η παρακείμενη στήλη των δεδομένων μας, πληκτρολογήστε χειροκίνητα το «Jeffery Haggins» (που είναι το αναμενόμενο αποτέλεσμα)
- Στο κελί Β3, εισαγάγετε το «Tim Scott» (το οποίο είναι το αναμενόμενο αποτέλεσμα για το δεύτερο κελί)
- Επιλέξτε το εύρος B2: B10
- Κάντε κλικ στην καρτέλα Αρχική σελίδα
- Στην ομάδα Επεξεργασία, κάντε κλικ στην αναπτυσσόμενη επιλογή Συμπλήρωση
- Κάντε κλικ στο Flash Fill
Τα παραπάνω βήματα θα σας δώσουν το αποτέλεσμα όπως φαίνεται παρακάτω:
Μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου Flash Fill Control + E μετά την επιλογή των κελιών στη στήλη αποτελεσμάτων (Στήλη Β στο παράδειγμά μας)
Το Flash Fill είναι ένα υπέροχο εργαλείο και στις περισσότερες περιπτώσεις, είναι σε θέση να αναγνωρίσει το μοτίβο και να σας δώσει το σωστό αποτέλεσμα. αλλά σε ορισμένες περιπτώσεις, μπορεί να μην είναι σε θέση να αναγνωρίσει σωστά το μοτίβο και μπορεί να σας δώσει λάθος αποτελέσματα.
Επομένως, βεβαιωθείτε ότι έχετε ελέγξει ξανά τα αποτελέσματα του Flash Fill
Και όπως έχουμε αφαιρέσει όλο το κείμενο μετά από ένα συγκεκριμένο χαρακτήρα χρησιμοποιώντας flash Fill, μπορείτε να χρησιμοποιήσετε τα ίδια βήματα για να αφαιρέσετε το κείμενο πριν από έναν συγκεκριμένο χαρακτήρα. απλώς εμφανίστε flash fill πώς θα μοιάζει το αποτέλεσμα με το Διαδίκτυο με το χέρι στη διπλανή στήλη και θα έκανε τα υπόλοιπα.
Κατάργηση κειμένου χρησιμοποιώντας VBA (Προσαρμοσμένη λειτουργία)
Η όλη έννοια της αφαίρεσης του κειμένου πριν ή μετά από έναν συγκεκριμένο χαρακτήρα εξαρτάται από την εύρεση της θέσης αυτού του χαρακτήρα.
Όπως φαίνεται παραπάνω, η εύρεση της τελευταίας εμφάνισης αυτού του χαρακτήρα είναι καλή χρήση μιας σύνθεσης τύπων.
Εάν αυτό είναι κάτι που πρέπει να κάνετε αρκετά συχνά, μπορείτε να απλοποιήσετε αυτήν τη διαδικασία δημιουργώντας μια προσαρμοσμένη λειτουργία χρησιμοποιώντας το VBA (που ονομάζεται Λειτουργίες καθορισμένες από το χρήστη).
Μόλις δημιουργηθεί, μπορείτε να χρησιμοποιήσετε ξανά αυτήν τη λειτουργία ξανά και ξανά. Είναι επίσης πολύ απλούστερο και ευκολότερο στη χρήση (καθώς το μεγαλύτερο μέρος της άρσης βαρών γίνεται με τον κωδικό VBA στο πίσω άκρο).
Κάτω από τον κώδικα VBA που μπορείτε να χρησιμοποιήσετε για να δημιουργήσετε την προσαρμοσμένη συνάρτηση στο Excel:
Λειτουργία LastPosition (rCell As Range, rChar As String) 'Αυτή η λειτουργία δίνει την τελευταία θέση του καθορισμένου χαρακτήρα' Αυτός ο κωδικός έχει αναπτυχθεί από το Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Για i = rLen Σε 1 Βήμα -1 Εάν Mid (rCell, i - 1, 1) = rChar Τότε LastPosition = i - 1 Έξοδος από τη λειτουργία
Πρέπει να τοποθετήσετε τον κώδικα VBA σε μια κανονική μονάδα στο VB Editor ή στο Personal Macro Workbook. Μόλις το έχετε εκεί, μπορείτε να το χρησιμοποιήσετε ως οποιαδήποτε άλλη κανονική συνάρτηση φύλλου εργασίας στο βιβλίο εργασίας.
Αυτή η συνάρτηση παίρνει 2 ορίσματα:
- Η αναφορά κελιού για την οποία θέλετε να βρείτε την τελευταία εμφάνιση συγκεκριμένου χαρακτήρα ή συμβολοσειράς κειμένου
- Ο χαρακτήρας ή η συμβολοσειρά κειμένου τη θέση της οποίας πρέπει να βρείτε
Ας υποθέσουμε ότι έχετε τώρα το παρακάτω σύνολο δεδομένων και θέλετε να καταργήσετε όλο το κείμενο μετά το τελευταίο κόμμα και να έχετε μόνο το κείμενο πριν από το τελευταίο κόμμα.
Παρακάτω είναι ο τύπος που θα το κάνει:
= ΑΡΙΣΤΕΡΑ (A2, LastPosition (A2, ",")-1)
Στον παραπάνω τύπο, έχω καθορίσει να βρω τη θέση του τελευταίου κόμματος. Σε περίπτωση που θέλετε να βρείτε τη θέση κάποιου άλλου χαρακτήρα ή συμβολοσειράς κειμένου, θα πρέπει να το χρησιμοποιήσετε ως δεύτερο όρισμα στη συνάρτηση.
Όπως μπορείτε να δείτε, αυτό είναι πολύ πιο σύντομο και πιο εύκολο στη χρήση, σε σύγκριση με τον τύπο μεγάλου κειμένου που χρησιμοποιήσαμε την προηγούμενη ενότητα
Εάν τοποθετήσετε τον κώδικα VBA σε μια λειτουργική μονάδα σε ένα βιβλίο εργασίας, θα μπορείτε να χρησιμοποιήσετε αυτήν την προσαρμοσμένη λειτουργία μόνο στο συγκεκριμένο βιβλίο εργασίας. Εάν θέλετε να το χρησιμοποιήσετε σε όλα τα βιβλία εργασίας στο σύστημά σας, πρέπει να αντιγράψετε και να επικολλήσετε αυτόν τον κώδικα στο Personal Macro Workbook.
Αυτά είναι μερικά από τα μηνύματα που μπορείτε να χρησιμοποιήσετε για να αφαιρέσετε γρήγορα το κείμενο πριν ή μετά από έναν συγκεκριμένο χαρακτήρα στο Excel.
Εάν πρόκειται για ένα απλό πράγμα μιας φοράς, τότε μπορείτε να το κάνετε χρησιμοποιώντας εύρεση και αντικατάσταση. τι γίνεται αν είναι λίγο πιο περίπλοκο, τότε πρέπει να χρησιμοποιήσετε έναν συνδυασμό ενσωματωμένων τύπων Excel ή ακόμα και να δημιουργήσετε τον δικό σας προσαρμοσμένο τύπο χρησιμοποιώντας VBA.
Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο.
