Εξαγωγή αριθμών από μια συμβολοσειρά στο Excel (χρησιμοποιώντας τύπους ή VBA)

Παρακολουθήστε βίντεο - Πώς να εξαγάγετε αριθμούς από συμβολοσειρά κειμένου στο Excel (χρησιμοποιώντας τον τύπο και το VBA)

Δεν υπάρχει ενσωματωμένη συνάρτηση στο Excel για εξαγωγή αριθμών από μια συμβολοσειρά σε ένα κελί (ή αντίστροφα - αφαιρέστε το αριθμητικό μέρος και εξαγάγετε το τμήμα κειμένου από μια αλφαριθμητική συμβολοσειρά).

Ωστόσο, αυτό μπορεί να γίνει χρησιμοποιώντας ένα κοκτέιλ λειτουργιών του Excel ή κάποιον απλό κώδικα VBA.

Επιτρέψτε μου πρώτα να σας δείξω για τι πράγμα μιλάω.

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να εξαγάγετε τους αριθμούς από τη συμβολοσειρά (όπως φαίνεται παρακάτω):

Η μέθοδος που θα επιλέξετε θα εξαρτηθεί επίσης από την έκδοση του Excel που χρησιμοποιείτε:

• Για εκδόσεις πριν από το Excel 2016, πρέπει να χρησιμοποιήσετε ελαφρώς μεγαλύτερους τύπους
• Για το Excel 2016, μπορείτε να χρησιμοποιήσετε τη νέα λειτουργία TEXTJOIN
• Η μέθοδος VBA μπορεί να χρησιμοποιηθεί σε όλες τις εκδόσεις του Excel

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Εξαγωγή αριθμών από συμβολοσειρά στο Excel (Τύπος για Excel 2016)

Αυτός ο τύπος θα λειτουργήσει μόνο στο Excel 2016 καθώς χρησιμοποιεί τη συνάρτηση TEXTJOIN που εισήχθη πρόσφατα.

Επίσης, αυτός ο τύπος μπορεί να εξαγάγει τους αριθμούς που βρίσκονται στην αρχή, στο τέλος ή στη μέση της συμβολοσειράς κειμένου.

Σημειώστε ότι ο τύπος TEXTJOIN που καλύπτεται σε αυτήν την ενότητα θα σας δώσει όλους τους αριθμητικούς χαρακτήρες μαζί. Για παράδειγμα, εάν το κείμενο είναι "Η τιμή των 10 εισιτηρίων είναι 200 ​​USD", θα σας δώσει 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 USD", θα επιστρέψει 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) - Αυτό το μέρος του τύπου θα επιστρέψει έναν πίνακα #ΤΙΜΗΣ! σφάλματα ή αριθμοί με βάση τη συμβολοσειρά. Όλοι οι χαρακτήρες κειμένου στη συμβολοσειρά γίνονται #ΤΙΜΗ! σφάλματα και όλες οι αριθμητικές τιμές παραμένουν ως έχουν. Αυτό συμβαίνει καθώς έχουμε πολλαπλασιάσει τη συνάρτηση 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 συνδυάζει τώρα απλά τους χαρακτήρες συμβολοσειράς που παραμένει (που είναι μόνο οι αριθμοί) και αγνοεί την κενή συμβολοσειρά.

Pro Συμβουλή: Εάν θέλετε να ελέγξετε την έξοδο ενός μέρους του τύπου, επιλέξτε το κελί, πατήστε F2 για να μπείτε στη λειτουργία επεξεργασίας, επιλέξτε το τμήμα του τύπου για το οποίο θέλετε την έξοδο και πατήστε F9. Θα δείτε αμέσως το αποτέλεσμα. Στη συνέχεια, θυμηθείτε είτε να πατήσετε Control + Z είτε να πατήσετε το πλήκτρο Escape. ΜΗΝ πατήσετε το πλήκτρο εισαγωγής.

Κατεβάστε το Παράδειγμα αρχείου

