Γενική φόρμουλα
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Περίληψη
Για να αφαιρέσετε αριθμητικούς χαρακτήρες από μια συμβολοσειρά κειμένου, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στη συνάρτηση TEXTJOIN. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C5 είναι:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Εξήγηση
Το Excel δεν έχει τρόπο να μεταδώσει τα γράμματα σε μια συμβολοσειρά κειμένου σε έναν πίνακα απευθείας σε έναν τύπο. Ως λύση, αυτός ο τύπος χρησιμοποιεί τη συνάρτηση MID, με τη βοήθεια των λειτουργιών ROW και INDIRECT για την επίτευξη του ίδιου αποτελέσματος. Ο τύπος στο C5, αντιγράφεται, είναι:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Αυτό φαίνεται αρκετά περίπλοκο, αλλά η ουσία είναι ότι δημιουργούμε μια σειρά από όλους τους χαρακτήρες στο B5 και δοκιμάζουμε κάθε χαρακτήρα για να δούμε αν είναι ένας αριθμός. Εάν ναι, απορρίπτουμε την τιμή και την αντικαθιστούμε με μια κενή συμβολοσειρά (""). Εάν όχι, προσθέτουμε τον μη αριθμητικό χαρακτήρα σε έναν "επεξεργασμένο" πίνακα. Τέλος, χρησιμοποιούμε τη συνάρτηση TEXTJOIN (νέα στο Excel 2019) για να συνενώσουμε όλους τους χαρακτήρες μαζί, αγνοώντας τις κενές τιμές.
Δουλεύοντας από μέσα προς τα έξω, η συνάρτηση MID χρησιμοποιείται για την εξαγωγή του κειμένου σε B5, έναν χαρακτήρα κάθε φορά. Το κλειδί είναι το απόσπασμα ROW και INDIRECT εδώ:
ROW(INDIRECT("1:100"))
που περιστρέφει έναν πίνακα που περιέχει 100 αριθμούς όπως αυτό:
(1,2,3,4,5,6,7,8… .99,100)
Σημείωση: 100 αντιπροσωπεύει τους μέγιστους χαρακτήρες για επεξεργασία. Αλλάξτε για να ταιριάζει στα δεδομένα σας ή χρησιμοποιήστε τη λειτουργία LEN όπως εξηγείται παρακάτω.
Αυτός ο πίνακας μπαίνει στη συνάρτηση MID ως όρισμα start_num . Για num_chars , χρησιμοποιούμε 1.
Η συνάρτηση MID επιστρέφει έναν πίνακα ως εξής:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Σημείωση: επιπλέον στοιχεία στον πίνακα καταργήθηκαν για αναγνωσιμότητα
Σε αυτόν τον πίνακα, προσθέτουμε μηδέν. Αυτό είναι ένα απλό τέχνασμα που αναγκάζει το Excel να εξαναγκάσει κείμενο σε έναν αριθμό. Οι αριθμητικές τιμές κειμένου όπως "1", "2", "3", "4" κ.λπ. μετατρέπονται χωρίς σφάλματα, αλλά οι μη αριθμητικές τιμές θα αποτύχουν και θα ρίξουν ένα #VALUE σφάλμα. Χρησιμοποιούμε τη συνάρτηση IF με τη συνάρτηση ISERR για να εντοπίσουμε αυτά τα σφάλματα. Όταν βλέπουμε ένα σφάλμα, γνωρίζουμε ότι έχουμε έναν μη αριθμητικό χαρακτήρα, επομένως φέρνουμε αυτόν τον χαρακτήρα στον επεξεργασμένο πίνακα με μια άλλη συνάρτηση MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Εάν δεν λάβουμε σφάλμα, γνωρίζουμε ότι έχουμε έναν αριθμό, οπότε εισάγουμε μια κενή συμβολοσειρά ("") στον πίνακα αντί του αριθμού.
Το τελικό αποτέλεσμα πίνακα πηγαίνει στη συνάρτηση TEXTJOIN ως όρισμα text1. Για οριοθέτη, χρησιμοποιούμε μια κενή συμβολοσειρά ("") και για ign_empty παρέχουμε TRUE Στη συνέχεια, το TEXTJOIN συνενώνει όλες τις μη κενές τιμές στον πίνακα και επιστρέφει το αποτέλεσμα.
Ακριβές μήκος πίνακα
Αντί να κωδικοποιήσετε έναν αριθμό όπως 100 σε INDIRECT, μπορείτε να χρησιμοποιήσετε τη συνάρτηση LEN για να δημιουργήσετε έναν πίνακα με τον πραγματικό αριθμό χαρακτήρων στο κελί όπως αυτό:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
Το LEN επιστρέφει τον αριθμό χαρακτήρων στο κελί ως αριθμό, ο οποίος χρησιμοποιείται αντί για 100. Αυτό επιτρέπει στον τύπο να κλιμακώσει αυτόματα έως και οποιονδήποτε αριθμό χαρακτήρων.
Αφαίρεση επιπλέον χώρου
Όταν απογυμνώνετε αριθμητικούς χαρακτήρες, ενδέχεται να έχετε επιπλέον χαρακτήρες διαστήματος. Για να απογυμνώσετε κενά και πίσω θέσεις και να ομαλοποιήσετε κενά μεταξύ λέξεων, μπορείτε να τυλίξετε τον τύπο που εμφανίζεται σε αυτήν τη σελίδα μέσα στη συνάρτηση TRIM:
=TRIM(formula)
Με ΑΚΟΛΟΥΘΙΑ
Στο Excel 365, η νέα συνάρτηση SEQUENCE μπορεί να αντικαταστήσει τον κωδικό ROW + INDIRECT παραπάνω:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Εδώ, χρησιμοποιούμε το SEQUENCE + LEN για να δημιουργήσουμε μια σειρά σωστού μήκους σε ένα βήμα.
Με LET
Μπορούμε να βελτιώσουμε περαιτέρω αυτόν τον τύπο με τη συνάρτηση LET. Επειδή ο πίνακας έχει δημιουργηθεί δύο φορές παραπάνω με SEQUENCE και LEN, μπορούμε να ορίσουμε τον πίνακα ως μεταβλητή και να τον δημιουργήσουμε μόνο μία φορά:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Εδώ η τιμή του πίνακα ορίζεται μόνο μία φορά και στη συνέχεια χρησιμοποιείται δύο φορές μέσα στη συνάρτηση MID.
