wake-up-neo.net

Kombination der ersten beiden Buchstaben des Vornamens und der ersten beiden Buchstaben des Nachnamens

Ich habe eine Tabelle mit Benutzernamen.

Vor- und Nachname befinden sich in derselben Zelle der Spalte A.

Gibt es eine Formel, die die ersten beiden Buchstaben des Vornamens (erstes Wort) und die ersten beiden Buchstaben des Nachnamens (zweites Wort) verkettet?

Beispiel: John Doe sollte zu JoDo werden.

Ich habe es versucht

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

aber das gibt mir als Ergebnis JoDoe.

9
prweq

Ja; Angenommen, jede Person hat nur einen Vor- und Nachnamen. Dies wird immer durch ein Leerzeichen getrennt, das Sie wie folgt verwenden können:

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

Ich konnte diese Antwort nur auf diese Annahmen stützen, da dies alles ist, was Sie angegeben haben.

Oder wenn ein Leerzeichen noch enthalten sein soll:

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

Und um die Sache abzurunden, hier ist eine Lösung, die die ersten beiden Zeichen des Vornamens und die ersten beiden Zeichen des Nachnamens zurückgibt, aber berücksichtigt auch den zweiten Vornamen.

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

 enter image description here

Danke an @Kyle für den Hauptteil der Formel

11
BruceWayne

Dies ist ein anderer Weg ...

 Screenshot of worksheet

  • Ein Name
  • B - =CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))
9
Stese

Zunächst möchte ich sagen, dass die Antwort von PeterH am einfachsten und am einfachsten zu verstehen ist. (Obwohl ich FIND() anstelle von SEARCH() verwenden möchte - die Eingabe von zwei Zeichen weniger hilft, RSI zu vermeiden ;-))

Eine alternative Antwort, die weder MID(), LEFT() noch RIGHT() verwendet, sondern stattdessen REPLACE() verwendet, um unerwünschte Teile des Namens zu entfernen, lautet wie folgt:

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

Erklärung:

Die innere REPLACE(A1, FIND(" ",A1)+3, LEN(A1), "") entfernt die Zeichen ab dem dritten Zeichen des Nachnamens, während die äußere REPLACE(inner_replace, 3, FIND(" ",A1)-2, "") die Zeichen ab dem dritten Zeichen des Vornamens bis einschließlich des Leerzeichens entfernt.


Anhang 1:

Die obige Formel kann auch angepasst werden, um einen einzigen zweiten Vornamen zuzulassen:

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

indem Sie FIND(" ",A1) durch IFERROR(FIND(" ",A1,FIND(" ",A1)+1), FIND(" ",A1)) ersetzen.

FIND(" ", A1, FIND(" ",A1)+1) findet das zweite Leerzeichen (durch Starten der Suche nach dem Leerzeichen nach dem ersten Leerzeichen) oder auf andere Weise einen Fehler.
IFERROR(find_second_space, FIND(" ",A1)) findet das erste Leerzeichen, wenn es kein zweites Leerzeichen gibt.


Diese (langatmige) Version erlaubt eine beliebige Anzahl von Zweitnamen:

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

In diesem Fall wird FIND(" ",A1) durch FIND("§", SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) ersetzt.

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) zählt die Anzahl der Leerzeichen.
SUBSTITUTE(A1, " ", "§", count_of_spaces) ersetzt das letzte Leerzeichen durch §.
FIND("§", last_space_replaced_string) findet den ersten §, der mit dem letzten Leerzeichen identisch ist.

(Der § könnte natürlich durch ein beliebiges Zeichen ersetzt werden, von dem garantiert wird, dass es in der vollständigen Namenszeichenfolge nicht vorhanden ist. Eine allgemeinere und sicherere Alternative wäre die Verwendung von CHAR(1).)


Beachten Sie, dass BruceWaynes Antwort die einfachste und verständlichste Lösung ist, die eine beliebige Anzahl von Zweitnamen zulässt. Nun war es. Bis ich meine andere Antwort gepostet habe, das ist ;-)


Anhang 2:

Alle Lösungen können angepasst werden, um nur den Fall eines einzelnen Namens zu berücksichtigen (wenn ein Ergebnis mit vier Zeichen erforderlich ist), indem sie in eine Funktion IFERROR() wie folgt eingeschlossen werden:

=IFERROR(solution, alternate_formula)

Beachten Sie, dass es sich bei der obigen Formel um eine allgemeine Fallformel handelt und möglicherweise eine effizientere Änderung an einer bestimmten Lösung vorgenommen werden kann. Wenn beispielsweise bei einem einzelnen Namen die Anforderung besteht, die ersten beiden Buchstaben mit den letzten beiden Buchstaben zu verbinden, kann die Antwort von PeterH auf diese Weise effizienter angepasst werden:

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


Um den Fall eines einzelnen Vornamens oder einer Initiale (vorausgesetzt, ein Leerzeichen oder Punkt ist als zweites Zeichen nicht zulässig) zu berücksichtigen, kann für jede Lösung Folgendes verwendet werden:

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

Beachten Sie, dass das einzelne Zeichen entweder fest codiert oder aus dem Namen berechnet werden kann. (Oder verwenden Sie "", um das Leerzeichen oder den Punkt zu entfernen.)


Wenn Siewirklichfür den Fall sorgen müssen, dass der vollständige Name nur aus einem einzelnen Zeichen besteht (!), Müssen Sie nur die Formel mit einem einzelnen Namen umbrechen mit einem anderenIFERROR(). (Vorausgesetzt natürlich, dass die alternative Formel diesen Sonderfall nicht berücksichtigt.)


