Παίζοντας με τις λέξεις

Στο σημερινό σημείωμα θα ασχοληθούμε με συναρτήσεις φύλλου που μπορούν να αποσπάσουν μία ή περισσότερες λέξεις από ένα κείμενο που είναι γραμμένο σε ένα κελί.
Στο κελί Α1 είναι γραμμένη η πρόταση “’Ένα Δύο Τρία Τέσσερα Πέντε Έξι Επτά Οκτώ Εννέα Δέκα Έντεκα Δώδεκα Δεκατρία Δεκατέσσερα Δεκαπέντε Δεκαέξι Δεκαεπτά Δεκαοκτώ Δεκαεννέα Είκοσι”. Είναι μια πρόταση 20 λέξεων, που δεν εκφράζει κάποιο σπουδαίο διανόημα, αλλά θα βοηθήσει να γίνουν κατανοητά τα παραδείγματα.

 
Α) Ας δούμε πρώτα ένα τύπο που μπορεί να μετρήσει το πλήθος των λέξεων. Ο τύπος:
=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))+1
Θα επιστρέψει σωστά: 20 για το κείμενό μας στο κελί Α1.
Η συνάρτηση LEN(A1) μετρά το πλήθος των χαρακτήρων του κελιού Α1,γράμματα, αριθμούς και κενά που είναι 139 και η συνάρτηση LEN(SUBSTITUTE(A1;» «;»»)) ξαναμετρά το πλήθος την χαρακτήρων χωρίς τα κενά, τα οποία απαλείφει η  SUBSTITUTE(A1;» «;»»), που είναι 120. Η διαφορά των δύο LEN (139-120) θα μετρήσει ουσιαστικά τα κενά που υπάρχουν ανάμεσα στις λέξεις και το πλήθος των λέξεων θα είναι κατά μία περισσότερες από τα κενά. Έτσι καταλήγουμε στον παραπάνω τύπο. Γίνεται φανερό ότι για να επιστέψει σωστά αποτελέσματα ο τύπος δεν πρέπει να υπάρχουν επί πλέον άσκοπα κενά στο κείμενό μας, ούτε στην αρχή, ούτε στο τέλος ούτε ανάμεσα στις λέξεις. Επειδή όμως δεν μπορούμε να είμαστε σίγουροι ή επειδή ίσως τα επί πλέον κενά έχουν γραφτεί για κάποιο λόγο, αναθέτουμε στη συνάρτηση TRIM να καθαρίσει όλα τα άσκοπα (i) κενά, πριν η LEN κάνει τους υπολογισμούς της, και η συνάρτηση παίρνει τη μορφή :  (ii)
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1;" ";""))+1
Σε όλους τους παρακάτω τύπους και όπου υπάρχει κίνδυνος λανθασμένου αποτελέσματος από τα επιπλέον κενά, στη θέση του ορίσματος Α1, θα γίνεται χρήση του ορίσματος TRIM(A1).

Β) Συναρτήσεις για την επιστροφή της πρώτης λέξης.
Στη συνάρτηση:
=LEFT(TRIM(A1);FIND(" ";TRIM(A1))-1)
Η FIND εντοπίζει τη θέση (τάξη) του πρώτου κενού και η LEFT επιστέφει τους χαρακτήρες που βρίσκονται αριστερά αυτής της θέσης, άρα την πρώτη λέξη. Στο παράδειγμά μας η:  FIND(» «; A1) ή όπως είπαμε, πιο καλά η: FIND(» «;TRIM(A1)) επιστρέφει 4 και η:  LEFT(TRIM(A1);4-1) θα επιστρέψει τους 3 πρώτους χαρακτήρες, δηλαδή την πρώτη λέξη “’Ένα”.
Η συνάρτηση αυτή όμως θα αποτύχει αν η πρόταση στο κελί Α1 αποτελείται από μία λέξη, διότι η:  FIND(» «; A1) δεν θα μπορεί να εντοπίσει κενό (» «). Για να εξασφαλιστούμε δεν έχουμε παρά να προσθέσουμε μόνοι μας αυτό το κενό! Μην τρομάζετε! Δεν θα το διορθώσουμε στις εγγραφές, απλά θα προσθέσουμε το κενό μέσα από την συνάρτηση. Διορθώνουμε το όρισμα:  FIND(» «;TRIM(A1)) ως εξής: FIND(» «;TRIM(A1)&» «) Προσθέτουμε δηλαδή στο τέλος του κειμένου του κελιού Α1 ένα κενό. Έτσι αν το κείμενο αποτελείται από μία λέξη η FIND δεν θα αποτύχει και ο τύπος μας παίρνει την τελική του μορφή:
 =LEFT(TRIM(A1);FIND(" ";TRIM(A1)&" ")-1)
