Łączenie pierwszych dwóch liter z imienia i pierwszych dwóch liter z nazwiska


9

Mam arkusz kalkulacyjny nazw użytkowników.

Imiona i nazwiska znajdują się w tej samej komórce kolumny A.

Czy istnieje formuła, która połączy dwie pierwsze litery imienia (pierwsze słowo) i dwie pierwsze litery nazwiska (drugie słowo)?

Na przykład John Doepowinien zostać JoDo.

próbowałem

=LEFT(A1)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,IFERROR(FIND(" ",SUBSTITUTE(A1," ","",1)),LEN(A1))-IFERROR(FIND(" ",A1),LEN(A1)))

ale to daje mi JoDoewynik.



Czy któraś z odpowiedzi rozwiązuje problem? (Jeśli tak, czy możesz oznaczyć go jako Odpowiedź, klikając znacznik wyboru po lewej stronie wpisu).
BruceWayne

Odpowiedzi:


15

Tak; zakładając, że każda osoba ma tylko imię i nazwisko, a to zawsze jest oddzielone spacją, której możesz użyć poniżej:

=LEFT(A1,2)&MID(A1,SEARCH(" ",A1)+1,2)

Mógłbym oprzeć tę odpowiedź tylko na tych założeniach, ponieważ jest to wszystko, co podałeś.

Lub jeśli chcesz, aby miejsce było nadal uwzględniane:

=LEFT(A1,2)&" "&MID(A1,SEARCH(" ",A1)+1,2)

2
@prweq no probs, zaakceptuj to jako poprawne, jeśli działa dla ciebie
PeterH

Najprostsze i najłatwiejsze do zrozumienia rozwiązanie. (Cóż, z wyjątkiem użycia FIND()zamiast SEARCH();-)) Ponieważ =LEFT(A1,2)&MID(A1,SEARCH(" ",A1),3)
zakładasz,

4
@RajeshS Wiem. Dlatego moja odpowiedź zaczyna się odassuming each person only has a First and Last name
PeterH,

2
Programiści fałszu wierzą w nazwiska ; nie żeby sensowne było załatwianie wszystkich tych spraw od samego początku, jeśli twoje oprogramowanie nie ma zasięgu międzynarodowego
Aaron,

1
Sugeruję TRIM(LEFT(A1,2))na wypadek, gdyby ich imię zawierało tylko jedną literę, ale może być również łatwe ręczne sprawdzenie tych specjalnych przypadków.
Engineer Toast,

11

Podsumowując, oto rozwiązanie, które zwróci pierwsze dwa znaki imienia i pierwsze dwa znaki nazwiska, ale także uwzględni drugie imię.

=LEFT(A1,2)&LEFT(MID(A1,FIND("~~~~~",SUBSTITUTE(A1," ","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)),2)

wprowadź opis zdjęcia tutaj

Dzięki @Kyle za główną część formuły


1
Pobiłeś mnie do tego ;-) (Było już dobrze po północy i muszę zasnąć - planowałem dodać to do mojej odpowiedzi później). Niewielkim ulepszeniem twojej formuły byłoby użycie jednego ~zamiast czterech. Jeśli obawiasz się, że Tilda może być używana jako część nazwy (!) Lub, co bardziej prawdopodobne, została przypadkowo wpisana, po prostu użyj znaku, który nie pojawia się na żadnej klawiaturze. Wolę używać §. jest kolejnym dobrym.
robinCTS,

@BruceWayne ,, Twoja Formuła jest bardziej kompetentna ,,, ponieważ ma dostęp tylko do Firts i nazwisko ignoruje drugie imię ,,, W górę głosował ☺
Rajesh S

@RajeshS Co z ludźmi o hiszpańskich nazwiskach , takimi jak były kierowca F1 Fernando Alonso Díaz, który ma 1 imię, 2 nazwiska i nie ma drugich imion? (Czy nawet król Felipe Juan Pablo Alfonso de Todos los Santos de Borbón y de Grecia ?)
Chronocidal

2
@Chronocidal teraz schodzimy do króliczej dziury tego, co programiści wierzą w nazwy
BruceWayne

1
@robinCTS - Dobra rozmowa! Otrzymałem formułę z innej odpowiedzi (zredagowanej w) i tak naprawdę nie kopałem zbyt głęboko. Dzięki za wskazówki!
BruceWayne,

9

To inny sposób ...

Zrzut ekranu z arkusza roboczego

  • Imię
  • B - =CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))

Możesz pójść jeszcze dalej i usunąć wszystkie te dodatkowe kolumny, łącząc je=CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))
PeterH