Anhang 3:

Endlich, endlich (nein, wirklich* ;-)) Verwenden Sie TRIM(A1) anstelle von A1, um mehrere aufeinanderfolgende und/oder führende/nachfolgende Leerzeichen zu berücksichtigen.


* Ich lasse den Fall für einen einzelnen Buchstaben Nachname, wie Herr T., als Übung für den Leser.

Hinweis: =solution &IF(MID(A1,LEN(A1)-1,1)=" ", single_char, "")

5
robinCTS

Basierend auf dieser Antwort ist hier eine elegante Lösung, die mit einer beliebigen Anzahl von zweiten Vornamen funktioniert:

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

Erklärung:

SUBSTITUTE(A1, " ", REPT(" ",LEN(A1))) ersetzt die Leerzeichen zwischen Wörtern durch Leerzeichen, deren Anzahl der Länge der gesamten Zeichenfolge entspricht. Die Verwendung der Zeichenkettenlänge anstelle einer willkürlich großen Zahl garantiert, dass die Formel für jede Zeichenkettenlänge funktioniert, und bedeutet, dass dies effizient ist.

RIGHT(space_expanded_string, LEN(A1)) extrahiert das Wort ganz rechts, dem eine Reihe von Leerzeichen vorangestellt sind.*

TRIM(space_prepended_rightmost_Word) extrahiert das Wort ganz rechts.

LEFT(rightmost_Word, 2) extrahiert die ersten beiden Zeichen des Wortes ganz rechts (Nachname).


* Warnung: Wenn ein Benutzername nachgestellte Leerzeichen enthalten kann, müssen Sie das erste Argument von SUBSTITUTE(), d. H. A1, durch TRIM(A1) ersetzen. Führende Leerzeichen und mehrere aufeinanderfolgende Leerzeichen zwischen Wörtern werden nur mit A1 korrekt behandelt.


Reparieren Sie Ihren Versuch

Bei näherer Betrachtung Ihrer Lösungsversuche scheinen Sie einer funktionierenden Formel sehr nahe gekommen zu sein, um die ersten beiden Buchstaben des ersten Wortes (dh den Vornamen) und die ersten beiden Buchstaben deszweiten) zu verkettenWord, falls vorhanden.

Beachten Sie, dass die korrigierte Formel, wenn ein Benutzername Zweitnamen enthalten würde, fälschlicherweise die ersten beiden Buchstaben des ersten Zweitnamens anstelle des Nachnamens erfasst (vorausgesetzt, Sie möchten sie tatsächlich aus dem Nachnamen extrahieren).

Wenn alle Benutzernamen nur aus einem Vornamen oder einem Vor- und Nachnamen bestehen, ist die Formel unnötig kompliziert und kann vereinfacht werden.


Um zu sehen, wie die Formel funktioniert und um sie so zu korrigieren, ist es einfacher, wenn sie so beschönigt ist:

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


Um zu verstehen, wie es funktioniert, schauen Sie sich zunächst an, was passiert, wenn A1 keine Leerzeichen enthält (d. H. Nur einen einzigen Namen enthält). Alle IFERROR()-Funktionen werden zu ihren zweiten Argumenten ausgewertet, da FIND() einen #VALUE!-Fehler zurückgibt, wenn die Suchzeichenfolge nicht in der Zielzeichenfolge gefunden wird:

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

Das dritte Argument von MID() wird mit Null ausgewertet, sodass die Funktion "" ausgibt und das Formelergebnis die ersten beiden Zeichen des einzelnen Namens sind.


Sehen Sie sich nun an, wann genau zwei Namen vorhanden sind (d. H. Genau ein Leerzeichen). Die erste und die dritte Funktion IFERROR() werden nach ihren ersten Argumenten ausgewertet, während die zweite nach ihrem zweiten Argument ausgewertet wird, da FIND(" ", SUBSTITUTE(A1," ","",1)) versucht, nach dem Entfernen des ersten und einzigen Leerzeichens ein anderes Leerzeichen zu finden:

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

Offensichtlich gibt MID() das zweite Wort (d. H. Den Nachnamen) in seiner Gesamtheit zurück, und das Formelergebnis sind die ersten beiden Zeichen des Vornamens, gefolgt von alle den Zeichen des Nachnamens.


Der Vollständigkeit halber werden wir uns auch den Fall ansehen, in dem es mindestens drei Namen gibt, obwohl es jetzt ziemlich offensichtlich sein sollte, wie die Formel zu korrigieren ist. Dieses Mal werden alle IFERROR() -Funktionen auf ihre ersten Argumente hin ausgewertet:

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

Es ist etwas weniger klar als im vorherigen Fall, aber MID() gibt genau das gesamte Wort second (d. H. Erster zweiter Vorname) zurück. Das Formelergebnis sind also die ersten beiden Zeichen des Vornamens, gefolgt von allen Zeichen des ersten zweiten Vornamens.


Offensichtlich besteht die Korrektur darin, LEFT() zu verwenden, um die ersten beiden Zeichen der Ausgabe von MID() abzurufen:

=
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
)


Die oben erwähnte Vereinfachung besteht darin, LEFT(MID(…,…,…), 2) durch MID(…,…,2) zu ersetzen:

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

oder in einer Zeile:

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

Dies ist im Wesentlichen PeterHs Lösung geändert, um auch mit einzelnen Namen zu arbeiten (in diesem Fall sind das Ergebnis nur die ersten beiden Zeichen des Namens).


Hinweis: Die angegebenen Formeln funktionieren tatsächlich, wenn sie eingegeben werden.

2
robinCTS