Γ) Συναρτήσεις για την επιστροφή της τελευταίας λέξης.
=RIGHT(" "&TRIM(A1);LEN(" "&TRIM(A1))-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);LEN(" "&TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1);" ";"")))))
Στη συνάρτηση, αντί να αναφερόμαστε στο ίδιο το κείμενο του κελιού Α1, αναφερόμαστε στη καθαρή, χωρίς άσκοπα κενά (τριμαρισμέμη) μορφή στην οποία προσθέτουμε ένα κενό στην αρχή για να καλύψουμε το ενδεχόμενο το κείμενο να αποτελείται από μία λέξη, δηλαδή αντί για Α1 χρησιμοποιούμε   » «&TRIM(A1). Στη συνέχεια θα εντοπιστεί ο αριθμός του τελευταίου κενού, δηλαδή του κενού μεταξύ των δύο τελευταίων λέξεων που είναι ο αριθμός του πλήθους των κενών. Αυτό θα το κάνει το όρισμα: LEN(» «&TRIM(A1))-LEN(SUBSTITUTE(» «&TRIM(A1);» «;»»)) που στο παράδειγμά μας θα επιστρέψει 20. Κατόπιν με το όρισμα SUBSTITUTE(» «&LEN(A1);» «;CHAR(1);20) αντικαθιστούμε το τελευταίο αυτό κενό (εικοστό) με τον χαρακτήρα  CHAR(1). Δεν έχουμε παρά να ζητήσουμε με την RIGHT να μας επιστραφούν οι χαρακτήρες που βρίσκονται δεξιότερα του  CHAR(1).
Δέστε αν θέλετε τα σταδιακά βήματα δημιουργίας του τύπου στο excel βιβλίο: extract_text_functions
Με παρόμοια λογική έχει γραφτεί και ο παρακάτω τύπος που χρησιμοποιεί την MID αντί για την RIGHT
 
=MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);LEN(" "&TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1);" ";""))));1024)
 Δ) Μια διαφορετική προσέγγιση.
Παρατηρήστε τις συναρτήσεις:
=TRIM(LEFT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";LEN(TRIM(A1))));LEN(TRIM(A1))))
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";LEN(TRIM(A1))));LEN(TRIM(A1))))
Ο πρώτος επιστρέφει την πρώτη λέξη και ο δεύτερος την τελευταία λέξη του κειμένου του κελιού Α1.
Να δούμε πως δουλεύουν αυτές οι συναρτήσεις. Αρχικά απαλείφουμε με την TRIM τα άχρηστα κενά από το κείμενο στο κελί Α1. Στην συνέχεια με την SUBSTITUTE αντικαθιστούμε τα κενά μεταξύ των λέξεων με….κενά, πολλά κενά, τόσα πολλά όσοι είναι οι χαρακτήρες του κειμένου. Έτσι αν το αρχικό μας κείμενο ήταν ας πούμε ΕΝΑ ΔΥΟ ΤΡΙΑ, τώρα το κείμενο έχει…αραιώσει και έχει γίνει:
ΕΝΑ            ΔΥΟ            ΤΡΙΑ . Ανάμεσα σε κάθε λέξη υπάρχουν 12 κενά. Τα μέτρησε η LEN(TRIM(A1)) και η REPT(» «;12)) τα επανάλαβε τις σωστές φορές.
Αν τώρα πάρουμε με την LEFT  12 χαρακτήρες από την αρχή, θα έχουμε πάρει σίγουρα την πρώτη λέξη διότι η πρώτη λέξη θα έχει λιγότερους από 12 χαρακτήρες (ή το πολύ 12 αν το κείμενο αποτελείται από μία λέξη) και μαζί θα πάρουμε ίσως και μερικά κενά, τα οποία για να τα διώξουμε, καλούμε ξανά την TRIM.
Αν πάρουμε με την RIGHT 12 χαρακτήρες από το τέλος, θα μας επιστραφεί σίγουρα η τελευταία λέξη παρέα ίσως με μερικά κενά τα οποία πάλι θα απαλείψει η TRIM.
Παρακολουθήστε τα σταδιακά βήματα κατασκευής των τύπων στο excel βιβλίο: extract_text_functions
 Για τον χαρακτήρα του κενού, αντί για » » μπορούμε να χρησιμοποιήσουμε την συνάρτηση CHAR(32) και οι τύποι γίνονται:
=TRIM(LEFT(SUBSTITUTE(TRIM(A1);CHAR(32);REPT(CHAR(32);LEN(TRIM(A1))));LEN(TRIM(A1))))
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1);CHAR(32);REPT(CHAR(32);LEN(TRIM(A1))));LEN(TRIM(A1))))
Τη μέθοδο αυτή μπορούμε να χρησιμοποιήσουμε και για κείμενα που δεν είναι διαχωρισμένα με κενό αλλά με οποιοδήποτε άλλο χαρακτήρα.
Παράδειγμα, ο παρακάτω τύπος επιστρέφει το όνομα αρχείου, από την πλήρη (ή τη μερική) διαδρομή του.
=TRIM(RIGHT(SUBSTITUTE(A1;"\";REPT(CHAR(32);LEN(A1)));LEN(A1)))
Ε) Συναρτήσεις για την επιστροφή της ν-οστής λέξης.
Όλοι οι παρακάτω τύποι θα επιστρέψουν την ν-οστή λέξη του κειμένου. Αν στο κελί Β1 γράψουμε 8, θα μας επιστραφεί η 8η λέξη από το κείμενο του κελιού Α1.
Για τιμές που ξεπερνούν το πλήθος των λέξεων ή για αρνητικές τιμές στο κελί Β1, οι συναρτήσεις θα επιστρέψουν το σφάλμα  #ΤΙΜΗ!, πλην της 4ης  που θα επιστρέψει κενό. Η πολύ κομψή και σύντομη  αυτή συνάρτηση έχει γραφτεί με τη λογική της παραγράφου Δ (Μια διαφορετική προσέγγιση). 
=LEFT(MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);B1));1024)&" ";FIND(" ";MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);B1));1024)&" ")-1)
=LEFT(RIGHT(TRIM(A1);LEN(TRIM(A1))+1-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);B1)))&" ";FIND(" ";RIGHT(TRIM(A1);LEN(TRIM(A1))+1-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);B1)))&" ")-1)
=MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);B1));FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);B1+1))-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);B1))-1)
=TRIM(MID(SUBSTITUTE(" "&TRIM(A1);" ";REPT(" ";1024));B1*1024;1024))
ΣΤ) …και άλλες πολλές συναρτήσεις….
Για την επιστροφή της ν-οστής λέξη και μετά:
=MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);B1));1024)
=RIGHT(TRIM(A1);LEN(TRIM(A1))+1-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1);" ";CHAR(1);B1)))
Για την επιστροφή των ν πρώτων λέξεων:
=LEFT(SUBSTITUTE(TRIM(A1)&" ";" ";CHAR(1);B1);FIND(CHAR(1);SUBSTITUTE(TRIM(A1)&" ";" ";CHAR(1);B1))-1)
=MID(SUBSTITUTE(TRIM(A1)&" ";" ";CHAR(1);B1);1;FIND(CHAR(1);SUBSTITUTE(TRIM(A1)&" ";" ";CHAR(1);B1))-1)
Για την επιστροφή των ν τελευταίων λέξεων:
=RIGHT(TRIM(A1);LEN(TRIM(A1))+1-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);LEN(TRIM(A1))+2-LEN(SUBSTITUTE(TRIM(A1);" ";""))-B1)))
=MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);LEN(TRIM(A1))+2-LEN(SUBSTITUTE(TRIM(A1);" ";""))-B1));1024)
Για την επιστροφή της ν-οστής λέξη μετρώντας από το τέλος:
=LEFT(RIGHT(TRIM(A1);LEN(TRIM(A1))+1-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);LEN(TRIM(A1))+2-LEN(SUBSTITUTE(TRIM(A1);" ";""))-B1)))&" ";FIND(" ";RIGHT(TRIM(A1);LEN(TRIM(A1))+1-FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);LEN(TRIM(A1))+2-LEN(SUBSTITUTE(TRIM(A1);" ";""))-B1)))&" ")-1)
=LEFT(MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);LEN(TRIM(A1))+2-LEN(SUBSTITUTE(TRIM(A1);" ";""))-B1));1024)&" ";FIND(" ";MID(TRIM(A1);FIND(CHAR(1);SUBSTITUTE(" "&TRIM(A1)&" ";" ";CHAR(1);LEN(TRIM(A1))+2-LEN(SUBSTITUTE(TRIM(A1);" ";""))-B1));1024)&" ")-1)
Για την επιστροφή μ λέξεων, ξεκινώντας από την ν-οστή (ο αριθμός ν στο κελί Β1 και ο αριθμός μ στο κελί C1):
=TRIM(MID(SUBSTITUTE(" "&TRIM(A1);" ";REPT(" ";1024));B1*1024;C1*1024))
Για την επιστροφή από τη ν-οστή (κελί Β1) έως την μ-οστή (κελί C1):
=TRIM(MID(SUBSTITUTE(" "&TRIM(A1);" ";REPT(" ";1024));B1*1024;(C1-B1+1)*1024))
 
