Steve hat Telefonnummern in Spalte A, z. B. 3035551212, und möchte die ersten drei Ziffern (die Vorwahl) anzeigen und in Spalte B den Status zurückgeben können, dem diese Vorwahl zugeordnet ist. Er fragt sich, wie dies am besten erreicht werden kann.

Excel verfügt über viele Funktionen, die Ihnen das Leben erleichtern, wenn Sie versuchen, Daten zu bearbeiten. In diesem Fall ist die Verwendung der VLOOKUP-Funktion zum Anpassen der Vorwahl an den entsprechenden Status einfach.

Bevor Sie eine Funktion zum Abrufen der gewünschten Informationen anwenden, müssen Sie eine einfache Datentabelle erstellen, die die Daten enthält, die Sie abrufen möchten. In dieser Tabelle müssen die Vorwahlen und Zustände in ihren eigenen Spalten nebeneinander in Ihrem Arbeitsblatt nach Vorwahl sortiert sein. Beispielsweise können Sie die Vorwahlen in Spalte F und die Zustände für diese Vorwahlen in Spalte G einfügen. Die Vorwahlen und Zustände finden Sie auf einer Reihe von Websites, oder Sie können eine eigene Tabelle erstellen, wenn Sie dies bevorzugen.

Wenn Sie die Daten in den beiden Spalten haben, wählen Sie diese Spalten aus und erstellen Sie einen Namen für den ausgewählten Bereich. (Wie Sie einen benannten Bereich erstellen, wurde in anderen Ausgaben von ExcelTips. behandelt.) Sie können den Bereich beispielsweise wie StateCodes benennen. Diese Benennung ist zwar nicht unbedingt erforderlich, erleichtert jedoch die Verwendung der Suchformel.

Angenommen, die Telefonnummer befindet sich in Zelle A1 und Sie möchten, dass der Statusname in der Spalte neben der Telefonnummer zurückgegeben wird, geben Sie in Zelle B1 Folgendes ein:

=VLOOKUP(VALUE(LEFT(A1,3)),StateCodes,2,FALSE)

Mit den Funktionen VALUE und LEFT werden nur die ersten drei Zeichen von der Telefonnummer abgerufen. Dies wird dann in der VLOOKUP-Formel verwendet, um die Vorwahl in der StateCodes-Tabelle zu finden. Excel gibt den Namen des Status zurück, der der Vorwahl entspricht.

Eine andere Möglichkeit, die Vorwahl (die für die Suche wichtig ist) herauszuholen, ist die Verwendung der FLOOR-Funktion, wie hier gezeigt:

=VLOOKUP(FLOOR(A1/10000000,1),StateCodes,2,FALSE)

Beachten Sie, dass für diesen Ansatz die Telefonnummer als Nummer gespeichert werden muss, damit sie durch 10.000.000 geteilt werden kann.

Die hier diskutierten Ansätze funktionieren hervorragend, vorausgesetzt, Ihre Telefonnummern haben immer das angegebene Format (3035551212). Wenn Ihre Telefonnummern ein anderes Format haben – möglicherweise ein Format, das Klammern und Bindestriche verwendet -, funktioniert die Formel nicht und muss angepasst werden, um festzustellen, wo sich die Vorwahl tatsächlich in der Telefonnummer befindet. Wenn Sie Telefonnummern haben, die nicht in einem einzigen Format vorliegen, sind alle Wetten ungültig und die Suche wird sehr viel schwieriger.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (8065) ​​gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Bestimmen eines Status aus einer Vorwahl.