Μπορείτε επίσης να χρησιμοποιήσετε την ίδια λογική για να εξαγάγετε το τμήμα κειμένου από μια αλφαριθμητική συμβολοσειρά. Παρακάτω είναι ο τύπος που θα λάβει το τμήμα κειμένου από τη συμβολοσειρά:

= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN (Α2))), 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.

Και πάλι, αυτός ο τύπος θα εξαγάγει όλους τους αριθμούς στη συμβολοσειρά, ανεξάρτητα από τη θέση. Για παράδειγμα, εάν το κείμενο είναι "Η τιμή των 10 εισιτηρίων είναι 200 ​​USD", θα σας δώσει 10200 ως αποτέλεσμα.

Προσοχή: Ενώ αυτός ο τύπος λειτουργεί τέλεια, χρησιμοποιεί μια πτητική συνάρτηση (η συνάρτηση INDIRECT). Αυτό σημαίνει ότι σε περίπτωση που το χρησιμοποιήσετε με ένα τεράστιο σύνολο δεδομένων, μπορεί να χρειαστεί λίγος χρόνος για να σας δώσει τα αποτελέσματα. Είναι καλύτερο να δημιουργήσετε ένα αντίγραφο ασφαλείας πριν χρησιμοποιήσετε αυτόν τον τύπο στο Excel.

Ξεχωριστό κείμενο και αριθμοί στο Excel χρησιμοποιώντας VBA

Εάν ο διαχωρισμός κειμένου και αριθμών (ή η εξαγωγή αριθμών από το κείμενο) είναι κάτι που πρέπει συχνά, μπορείτε επίσης να χρησιμοποιήσετε τη μέθοδο VBA.

Το μόνο που χρειάζεται να κάνετε είναι να χρησιμοποιήσετε έναν απλό κώδικα VBA για να δημιουργήσετε μια προσαρμοσμένη συνάρτηση που καθορίζεται από το χρήστη (UDF) στο Excel και, στη συνέχεια, αντί να χρησιμοποιείτε μακριούς και περίπλοκους τύπους, χρησιμοποιήστε αυτόν τον τύπο VBA.

Επιτρέψτε μου να σας δείξω πώς να δημιουργήσετε δύο τύπους στο VBA - έναν για εξαγωγή αριθμών και έναν για εξαγωγή κειμένου από μια συμβολοσειρά.

Εξαγωγή αριθμών από συμβολοσειρά στο Excel (χρησιμοποιώντας VBA)

Σε αυτό το μέρος, θα σας δείξω πώς να δημιουργήσετε την προσαρμοσμένη συνάρτηση για να λάβετε μόνο το αριθμητικό μέρος από μια συμβολοσειρά.

Παρακάτω είναι ο κώδικας VBA που θα χρησιμοποιήσουμε για τη δημιουργία αυτής της προσαρμοσμένης λειτουργίας:

Συνάρτηση GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Στη συνέχεια Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Λειτουργία λήξης αποτελεσμάτων

Ακολουθούν τα βήματα για να δημιουργήσετε αυτήν τη συνάρτηση και, στη συνέχεια, να τη χρησιμοποιήσετε στο φύλλο εργασίας:

• Μεταβείτε στην καρτέλα Προγραμματιστής.
• Κάντε κλικ στο Visual Basic (Μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου ALT + F11)
• Στο backend του VB Editor που ανοίγει, κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα του βιβλίου εργασίας.
• Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει το αντικείμενο μονάδας για το βιβλίο εργασίας.
• Στο παράθυρο κωδικού μονάδας, αντιγράψτε και επικολλήστε τον κωδικό VBA που αναφέρθηκε παραπάνω.
• Κλείστε το πρόγραμμα επεξεργασίας VB.

Τώρα, θα μπορείτε να χρησιμοποιήσετε τη συνάρτηση GetText στο φύλλο εργασίας. Δεδομένου ότι έχουμε κάνει όλη την άρση βαρών στον ίδιο τον κώδικα, το μόνο που χρειάζεται να κάνετε είναι να χρησιμοποιήσετε τον τύπο = GetNumeric (A2).