Tak, zrobiłem to kilka chwil później!
Stese,

tak, zauważyłem, że zredagowałeś odpowiedź, Nice Answer!
PeterH,

5

Po pierwsze, chciałbym powiedzieć, że odpowiedź PeterH jest najprostsza i najłatwiejsza do zrozumienia. (Chociaż wolę używać FIND()zamiast SEARCH()- wpisanie dwóch mniej znaków pomaga uniknąć RSI ;-))

Alternatywna odpowiedź, która ani nie używa MID(), LEFT()ani RIGHT()zamiast tego wykorzystuje REPLACE()do usunięcia niechcianych części nazwy, jest następująca:

=REPLACE(REPLACE(A1,FIND(" ",A1)+3,LEN(A1),""),3,FIND(" ",A1)-2,"")

Wyjaśnienie:

Wewnętrzne REPLACE(A1, FIND(" ",A1)+3, LEN(A1), "")usuwa znaki od trzeciego znaku nazwiska, a zewnętrzne usuwa znaki od trzeciego znaku imienia do spacji włącznie.REPLACE(inner_replace, 3, FIND(" ",A1)-2, "")


Dodatek 1:

Powyższą formułę można również dostosować, aby umożliwić utworzenie jednego drugiego imienia:

=REPLACE(REPLACE(A1,IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))+3,LEN(A1),""),3,IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))-2,"")

zastępując FIND(" ",A1)z IFERROR(FIND(" ",A1,FIND(" ",A1)+1), FIND(" ",A1)).

FIND(" ", A1, FIND(" ",A1)+1)znajduje drugą spację (rozpoczynając wyszukiwanie spacji po pierwszej spacji) lub w przeciwnym razie popełnia błędy. znajdzie pierwszą spację, jeśli nie ma drugiej spacji.
IFERROR(find_second_space, FIND(" ",A1))


Ta wersja (z długim nawinięciem) pozwala na dowolną liczbę drugich imion:

=REPLACE(REPLACE(A1,FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+3,LEN(A1),""),3,FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-2,"")

W takim przypadku FIND(" ",A1)jest zastąpiony przez FIND("§", SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))).

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))liczy liczbę spacji. zamienia ostatnią spację na . znajduje pierwszą, która jest taka sama jak znalezienie ostatniej spacji.
SUBSTITUTE(A1, " ", "§", count_of_spaces)§
FIND("§", last_space_replaced_string)§

( §Oczywiście można je zastąpić dowolnym znakiem, który nie istnieje w ciągu pełnej nazwy. Można by użyć bardziej ogólnej, bezpieczniejszej alternatywy CHAR(1)).


Zauważ, że oczywiście odpowiedź BruceWayne jest najprostszym i najłatwiejszym do zrozumienia rozwiązaniem, które pozwala na dowolną liczbę drugich imion. Tak było. Dopóki nie opublikowałem innej odpowiedzi , to znaczy ;-)


Dodatek 2:

Wszystkie rozwiązania można dostosować tak, aby uwzględniały tylko jedną nazwę (jeśli wymagany jest wynik czterech znaków), zawijając je w IFERROR()funkcji takiej jak ta:

=IFERROR(solution, alternate_formula)

Zauważ, że powyższe jest ogólną formułą przypadku i może być możliwe dokonanie bardziej wydajnej modyfikacji konkretnego rozwiązania. Na przykład, jeśli w przypadku pojedynczego imienia wymagane jest połączenie dwóch pierwszych liter z dwiema ostatnimi literami, odpowiedź PeterH można skuteczniej dostosować w ten sposób:

=LEFT(A1,2)&MID(A1,IFERROR(SEARCH(" ",A1)+1,LEN(A1)-1),2)


Aby uwzględnić przypadek pojedynczego imienia lub inicjału (zakładając, że spacja lub kropka nie są dopuszczalne jako drugi znak), można zastosować następujące rozwiązanie w przypadku dowolnego rozwiązania:

=SUBSTITUTE(SUBSTITUTE(solution, " ", single_char), ".", single_char))

Pamiętaj, że pojedynczy znak może być zakodowany na stałe lub obliczony na podstawie nazwy. (Lub użyj, ""aby usunąć spację lub kropkę.)


Na koniec, jeśli naprawdę potrzebujesz uwzględnić przypadek, w którym pełna nazwa jest tylko jednym znakiem (!), Po prostu owiń formułę zawierającą tylko jedną nazwę inną IFERROR() . (Zakładając oczywiście, że alternatywna formuła nie zajmuje się tym szczególnym przypadkiem).


Dodatek 3:

Wreszcie, wreszcie (nie, naprawdę * ;-)), aby obsłużyć wiele kolejnych i / lub wiodących / końcowych spacji, użyj TRIM(A1)zamiast A1.


* Opuszczę skrzynkę dla pojedynczego nazwiska, takiego jak pan T, jako ćwiczenie dla czytelnika.

Wskazówka: =solution &IF(MID(A1,LEN(A1)-1,1)=" ", single_char, "")


1
Jak zawsze twoje odpowiedzi wyglądają tak, jakby pochodziły z zaawansowanego podręcznika użytkownika programu Excel! To byłaby najlepiej głosowana odpowiedź, gdybyś opublikował ją wcześniej!
PeterH,

@PeterH Dzięki za uzupełnienie. Natknąłem się na to pytanie, gdy trafiło na listę Hot Network Questions, więc byłem trochę spóźniony na imprezę ;-)
robinCTS,

2

W oparciu o tę odpowiedź oto eleganckie rozwiązanie, które działa z dowolną liczbą drugich imion:

=LEFT(A1,2)&LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),2)

Wyjaśnienie:

SUBSTITUTE(A1, " ", REPT(" ",LEN(A1)))zastępuje spacje między wyrazami spacjami równymi liczbowo długości całego łańcucha. Użycie długości łańcucha zamiast arbitralnie dużej liczby gwarantuje, że formuła działa na łańcuch o dowolnej długości i oznacza, że ​​działa to skutecznie.

RIGHT(space_expanded_string, LEN(A1))wyodrębnia najbardziej prawe słowo poprzedzone wiązką spacji. *

TRIM(space_prepended_rightmost_word) wyodrębnia najbardziej prawe słowo.

LEFT(rightmost_word, 2) wyodrębnia dwa pierwsze znaki najbardziej wysuniętego w prawo słowa (nazwisko).


* Zastrzeżenie: Jeśli jest to możliwe, że nazwa zawiera spacje, należy wymienić pierwszy argument SUBSTITUTE(), to znaczy A1, ze TRIM(A1). Wiodące spacje i wiele kolejnych spacji między słowami są obsługiwane poprawnie tylko za pomocą A1.


Naprawianie twojej próby

Przy bliższym przyjrzeniu się próbowanemu rozwiązaniu wygląda na to, że byłeś bardzo blisko działającej formuły, która łączy dwie pierwsze litery pierwszego słowa (tj. Imię) i dwie pierwsze litery drugiego słowa, jeśli takie istniały.

Zauważ, że jeśli nazwa użytkownika miałaby zawierać drugie imiona, poprawiona formuła niepoprawnie przechwyciłaby dwie pierwsze litery z pierwszego drugiego imienia zamiast z nazwiska (zakładając, że naprawdę chcesz wyodrębnić je z nazwiska).

Ponadto, jeśli wszystkie nazwy użytkowników składają się tylko z imienia lub imienia i nazwiska, wówczas formuła jest niepotrzebnie skomplikowana i można ją uprościć.


Aby zobaczyć, jak działa formuła, a więc ją naprawić, łatwiej jest ją wstępnie ująć, na przykład:

=
LEFT(A1,2) &
MID(
  A1,
  IFERROR(FIND(" ",A1), LEN(A1)) + 1,
  IFERROR(
    FIND(" ", SUBSTITUTE(A1," ","",1)),
    LEN(A1)
  )
  - IFERROR(FIND(" ",A1), LEN(A1))
)


Aby zrozumieć, jak to działa, najpierw spójrz na to, co się dzieje, gdy A1nie zawiera spacji (tzn. Zawiera tylko jedną nazwę). Wszystkie IFERROR()funkcje oceniają na swoje drugie argumenty, ponieważ FIND()zwraca #VALUE!błąd, jeśli szukany ciąg nie został znaleziony w ciągu docelowym:

=
LEFT(A1,2) &
MID(
  A1,
  LEN(A1) + 1,
  LEN(A1)
  -LEN(A1)
)

Trzeci argument argumentu MID()jest równy zero, więc funkcja wyprowadza ""i wynikiem formuły są pierwsze dwa znaki pojedynczej nazwy.


Teraz spójrz na to, kiedy są dokładnie dwie nazwy (tj. Jest dokładnie jedna spacja). Pierwsza i trzecia IFERROR()funkcja oceniają na pierwsze argumenty, ale druga na drugi argument, ponieważ FIND(" ", SUBSTITUTE(A1," ","",1))próbuje znaleźć inną spację po usunięciu pierwszego i tylko jednego:

=
LEFT(A1,2) &
MID(
  A1,
  FIND(" ",A1) + 1,
  LEN(A1)
  - FIND(" ",A1)
)

Oczywiście MID()zwraca drugie słowo (tj. Nazwisko) w całości, a wynikiem formuły są pierwsze dwa znaki imienia, po których następują wszystkie znaki nazwiska.


Dla zachowania kompletności przyjrzymy się również przypadkowi, w którym istnieją co najmniej trzy nazwy, chociaż teraz powinno być dość oczywiste, jak naprawić formułę. Tym razem wszystkie IFERROR()funkcje oceniają na pierwsze argumenty:

=
LEFT(A1,2) &
MID(
  A1,
  FIND(" ",A1) + 1,
  FIND(" ", SUBSTITUTE(A1," ","",1))
  - FIND(" ",A1)
)

Jest nieco mniej wyraźny niż w poprzednim przypadku, ale MID()zwraca dokładnie całe drugie słowo (tj. Pierwsze drugie imię). Tak więc wynikiem formuły są pierwsze dwa znaki pierwszego imienia, po których następują wszystkie znaki pierwszego drugiego imienia.


Oczywiście poprawką jest użycie LEFT()dwóch pierwszych znaków MID()wyniku:

=
LEFT(A1,2) &
LEFT(
  MID(
    A1,
    IFERROR(FIND(" ",A1), LEN(A1)) + 1,
    IFERROR(
      FIND(" ", SUBSTITUTE(A1," ","",1)),
      LEN(A1)
    )
    - IFERROR(FIND(" ",A1), LEN(A1))
  ),
  2
)


Uproszczenie wspomniałem powyżej jest zastąpienie LEFT(MID(…,…,…), 2)z MID(…,…,2):

=
LEFT(A1,2) &
MID(
  A1,
  IFERROR(FIND(" ",A1), LEN(A1)) + 1,
  2
)

lub w jednej linii:

=LEFT(A1,2)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,2)

Jest to zasadniczo rozwiązanie PeterH zmodyfikowane tak, aby działało również z pojedynczymi nazwami (w takim przypadku wynikiem są tylko dwa pierwsze znaki nazwy).


Uwaga: wstępnie zapisane formuły faktycznie działają, jeśli zostaną wprowadzone.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.