Πώς να χωρίσετε αριθμούς και κείμενο από συμβολοσειρά στο Excel

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

Σενάριο:
Έτσι, ένας φίλος μας στο Exceltip.com έκανε αυτήν την ερώτηση στην ενότητα σχολίων. «Πώς διαχωρίζω τους αριθμούς που έρχονται πριν από ένα κείμενο και στο τέλος του κειμένου χρησιμοποιώντας τον τύπο excel. Για παράδειγμα 125EvenueStreet και LoveYou3000 κ.λπ. »

Για την εξαγωγή κειμένου, χρησιμοποιούμε ΣΩΣΤΗ, ΑΡΙΣΤΕΡΑ, MID και άλλες λειτουργίες κειμένου. Απλώς πρέπει να γνωρίζουμε τον αριθμό των κειμένων που πρέπει να εξαχθούν. Και εδώ θα κάνουμε το ίδιο πρώτα.
Εξαγωγή αριθμού και κειμένου από μια συμβολοσειρά όταν ο αριθμός βρίσκεται στο τέλος της συμβολοσειράς
Για το παραπάνω παράδειγμα έχω ετοιμάσει αυτό το φύλλο. Στο κελί Α2, έχω τη συμβολοσειρά. Στο κελί B2, θέλω το τμήμα κειμένου και στο C2 το τμήμα αριθμών.

Απλώς πρέπει να γνωρίζουμε τη θέση από την οποία ξεκινά ο αριθμός. Στη συνέχεια θα χρησιμοποιήσουμε την αριστερή και άλλη συνάρτηση. Έτσι για να πάρουμε τη θέση του πρώτου αριθμού χρησιμοποιούμε τον παρακάτω γενικό τύπο:
Γενικός τύπος για να λάβετε τη θέση του πρώτου αριθμού στη συμβολοσειρά:

= MIN (ΑΝΑΖΗΤΗΣΗ ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

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

= MIN (ΑΝΑΖΗΤΗΣΗ ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))

Εξαγωγή τμήματος κειμένου

Θα επιστρέψει 15 καθώς ο πρώτος αριθμός που βρίσκεται βρίσκεται στη 15η θέση στο Κείμενο. Θα το εξηγήσω αργότερα.

Τώρα, για να λάβουμε το Κείμενο, από αριστερά πρέπει απλώς να πάρουμε 15-1 χαρακτήρες από συμβολοσειρά. Έτσι θα χρησιμοποιήσουμε
Λειτουργία ΑΡΙΣΤΕΡΑ για εξαγωγή κειμένου.
Τύπος για εξαγωγή κειμένου από αριστερά

= ΑΡΙΣΤΕΡΑ (A5, MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)