Αυτό θα σας δώσει αμέσως μόνο το αριθμητικό μέρος της συμβολοσειράς.

Σημειώστε ότι δεδομένου ότι το βιβλίο εργασίας έχει πλέον κωδικό VBA, πρέπει να το αποθηκεύσετε με επέκταση .xls ή .xlsm.

Κατεβάστε το Παράδειγμα αρχείου

Σε περίπτωση που πρέπει να χρησιμοποιείτε συχνά αυτόν τον τύπο, μπορείτε επίσης να το αποθηκεύσετε στο προσωπικό σας βιβλίο μακροεντολών εργασίας. Αυτό θα σας επιτρέψει να χρησιμοποιήσετε αυτόν τον προσαρμοσμένο τύπο σε οποιοδήποτε από τα βιβλία εργασίας του Excel με τα οποία εργάζεστε.

Εξαγωγή κειμένου από μια συμβολοσειρά στο Excel (χρησιμοποιώντας VBA)

Σε αυτό το μέρος, θα σας δείξω πώς να δημιουργήσετε την προσαρμοσμένη συνάρτηση για να λάβετε μόνο το τμήμα κειμένου από μια συμβολοσειρά.

Παρακάτω είναι ο κώδικας VBA που θα χρησιμοποιήσουμε για τη δημιουργία αυτής της προσαρμοσμένης λειτουργίας:

Λειτουργία GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Στη συνέχεια Result = Result & Mid (CellRef, i, 1 ) Επόμενο i GetText = Λειτουργία λήξης αποτελεσμάτων

Ακολουθούν τα βήματα για να δημιουργήσετε αυτήν τη συνάρτηση και στη συνέχεια να τη χρησιμοποιήσετε στο φύλλο εργασίας:

• Μεταβείτε στην καρτέλα Προγραμματιστής.
• Κάντε κλικ στο Visual Basic (Μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου ALT + F11)
• Στο backend του VB Editor που ανοίγει, κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα του βιβλίου εργασίας.
• Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει το αντικείμενο μονάδας για το βιβλίο εργασίας.
  • Εάν έχετε ήδη μια ενότητα, κάντε διπλό κλικ σε αυτήν (δεν χρειάζεται να εισαγάγετε μια νέα εάν την έχετε ήδη).
• Στο παράθυρο κωδικού μονάδας, αντιγράψτε και επικολλήστε τον κωδικό VBA που αναφέρθηκε παραπάνω.
• Κλείστε το πρόγραμμα επεξεργασίας VB.

Τώρα, θα μπορείτε να χρησιμοποιήσετε τη συνάρτηση GetNumeric στο φύλλο εργασίας. Δεδομένου ότι έχουμε κάνει όλη την άρση βαρών στον ίδιο τον κώδικα, το μόνο που χρειάζεται να κάνετε είναι να χρησιμοποιήσετε τον τύπο = GetText (A2).

Αυτό θα σας δώσει αμέσως μόνο το αριθμητικό μέρος της συμβολοσειράς.

Σημειώστε ότι δεδομένου ότι το βιβλίο εργασίας έχει πλέον κωδικό VBA, πρέπει να το αποθηκεύσετε με επέκταση .xls ή .xlsm.

Σε περίπτωση που πρέπει να χρησιμοποιείτε συχνά αυτόν τον τύπο, μπορείτε επίσης να τον αποθηκεύσετε στο Προσωπικό σας βιβλίο μακροεντολών εργασίας. Αυτό θα σας επιτρέψει να χρησιμοποιήσετε αυτόν τον προσαρμοσμένο τύπο σε οποιοδήποτε βιβλίο εργασίας του Excel με το οποίο εργάζεστε.

Σε περίπτωση που χρησιμοποιείτε Excel 2013 ή προηγούμενες εκδόσεις και δεν έχετε