Γενική φόρμουλα
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Περίληψη
Για να διαχωρίσετε κείμενο με οριοθέτη και να μετατρέψετε το αποτέλεσμα σε πίνακα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση FILTERXML με τη βοήθεια των λειτουργιών SUBSTITUTE και TRANSPOSE. Στο παράδειγμα που εμφανίζεται, ο τύπος στο D5 είναι:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Σημείωση: Το FILTERXML δεν είναι διαθέσιμο στο Excel σε Mac ή στο Excel Online.
Σημείωση: Έμαθα αυτό το τέχνασμα από τον Bill Jelen σε ένα βίντεο MrExcel.
Εξήγηση
Το Excel δεν έχει μια συνάρτηση αφιερωμένη στη διαίρεση κειμένου σε έναν πίνακα, παρόμοια με τη συνάρτηση PHP explode ή τη μέθοδο διαχωρισμού Python Ως λύση, μπορείτε να χρησιμοποιήσετε τη συνάρτηση FILTERXML, αφού πρώτα προσθέσετε τη σήμανση XML στο κείμενο.
Στο παράδειγμα που εμφανίζεται, έχουμε αρκετές συμβολοσειρές κειμένου οριοθετημένες με κόμματα όπως αυτή:
"Jim,Brown,33,Seattle,WA"
Ο στόχος είναι να χωριστούν οι πληροφορίες σε ξεχωριστές στήλες χρησιμοποιώντας το κόμμα ως οριοθέτης.
Η πρώτη εργασία είναι να προσθέσετε σήμανση XML σε αυτό το κείμενο, έτσι ώστε να μπορεί να αναλυθεί ως XML με τη συνάρτηση FILTERXML. Θα κάνουμε αυθαίρετα κάθε πεδίο στο κείμενο ένα στοιχείο, που περικλείεται με ένα γονικό στοιχείο. Ξεκινάμε με τη συνάρτηση SUBSTITUTE εδώ:
SUBSTITUTE(B5,",","")
Το αποτέλεσμα από το SUBSTITUTE είναι μια συμβολοσειρά κειμένου ως εξής:
"JimBrown33SeattleWA"
Για να διασφαλίσουμε καλά σχηματισμένες ετικέτες XML και για να περιτυλίξουμε όλα τα στοιχεία σε ένα γονικό στοιχείο, προετοιμάζουμε και προσθέτουμε περισσότερες ετικέτες XML όπως αυτό:
""&SUBSTITUTE(B5,",","")&""
Αυτό αποδίδει μια συμβολοσειρά κειμένου όπως αυτή (προστίθενται διαλείμματα γραμμής για αναγνωσιμότητα)
" Jim Brown 33 Seattle WA "
Αυτό το κείμενο παραδίδεται απευθείας στη συνάρτηση FILTERXML ως όρισμα xml, με έκφραση Xpath του "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Το Xpath είναι μια γλώσσα ανάλυσης και το "// y" επιλέγει όλα τα στοιχεία. Το αποτέλεσμα από το FILTERXML είναι ένας κάθετος πίνακας ως εξής:
("Jim";"Brown";33;"Seattle";"WA")
Επειδή θέλουμε έναν οριζόντιο πίνακα σε αυτήν την περίπτωση, τυλίγουμε τη συνάρτηση TRANSPOSE γύρω από το FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Το αποτέλεσμα είναι ένας οριζόντιος πίνακας ως εξής:
("Jim","Brown",33,"Seattle","WA")
που χύνεται στην περιοχή D5: H5 στο Excel 365.