Οι 2 τελευταίες συναρτήσεις έχουν γραφτεί με την λογική που περιγράψαμε στην παράγραφο Δ: “Μια διαφορετική προσέγγιση”. Με την ίδια λογική μπορούν να γραφούν πιο κομψά μερικές από τις παραπάνω συναρτήσεις. Δοκιμάστε το.

 Όλες τις συναρτήσεις μπορείτε να βρείτε στο excel βιβλίο: extract_text_functions

 
(i)  Η LEN δεν απαλείφει το άσκοπο κενό μεταξύ του τέλους μιας λέξης και ενός σημείου στίξεως. Παράδειγμα, την πρόταση:  Ω , τι κόσμος μπαμπά ! δεν θα την διορθώσει σε:  Ω, τι κόσμος μπαμπά!      
(ii) Στη δεύτερη LEN δεν χρειάζεται η TRIM διότι η SUBSTITUTE(A1;» «;»») θα απαλείψει όλα τα κενά. Να διευκρινίσουμε: η SUBSTITUTE(A1;» «;»») απαλείφει όλα τα κενά, ενώ η TRIM(A1) απαλείφει μόνο τα άσκοπα κενά, δηλαδή όλα τα κενά στην αρχή και το τέλος της πρότασης και τα  επί πλέον κενά ανάμεσα στις λέξεις.
 
This entry was posted in excel, Συναρτήσεις φύλλου and tagged , , , , , . Bookmark the permalink.

3 Responses to Παίζοντας με τις λέξεις

  1. Ο/Η sophia_ana λέει:

    Καλημέρα.
    Έχω ένα πολύ μεγάλο πίνακα με πελάτες που για κάποιο λόγο που δεν γνωρίζω, το όνομα και το επίθετο του κάθε πελάτη είναι στο ίδιο κελί, χωρίς κενό ανάμεσα πχ ΤάκηςΠέτρου.
    Θέλω να τα χωρίσω και σκεφτόμουν αν μπορούσα με κάποια συνάρτηση να βρω τη θέση του 2ου κεφαλαίου γράμματος, μετά να χρησιμοποιήσω τις συναρτήσεις LEFT και RIGHT.
    Μπορεί να γίνει αυτό; ή κάποια άλλη βοήθεια ; Σας ευχαριστώ
    Σοφία

  2. Ο/Η vioannis λέει:

    Πολύ σωστά σκέφτηκες Σοφία. Αυτή είναι η λύση. Η συνάρτηση που ζητάς και που εντοπίζει τη θέση του δεύτερου κεφαλαίου γράμματος, είναι:

     
    =1+MATCH(FALSE;EXACT(LOWER(MID(A1;ROW(INDIRECT("2:"&LEN(A1)));1));MID(A1;ROW(INDIRECT("2:"&LEN(A1)));1));0)
    

    Πρόσεχε είναι συνάρτηση πίνακας και εισάγεται με Ctrl+Shift+Enter.
    Η συνάρτηση αυτή θα σου δώσει 6 για το όνομα ΤάκηςΠέτρου, 7 για την ΙωάνναΆλλη, 3 για τον J.Smith κτλ
    Επειδή ίσως στη λίστα σου θα υπάρχουν και εγγραφές με δύο μικρά ονόματα ή διπλά επίθετα ή άλλες ειδικές περιπτώσεις και αυτά δεν τα καλύπτει η παραπάνω συνάρτηση, σου γράφω και μία συνάρτηση που μετρά το πλήθος των κεφαλαίων γραμμάτων σε μία εγγραφή(κελί). Με αυτή τη συνάρτηση μπορείς να εντοπίσεις και να χειριστείς κατά περίπτωση τις εγγραφές αυτές.

      
    =SUMPRODUCT(1-EXACT(LOWER(MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1));MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1)))
    

    Οι παραπάνω συναρτήσεις δουλεύουν και με την ελληνικό και την αγγλικό πληκτρολόγιο αλλά όχι με το ελληνικό πολυτονικό.
    Για τις συναρτήσεις – πίνακες ή Ctrl+Shift+Enter συναρτήσεις θα βρεις αναλυτικές οδηγίες στο αντίστοιχο σημείωμα του ιστολογίου. Καλή επιτυχία.

  3. Ο/Η sophia_ana λέει:

    Είστε θαυματοποιός! Σε λιγότερο από 20 λεπτά είχα ξεμπερδέψει. Ευχαριστώ πολύ

Τα σχόλια είναι απενεργοποιημένα.