Εδώ αφαιρέσαμε το 1 από τον αριθμό που επέστρεψε το MIN (ΑΝΑΖΗΤΗΣΗ ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Μέρος αριθμού αποσπάσματος

Τώρα για να πάρουμε αριθμούς, πρέπει απλώς να πάρουμε αριθμούς χαρακτήρων από τον 1ο αριθμό που βρέθηκε. Έτσι υπολογίζουμε το συνολικό μήκος του σειρά και αφαιρέστε τη θέση του πρώτου αριθμού που βρέθηκε και προσθέστε 1 σε αυτό Απλός. Ναι, είναι απλά πολύπλοκο, είναι απλό.
Τύπος για εξαγωγή αριθμών από δεξιά

= ΔΕΞΙΑ (A5, LEN (A5) -MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")))+1)

Εδώ μόλις πήραμε το συνολικό μήκος συμβολοσειράς χρησιμοποιώντας τη συνάρτηση LEN και αφαιρέσαμε τη θέση του πρώτου αριθμού που βρήκαμε και στη συνέχεια προσθέσαμε 1 σε αυτόν. Αυτό μας δίνει συνολικό αριθμό αριθμών. Μάθετε περισσότερα εδώ σχετικά με την εξαγωγή κειμένου χρησιμοποιώντας τις ΑΡΙΣΤΕΡΕΣ και ΔΕΞΙΕΣ συναρτήσεις του Excel.

Επομένως, η συνάρτηση ΑΡΙΣΤΕΡΑ και ΔΕΞΙΑ είναι απλή. Το Tricky μέρος είναι MIN and SEARCH Part που μας δίνει τη θέση του πρώτου αριθμού που βρέθηκε. Ας το καταλάβουμε.
Πως δουλεύει
Γνωρίζουμε πώς λειτουργούν τα αριστερά και τα δεξιά. Θα διερευνήσουμε το κύριο μέρος αυτού του τύπου που παίρνει τη θέση του πρώτου αριθμού που βρέθηκε και αυτό είναι: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Η συνάρτηση SEARCH επιστρέφει τη θέση ενός κειμένου σε συμβολοσειρά. Η συνάρτηση SEARCH («κείμενο», «συμβολοσειρά») απαιτεί δύο ορίσματα, πρώτα το κείμενο που θέλετε να αναζητήσετε, και δεύτερον τη συμβολοσειρά στην οποία θέλετε να αναζητήσετε.

• • Εδώ στην ΑΝΑΖΗΤΗΣΗ, στη θέση κειμένου έχουμε έναν πίνακα αριθμών από 0 έως 9. Και στη θέση συμβολοσειράς έχουμε συμβολοσειρά που είναι συνδεδεμένη με "0123456789" χρησιμοποιώντας & χειριστής. Γιατί; Θα σου πω.
  • Κάθε στοιχείο στον πίνακα {0,1,2,3,4,5,6,7,8,9} θα αναζητηθεί σε δεδομένη συμβολοσειρά και θα επιστρέψει τη θέση του σε συμβολοσειρά μορφής πίνακα στον ίδιο δείκτη στον πίνακα.
  • Εάν δεν βρεθεί κάποια τιμή, θα προκαλέσει σφάλμα. Επομένως, ολόκληρος ο τύπος θα οδηγήσει σε σφάλμα. Για να αποφευχθεί αυτό, συνδυάσαμε τους αριθμούς "0123456789" στο κείμενο. Έτσι ώστε να βρίσκει πάντα κάθε αριθμό σε συμβολοσειρά. Αυτοί οι αριθμοί είναι στο τέλος, επομένως δεν θα προκαλέσουν κανένα πρόβλημα.
  • Τώρα Η συνάρτηση MIN επιστρέφει τη μικρότερη τιμή από τον πίνακα που επιστρέφεται από τη συνάρτηση SEARCH. Αυτή η μικρότερη τιμή θα είναι ο πρώτος αριθμός στη συμβολοσειρά. Τώρα χρησιμοποιώντας αυτήν τη συνάρτηση ΑΡΙΘΜΟΣ και ΑΡΙΣΤΕΡΑ και ΔΕΞΙΑ, μπορούμε να χωρίσουμε τα μέρη κειμένου και συμβολοσειράς.

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

• • MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): αυτό θα μεταφραστεί σε MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9}, "Monta270123456789”))

Τώρα, όπως εξήγησα, η αναζήτηση θα αναζητήσει κάθε αριθμό στον πίνακα {0,1,2,3,4,5,6,7,8,9} σε Monta270123456789 και θα επιστρέψει τη θέση του σε μορφή πίνακα. Ο πίνακας που επιστρέφεται θα είναι {8,9,6,11,12,13,14,7,16,17}. Πως?
0 θα αναζητηθεί σε συμβολοσειρά. Βρίσκεται στη θέση 8. Ως εκ τούτου, το πρώτο μας στοιχείο είναι 8. Σημειώστε ότι το αρχικό μας κείμενο έχει μήκος μόνο 7 χαρακτήρες. Αποκτήστε το. 0 δεν είναι μέρος του Μόντα27.
Το επόμενο 1 θα αναζητηθεί σε συμβολοσειρά και δεν είναι επίσης μέρος της αρχικής συμβολοσειράς και παίρνουμε τη θέση 9.
Τα επόμενα 2 θα αναζητηθούν. Δεδομένου ότι είναι το μέρος της αρχικής συμβολοσειράς, παίρνουμε το ευρετήριό του ως 6.
Ομοίως, κάθε στοιχείο βρίσκεται σε κάποια θέση.

