Δεν υπάρχει ενσωματωμένη συνάρτηση στο Excel για την εξαγωγή αριθμών από μια συμβολοσειρά σε ένα κελί (και αντίστροφα - αφαιρέστε το αριθμητικό τμήμα και εξάγετε το τμήμα κειμένου από μια αλφαριθμητική συμβολοσειρά).
Ωστόσο, αυτό μπορεί να γίνει χρησιμοποιώντας μια συνάρτηση Excel ή κάποιο απλό κώδικα VBA.
Επιτρέψτε μου να σας πω πρώτα για τι πράγμα μιλάω.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως παρακάτω και θέλετε να εξαγάγετε αριθμούς από συμβολοσειρές (όπως παρακάτω):
Η μέθοδος που επιλέγετε εξαρτάται επίσης από την έκδοση του Excel που χρησιμοποιείτε:
- Για εκδόσεις του Excel πριν από το 2016, πρέπει να χρησιμοποιήσετε έναν ελαφρώς μεγαλύτερο τύπο
- Για το Excel 2016 μπορείτε να χρησιμοποιήσετε τη συνάρτηση TEXTJOIN που παρουσιάστηκε πρόσφατα
- Η μέθοδος VBA είναι διαθέσιμη για όλες τις εκδόσεις του Excel
Περιεχόμενα
Εξαγωγή αριθμών από συμβολοσειρές στο Excel (τύποι του Excel 2016)
Αυτός ο τύπος λειτουργεί μόνο στο Excel 2016 επειδή χρησιμοποιεί τη συνάρτηση TEXTJOIN που παρουσιάστηκε πρόσφατα.
Επιπλέον, αυτός ο τύπος μπορεί να εξάγει αριθμούς στην αρχή, στο τέλος ή στη μέση μιας συμβολοσειράς κειμένου.
Σημειώστε ότι ο τύπος TEXTJOIN που περιγράφεται σε αυτήν την ενότητα τοποθετεί όλους τους αριθμητικούς χαρακτήρες μαζί.Για παράδειγμα, εάν το κείμενο είναι "10 εισιτήρια είναι 200 $", το αποτέλεσμα είναι 10200.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως το παρακάτω και θέλετε να εξαγάγετε αριθμούς από συμβολοσειρές σε κάθε κελί:
Ο παρακάτω τύπος θα σας δώσει το αριθμητικό μέρος μιας συμβολοσειράς στο Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Αυτός είναι ένας τύπος πίνακα, επομένως πρέπει να χρησιμοποιήσετε " Control + Shift + Enter " αντί να χρησιμοποιήσετε το Enter.
Αυτός ο τύπος θα επιστρέψει κενός (κενή συμβολοσειρά) εάν δεν υπάρχουν αριθμοί στη συμβολοσειρά κειμένου.
Πώς λειτουργεί αυτή η φόρμουλα;
Επιτρέψτε μου να αναλύσω αυτόν τον τύπο και να προσπαθήσω να εξηγήσω πώς λειτουργεί:
- ROW(INDIRECT(“1:”&LEN(A2))) – Αυτό το μέρος του τύπου θα δώσει μια σειρά αριθμών που ξεκινούν από το ένα.Η συνάρτηση LEN στον τύπο επιστρέφει τον συνολικό αριθμό χαρακτήρων στη συμβολοσειρά.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - Αυτό το τμήμα του τύπου θα επιστρέψει έναν πίνακα #VALUEs!Σφάλματα ή αριθμοί βάσει συμβολοσειρών.Όλοι οι χαρακτήρες κειμένου στη συμβολοσειρά γίνονται #VALUE!Τα σφάλματα και όλες οι τιμές παραμένουν ως έχουν.Αυτό συμβαίνει όταν πολλαπλασιάζουμε τη συνάρτηση MID επί 1.
- IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)"") – Όταν χρησιμοποιείτε τη συνάρτηση IFERROR, όλες οι #ΤΙΜΕΣ καταργούνται!Σφάλμα, θα παραμείνουν μόνο αριθμοί.Η έξοδος αυτού του τμήματος μοιάζει με αυτό - {"";"";"";"";""";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – Η συνάρτηση TEXTJOIN τώρα απλώς συνδυάζει χαρακτήρες συμβολοσειράς Ακόμα υπάρχουν (μόνο αριθμοί) και αγνοεί τις κενές συμβολοσειρές.
Επαγγελματική συμβουλή:Εάν θέλετε να ελέγξετε την έξοδο μέρους του τύπου, επιλέξτε το κελί, πατήστε F2 για να μπείτε στη λειτουργία επεξεργασίας, επιλέξτε το τμήμα του τύπου που θέλετε να εξάγετε και πατήστε F9.Θα δείτε τα αποτελέσματα αμέσως.Στη συνέχεια, θυμηθείτε να πατήσετε Control + Z ή πατήστε Escape.Μην πατήσετε enter.
Μπορείτε επίσης να χρησιμοποιήσετε την ίδια λογική για να εξαγάγετε τμήματα κειμένου από αλφαριθμητικές συμβολοσειρές.Εδώ είναι ο τύπος για να λάβετε το τμήμα κειμένου από τη συμβολοσειρά:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Μια μικρή αλλαγή σε αυτόν τον τύπο είναι ότι η συνάρτηση IF χρησιμοποιείται για τον έλεγχο του πίνακα που λαμβάνουμε από τη συνάρτηση MID για σφάλματα.Εάν πρόκειται για σφάλμα, διατηρήστε την τιμή, διαφορετικά αντικαταστήστε την με κενή.
Στη συνέχεια, χρησιμοποιήστε το TEXTJOIN για να συνδυάσετε όλους τους χαρακτήρες κειμένου.
注意: Παρόλο που αυτός ο τύπος είναι έγκυρος, χρησιμοποιεί μια πτητική συνάρτηση (τη συνάρτηση INDIRECT).Αυτό σημαίνει ότι εάν το χρησιμοποιείτε με ένα τεράστιο σύνολο δεδομένων, μπορεί να χρειαστεί κάποιος χρόνος για να σας δώσει αποτελέσματα.Είναι καλύτερο να δημιουργήσετε ένα αντίγραφο ασφαλείας πριν χρησιμοποιήσετε αυτόν τον τύπο στο Excel.
Εξαγωγή αριθμών από συμβολοσειρές στο Excel (για Excel 2013/2010/2007)
Εάν έχετε Excel 2013. 2010. ή 2007, δεν μπορείτε να χρησιμοποιήσετε τύπους TEXTJOIN, επομένως πρέπει να χρησιμοποιήσετε σύνθετους τύπους για να το κάνετε αυτό.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως παρακάτω και θέλετε να εξαγάγετε όλους τους αριθμούς σε συμβολοσειρές σε κάθε κελί.
Ο παρακάτω τύπος θα το κάνει αυτό:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Αυτός ο τύπος θα επιστρέψει κενός (κενή συμβολοσειρά) εάν δεν υπάρχουν αριθμοί στη συμβολοσειρά κειμένου.
Αν και αυτός είναι ένας τύπος πίνακα, εσείςΔεν χρειάζεταιΧρησιμοποιήστε το "Control-Shift-Enter" για να το χρησιμοποιήσετε.Μια απλή εισαγωγή λειτουργεί για αυτόν τον τύπο.
Η πίστωση για αυτή τη φόρμουλα πηγαίνει στο εκπληκτικόΚύριε Excel Forum.
Και πάλι, αυτός ο τύπος θα εξαγάγει όλους τους αριθμούς στη συμβολοσειρά ανεξάρτητα από τη θέση.Για παράδειγμα, εάν το κείμενο είναι "10 εισιτήρια είναι 200 $", το αποτέλεσμα είναι 10200.
注意: Παρόλο που αυτός ο τύπος είναι έγκυρος, χρησιμοποιεί μια πτητική συνάρτηση (τη συνάρτηση INDIRECT).Αυτό σημαίνει ότι εάν το χρησιμοποιείτε με ένα τεράστιο σύνολο δεδομένων, μπορεί να χρειαστεί κάποιος χρόνος για να σας δώσει αποτελέσματα.Είναι καλύτερο να δημιουργήσετε ένα αντίγραφο ασφαλείας πριν χρησιμοποιήσετε αυτόν τον τύπο στο Excel.
Διαχωρίστε κείμενο και αριθμούς στο Excel με VBA
Εάν χρειάζεται συχνά να διαχωρίζετε κείμενο και αριθμούς (ή να εξαγάγετε αριθμούς από το κείμενο), μπορείτε επίσης να χρησιμοποιήσετε τη μέθοδο VBA.
Το μόνο που χρειάζεται να κάνετε είναι να δημιουργήσετε μια προσαρμοσμένη συνάρτηση που ορίζεται από το χρήστη (UDF) στο Excel χρησιμοποιώντας απλό κώδικα VBA και, στη συνέχεια, να χρησιμοποιήσετε αυτόν τον τύπο VBA αντί για έναν εκτενή και σύνθετο τύπο.
Επιτρέψτε μου να σας δείξω πώς να δημιουργήσετε δύο τύπους στο VBA - έναν για την εξαγωγή αριθμών και έναν για την εξαγωγή κειμένου από συμβολοσειρές.
Εξαγωγή αριθμών από συμβολοσειρές στο Excel (με χρήση VBA)
Σε αυτό το μέρος, θα σας δείξω πώς να δημιουργήσετε μια προσαρμοσμένη συνάρτηση για να λάβετε μόνο το αριθμητικό μέρος από μια συμβολοσειρά.
Εδώ είναι ο κώδικας VBA που θα χρησιμοποιήσουμε για να δημιουργήσουμε αυτήν την προσαρμοσμένη συνάρτηση:
Συνάρτηση GetNumeric(CellRef ως συμβολοσειρά) Dim StringLength ως ακέραιος StringLength = Len(CellRef) Για i = 1 To StringLength Αν IsNumeric(Mid(CellRef, i, 1)) Τότε Αποτέλεσμα = Αποτέλεσμα & Mid(CellRef, i, 1) Επόμενο i GetNumeric = Συνάρτηση τέλους αποτελέσματος
Ακολουθούν τα βήματα για να δημιουργήσετε αυτήν τη συνάρτηση και στη συνέχεια να τη χρησιμοποιήσετε σε ένα φύλλο εργασίας:
Τώρα θα μπορείτε να χρησιμοποιήσετε τη συνάρτηση GetText στο φύλλο εργασίας.Εφόσον έχουμε κάνει όλη τη βαρύτητα στον ίδιο τον κώδικα, το μόνο που χρειάζεται να κάνετε είναι να χρησιμοποιήσετε τον τύπο =GetNumeric(A2).
Αυτό θα σας δώσει αμέσως το αριθμητικό μέρος της συμβολοσειράς.
Σημειώστε ότι επειδή το βιβλίο εργασίας περιέχει πλέον κώδικα VBA, θα πρέπει να τον αποθηκεύσετε με επέκταση .xls ή .xlsm.
Εάν πρέπει να χρησιμοποιείτε συχνά αυτόν τον τύπο, μπορείτε επίσης να τον αποθηκεύσετε στο προσωπικό σας βιβλίο εργασίας μακροεντολών.Αυτό θα σας επιτρέψει να χρησιμοποιήσετε αυτόν τον προσαρμοσμένο τύπο σε οποιοδήποτε βιβλίο εργασίας του Excel χρησιμοποιείτε.
Εξαγωγή κειμένου από συμβολοσειρά στο Excel (με χρήση VBA)
Σε αυτό το μέρος, θα σας δείξω πώς να δημιουργήσετε μια προσαρμοσμένη συνάρτηση για να λάβετε μόνο το τμήμα κειμένου από μια συμβολοσειρά.
Εδώ είναι ο κώδικας VBA που θα χρησιμοποιήσουμε για να δημιουργήσουμε αυτήν την προσαρμοσμένη συνάρτηση:
Συνάρτηση GetText(CellRef ως συμβολοσειρά) Dim StringLength ως Integer StringLength = Len(CellRef) Για i = 1 To StringLength Εάν όχι (IsNumeric(Mid(CellRef, i, 1))) Στη συνέχεια Αποτέλεσμα = Αποτέλεσμα & Mid(CellRef, i, 1 ) Επόμενο i GetText = Συνάρτηση Τέλος Αποτέλεσμα
Ακολουθούν τα βήματα για να δημιουργήσετε αυτήν τη συνάρτηση και στη συνέχεια να τη χρησιμοποιήσετε σε ένα φύλλο εργασίας:
Τώρα θα μπορείτε να χρησιμοποιήσετε τη συνάρτηση GetNumeric στο φύλλο εργασίας.Δεδομένου ότι έχουμε κάνει όλη τη βαρύτητα στον ίδιο τον κώδικα, το μόνο που χρειάζεται να κάνετε είναι να χρησιμοποιήσετε τον τύπο =GetText(A2).
Αυτό θα σας δώσει αμέσως το αριθμητικό μέρος της συμβολοσειράς.
Σημειώστε ότι επειδή το βιβλίο εργασίας περιέχει πλέον κώδικα VBA, θα πρέπει να τον αποθηκεύσετε με επέκταση .xls ή .xlsm.
Εάν πρέπει να χρησιμοποιείτε συχνά αυτόν τον τύπο, μπορείτε επίσης να τον αποθηκεύσετε στο προσωπικό σας βιβλίο εργασίας μακροεντολών.Αυτό θα σας επιτρέψει να χρησιμοποιήσετε αυτόν τον προσαρμοσμένο τύπο σε οποιοδήποτε βιβλίο εργασίας του Excel χρησιμοποιείτε.
