Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα σας δείξουν πώς μπορείτε να βρείτε και να εξαγάγετε έναν αριθμό από μια συμβολοσειρά κειμένου στο Excel και στα Υπολογιστικά φύλλα Google.
Εύρεση και εξαγωγή αριθμού από συμβολοσειρά
Μερικές φορές τα δεδομένα σας ενδέχεται να περιέχουν αριθμούς και κείμενο και θέλετε να εξαγάγετε τα αριθμητικά δεδομένα.
Εάν το τμήμα αριθμών βρίσκεται στη δεξιά ή αριστερή πλευρά της συμβολοσειράς, είναι σχετικά εύκολο να διαιρέσετε τον αριθμό και το κείμενο. Ωστόσο, εάν οι αριθμοί βρίσκονται μέσα στη συμβολοσειρά, δηλαδή μεταξύ δύο συμβολοσειρών κειμένου, θα χρειαστεί να χρησιμοποιήσετε έναν πολύ πιο περίπλοκο τύπο (φαίνεται παρακάτω).
TEXTJOIN - Εξαγωγή αριθμών στο Excel 2016+
Στο Excel 2016 εισήχθη η συνάρτηση TEXTJOIN, η οποία μπορεί να εξαγάγει τους αριθμούς από οπουδήποτε στη συμβολοσειρά κειμένου. Εδώ είναι ο τύπος:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
Ας δούμε πώς λειτουργεί αυτός ο τύπος.
Οι λειτουργίες ROW, INDIRECT και LEN επιστρέφουν μια σειρά αριθμών που αντιστοιχούν σε κάθε χαρακτήρα της αλφαριθμητικής συμβολοσειράς σας. Στην περίπτωσή μας, το "Monday01Day" έχει 11 χαρακτήρες, οπότε η συνάρτηση ROW θα επιστρέψει τον πίνακα {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), "")) |
Στη συνέχεια, η συνάρτηση MID εξάγει κάθε χαρακτήρα από τη συμβολοσειρά κειμένου, δημιουργώντας έναν πίνακα που περιέχει την αρχική συμβολοσειρά σας:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Ο πολλαπλασιασμός κάθε τιμής στον πίνακα με 1 θα δημιουργήσει έναν πίνακα που περιέχει σφάλματα Εάν ο χαρακτήρας του πίνακα ήταν κείμενο:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE !}), "")) |
Στη συνέχεια, η συνάρτηση IFERROR, καταργεί τις τιμές σφάλματος:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; "" "" "" "" ""; 0; 1; "" "" "" "" "}) |
Αφήνοντας μόνο τη συνάρτηση TEXTJOIN η οποία ενώνει τους υπόλοιπους αριθμούς μαζί.
Σημειώστε ότι ο τύπος θα σας δώσει όλους τους αριθμητικούς χαρακτήρες μαζί από τη συμβολοσειρά σας. Για παράδειγμα, εάν η αλφαριθμητική συμβολοσειρά σας είναι Monday01Day01, θα σας δώσει 0101 ως αποτέλεσμα.
Εξαγωγή αριθμών - Πριν από το Excel 2016
Πριν από το Excel 2016, θα μπορούσατε να χρησιμοποιήσετε μια πολύ πιο περίπλοκη μέθοδο για να εξαγάγετε αριθμούς από κείμενο. Μπορείτε να χρησιμοποιήσετε τη συνάρτηση FIND μαζί με τις συναρτήσεις IFERROR και MIN για να προσδιορίσετε τόσο την πρώτη θέση του τμήματος αριθμού όσο και την πρώτη θέση του τμήματος κειμένου μετά τον αριθμό. Στη συνέχεια, απλά εξάγουμε το αριθμητικό μέρος με τη λειτουργία MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), MIN (IFERROR (SEARCH ({"a" , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, Β3), 999999999))) |
Σημείωση: Αυτός είναι ένας τύπος πίνακα, πρέπει να εισαγάγετε τον τύπο πιέζοντας CTRL + SHIFT + ENTER, αντί απλά ENTER.
Ας δούμε βήμα -βήμα πώς λειτουργεί αυτός ο τύπος.
Βρείτε τον αριθμό της γροθιάς
Μπορούμε να χρησιμοποιήσουμε τη συνάρτηση FIND για να εντοπίσουμε την αρχική θέση του αριθμού.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Για το όρισμα find_text της συνάρτησης FIND, χρησιμοποιούμε τη σταθερά πίνακα {0,1,2,3,4,5,6,7,8,9}, η οποία κάνει τη συνάρτηση FIND να εκτελεί ξεχωριστή αναζήτηση για κάθε τιμή στο η σταθερά του πίνακα.
Το όρισμα εντός_κειμένου της συνάρτησης FIND στην περίπτωσή μας είναι Monday01Day, στην οποία 1 μπορεί να βρεθεί στη θέση 8 και 0 στη θέση 7, οπότε ο πίνακας αποτελεσμάτων μας θα είναι: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Χρησιμοποιώντας τη συνάρτηση IFERROR αντικαθιστούμε τα σφάλματα #VALUE κατά 999999999. Στη συνέχεια, απλά αναζητούμε το ελάχιστο σε αυτόν τον πίνακα και, ως εκ τούτου, παίρνουμε τη θέση του πρώτου αριθμού (7).
Λάβετε υπόψη ότι ο παραπάνω τύπος είναι ένας τύπος πίνακα, πρέπει να πατήσετε Ctrl+Shift+Enter για να τον ενεργοποιήσετε.
Εύρεση χαρακτήρα πρώτου κειμένου μετά τον αριθμό
Ομοίως με τον εντοπισμό του πρώτου αριθμού εντός της συμβολοσειράς, χρησιμοποιούμε τη συνάρτηση FIND για να καθορίσουμε πού ξεκινά ξανά το κείμενο μετά τον αριθμό που κάνει καλή χρήση του ορίσματος start_num της συνάρτησης.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Αυτός ο τύπος λειτουργεί πολύ παρόμοια με τον προηγούμενο που χρησιμοποιήθηκε για τον εντοπισμό του πρώτου αριθμού, μόνο που χρησιμοποιούμε γράμματα στη σταθερά του πίνακα μας και επομένως οι αριθμοί προκαλούν σφάλματα #VALUE. Λάβετε υπόψη ότι ξεκινάμε την αναζήτηση μόνο αφού καθοριστεί η θέση για τον πρώτο αριθμό (αυτό θα είναι το όρισμα start_num) και όχι από την αρχή της συμβολοσειράς.
Μην ξεχάσετε να πατήσετε Ctrl+Shift+Enter για να χρησιμοποιήσετε τον παραπάνω τύπο.
Δεν υπάρχει τίποτα άλλο από το να τα συνδυάσετε όλα αυτά.
Εξαγωγή αριθμού από δύο κείμενα
Μόλις έχουμε την αρχική θέση του αριθμητικού μέρους και την αρχή του τμήματος κειμένου μετά από αυτό, απλώς χρησιμοποιούμε τη συνάρτηση MID για να εξαγάγουμε το επιθυμητό τμήμα αριθμού.
| 1 | = MID (B3, C3, D3-C3) |
Άλλη Μέθοδος
Χωρίς να το εξηγήσετε λεπτομερέστερα, μπορείτε επίσης να χρησιμοποιήσετε τον παρακάτω σύνθετο τύπο για να λάβετε τον αριθμό μέσα από μια συμβολοσειρά.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:) & LEN (B3)))), 1))*ROW (INDIRECT (" 1: "& LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3))))+1,1)*10^ROW (INDIRECT ("1:" & LEN (B3)))/10) |
Λάβετε υπόψη ότι αυτός ο παραπάνω τύπος θα σας δώσει 0 όταν δεν υπάρχει αριθμός στη συμβολοσειρά και ότι τα μηδενικά που οδηγούν θα παραμείνουν εκτός.
Εύρεση και εξαγωγή αριθμού από συμβολοσειρά σε κείμενο στα Υπολογιστικά φύλλα Google
Τα παραδείγματα που εμφανίζονται παραπάνω λειτουργούν με τον ίδιο τρόπο στα Υπολογιστικά φύλλα Google όπως και στο Excel.