• • Τώρα αυτός ο πίνακας μεταβιβάζεται στη συνάρτηση MIN ως MIN ({8,9,6,11,12,13,14,7,16,17}). Το MIN επιστρέφει το 6 που είναι η θέση του πρώτου αριθμού που βρέθηκε στο αρχικό κείμενο.
    Και η ιστορία μετά από αυτό είναι αρκετά απλή. Χρησιμοποιούμε αυτόν τον αριθμό εξαγωγής κειμένου και αριθμών χρησιμοποιώντας ΑΡΙΣΤΕΡΑ και ΔΕΞΙΑ Λειτουργία.

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

Έχω ετοιμάσει έναν παρόμοιο πίνακα όπως παραπάνω. Απλά έχει αριθμό στην αρχή.

Εδώ θα χρησιμοποιήσουμε μια διαφορετική τεχνική. Θα μετρήσουμε το μήκος των αριθμών (που είναι 2 εδώ) και θα εξάγουμε αυτόν τον αριθμό χαρακτήρων από αριστερά του String.
Η μέθοδος είναι = ΑΡΙΣΤΕΡΑ (συμβολοσειρά, αριθμός αριθμών)
Για να μετρήσετε τον αριθμό των χαρακτήρων, αυτός είναι ο τύπος.
Γενικός τύπος για τον αριθμό των αριθμών:

= SUM (LEN (συμβολοσειρά) -LEN (SUBSTITUTE (συμβολοσειρά, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Εδώ,

• • • Η συνάρτηση SUBSTITUTE θα αντικαταστήσει κάθε αριθμό που βρέθηκε με "" (κενό). Εάν βρεθεί ένας αριθμός που έχει αντικατασταθεί και η νέα συμβολοσειρά θα προστεθεί στον πίνακα, θα προστεθεί άλλη αρχική συμβολοσειρά στον πίνακα. Με αυτόν τον τρόπο, θα έχουμε μια σειρά από 10 χορδές.
    • Τώρα η συνάρτηση LEN θα επιστρέψει το μήκος χαρακτήρων σε μια σειρά από αυτές τις συμβολοσειρές.
    • Στη συνέχεια, από το μήκος των αρχικών συμβολοσειρών, θα αφαιρέσουμε το μήκος κάθε συμβολοσειράς που επιστρέφεται από τη συνάρτηση SUBSTITUTE. Αυτό θα επιστρέψει ξανά έναν πίνακα.
    • Τώρα το SUM θα προσθέσει όλους αυτούς τους αριθμούς. Αυτός είναι ο αριθμός των αριθμών σε συμβολοσειρά.

Εξαγωγή τμήματος αριθμού από συμβολοσειρά

Τώρα αφού γνωρίζουμε το μήκος των αριθμών σε σειρά, θα αντικαταστήσουμε αυτή τη συνάρτηση στο LEFT.
Δεδομένου ότι έχουμε τη συμβολοσειρά μας ένα A11:

Τύπος για την εξαγωγή αριθμών από το ΑΡΙΣΤΕΡΟ

= ΑΡΙΣΤΕΡΑ (A11, SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))

Εξαγωγή τμήματος κειμένου από συμβολοσειρά

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

Τύπος για εξαγωγή κειμένου από δεξιά

= ΔΕΞΙΑ (A11, LEN (A2) -SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6 "," 7 "," 8 "," 9 "}," "))))

Πως δουλεύει
Το κύριο μέρος και στους δύο τύπους είναι SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) που υπολογίζει την πρώτη εμφάνιση ενός αριθμού. Μόνο αφού το βρούμε αυτό, είμαστε σε θέση να χωρίσουμε κείμενο και αριθμό χρησιμοποιώντας τη λειτουργία ΑΡΙΣΤΕΡΑ. Ας το καταλάβουμε λοιπόν.

• • • ΑΝΤΙΚΑΤΑΣΤΑΣΗ (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Αυτό το μέρος επιστρέφει έναν πίνακα συμβολοσειράς στο A11 αφού αντικαταστήσει αυτούς τους αριθμούς με τίποτα/κενό (""). Για 27 Μόντα θα επιστρέψει {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
    • LEN (ΑΝΤΙΚΑΤΑΣΤΑΣΗ (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Τώρα το τμήμα SUBSTITUTE είναι τυλιγμένο με τη λειτουργία LEN. Αυτό το μήκος επιστροφής κειμένων σε πίνακα που επιστρέφεται από τη συνάρτηση SUBSTITUTE. Ως αποτέλεσμα, θα έχουμε {7,7,6,7,7,7,7,6,7,7}.
    • LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," ")): Εδώ αφαιρούμε κάθε αριθμό που επιστρέφεται κατά το παραπάνω μέρος από το μήκος της πραγματικής συμβολοσειράς. Το μήκος του αρχικού κειμένου είναι 7. Επομένως θα έχουμε {7-7,7-7,7-6,….}. Τέλος θα έχουμε {0,0,1,0,0,0,0,0,1,0,0}.
    • SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Εδώ χρησιμοποιήσαμε το SUM για να αθροίσουμε τον πίνακα που επιστρέφεται από το παραπάνω μέρος της συνάρτησης. Αυτό θα δώσει 2. Ποιος είναι ο αριθμός των αριθμών σε συμβολοσειρά.

Τώρα χρησιμοποιώντας αυτό μπορούμε να εξαγάγουμε τα κείμενα και τον αριθμό και να τα χωρίσουμε σε διαφορετικά κελιά. Αυτή η μέθοδος θα λειτουργεί τόσο με κείμενο τύπου, όταν ο αριθμός είναι στην αρχή όσο και στο τέλος του. Απλώς πρέπει να χρησιμοποιήσετε σωστά τη ΛΕΥΤΕΡΑ και ΔΕΞΙΑ Λειτουργία.
Χρησιμοποιήστε τη λειτουργία SplitNumText για να διαχωρίσετε αριθμούς και κείμενα από μια συμβολοσειρά
Οι παραπάνω μέθοδοι είναι λίγο περίπλοκες και δεν είναι χρήσιμες όταν αναμειγνύονται κείμενο και αριθμοί. Για να χωρίσετε κείμενο και αριθμούς, χρησιμοποιήστε αυτήν τη συνάρτηση που ορίζεται από το χρήστη.

Σύνταξη:

= SplitNumText (συμβολοσειρά, op)

Σειρά: Το String που θέλετε να χωρίσετε.
Op: αυτό είναι boolean. Πάσο 0 ή ψευδής για να λάβετε μέρος κειμένου. Για αριθμητικό μέρος, περάστε αληθής ή οποιοδήποτε αριθμό μεγαλύτερο από 0.
Για παράδειγμα, εάν η συμβολοσειρά είναι σε A20, τότε,
Ο τύπος για την εξαγωγή αριθμών από συμβολοσειρά είναι:

= SplitNumText (A20,1)

Και
Ο τύπος για την εξαγωγή κειμένου από συμβολοσειρά είναι:

= SplitNumText (A20,0)

Αντιγράψτε τον παρακάτω κώδικα στη μονάδα VBA για να λειτουργήσει ο παραπάνω τύπος.

Συνάρτηση SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Then num = num & Mid (str, i , 1) Άλλο txt = txt & Mid (str, i, 1) Τέλος αν Επόμενο i Αν op = True Τότε SplitNumText = num Άλλο SplitNumText = txt Τέλος Αν Λειτουργία Τέλους 

Αυτός ο κώδικας απλώς ελέγχει κάθε χαρακτήρα σε συμβολοσειρά, αν είναι αριθμός ή όχι. Εάν είναι ένας αριθμός τότε αποθηκεύεται σε num μεταβλητή αλλού σε μεταβλητή txt. Εάν ο χρήστης περάσει true για το op τότε το num επιστρέφεται αλλιώς επιστρέφει το txt.

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

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

Κάντε κλικ στον παρακάτω σύνδεσμο για να κατεβάσετε το αρχείο εργασίας:

Διαχωρισμός αριθμού και κειμένου από ένα κελί

Δημοφιλή άρθρα:
50 Συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας
Η συνάρτηση VLOOKUP στο Excel
COUNTIF στο Excel 2016
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel