Formula generică
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
rezumat
Dacă aveți nevoie să extrageți numele de familie dintr-un nume complet, puteți face acest lucru cu această formulă destul de complexă care folosește mai multe funcții. În forma generică a formulei (de mai sus), numele este un nume complet, cu un spațiu care separă prenumele de alte părți ale numelui.
În exemplu, celula activă conține această formulă:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Explicaţie
La bază, această formulă folosește funcția DREAPTA pentru a extrage caractere începând din dreapta. Celelalte funcții care alcătuiesc partea complexă a acestei formule fac doar un lucru: calculează câte caractere trebuie extrase.
La un nivel ridicat, formula înlocuiește ultimul spațiu din nume cu un asterisc „*” și apoi folosește FIND pentru a determina poziția asteriscului în nume. Poziția este utilizată pentru a stabili câte caractere trebuie extrase cu DREAPTA.
Cum înlocuiește funcția doar ultimul spațiu? Aceasta este partea inteligentă.
Închideți-vă, explicația devine puțin tehnică.
Cheia acestei formule este acest pic:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Ceea ce face înlocuirea efectivă a ultimului spațiu cu „*”.
SUBSTITUTE are un al patrulea argument (opțional) care specifică care „instanță” a textului de căutare trebuie înlocuită. Dacă nu este furnizat nimic pentru acest argument, toate instanțele sunt înlocuite. Cu toate acestea, dacă, să spunem că numărul 2 este furnizat, doar a doua instanță este înlocuită. În fragmentul de mai sus, instanța este calculată folosind al doilea SUBSTITUT:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Aici, lungimea numelui fără spații este scăzută din lungimea reală a numelui. Dacă există un singur spațiu în nume, acesta produce 1. Dacă există două spații, rezultatul este 2 și așa mai departe.
În numele de exemplu din B4, există două spații în nume, astfel încât să obținem:
15 - 13 = 2
Și doi este folosit ca în numărul instanței:
SUBSTITUTE(B4," ","*",2)
care înlocuiește al doilea spațiu cu „*”. Numele arată astfel:
„Susan Ann * Chang”
Funcția FIND preia apoi rolul pentru a afla unde este „*” în nume:
FIND("*", "Susan Ann*Chang")
Rezultatul este 10 (* este în poziția a 10-a) care se scade din lungimea totală a numelui:
LEN(B4)-10
Deoarece numele este de 15 caractere, avem:
15-10 = 5
Numărul 5 este folosit de DREAPTA astfel:
=RIGHT(B4,5)
Ceea ce are ca rezultat „Chang”
După cum puteți vedea, este mult de lucru mai sus să calculați acel 5 simplu!
Manipularea spațiilor inconsistente
Spațiile suplimentare vor cauza probleme cu această formulă. O soluție este să folosiți mai întâi funcția TRIM pentru a curăța lucrurile, apoi să utilizați formula de analiză.
