Jay muss die Zelle bestimmen, in der ein bestimmter Textwert vorkommt.

Er weiß, dass er die Such- und Ersetzungsfunktionen von Excel verwenden kann, um die Adresse von Zellen, die einen Textwert enthalten, manuell zu bestimmen, sucht jedoch nach einer Formel, um die Adresse der Zellen zu bestimmen. Er fragt sich, ob es eine Möglichkeit gibt, innerhalb eines Bereichs nach Textstücken zu suchen und Excel die Adresse der Zelle zurückgeben zu lassen, in der sich der Text befindet.

Es gibt zwei Dinge, die Sie ausprobieren können. Wenn Sie nach einer genauen Übereinstimmung für den Zellinhalt suchen, können Sie zunächst eine Formel verwenden. Die Grundformel lautet:

=ADDRESS(MATCH(C2,A:A,0),1)

In diesem Beispiel enthält Zelle C2 den gesuchten Wert und Spalte A den Bereich der durchsuchten Zellen. Die Formel gibt unabhängig von der Großschreibung von C2 oder den Werten in Spalte A ein Ergebnis zurück.

Wenn also C2 „Apfel“ enthält, stimmt die Formel positiv mit Zellen überein, die „Apfel“, „Apfel“ oder „APFEL“ enthalten. In der Tat wird jede Mischung aus Kapitalisierung übereinstimmen.

Diese Formel gibt keine Adresse für eine Zelle zurück, die das enthält, wonach Sie suchen, und zwar in einem anderen Text. Wenn Sie also nach „Apfel“ suchen (Zelle C2), wird nicht die Adresse einer Zelle zurückgegeben, die den Ausdruck „Apfel knusprig“ enthält. Sie können dieses Verhalten ein wenig ändern, indem Sie der Suchzelle Platzhalterzeichen hinzufügen. Wenn Sie beispielsweise nach „Apfel“ suchen, gibt die Formel die Adresse einer Zelle zurück, die „Apfel“ enthält, auch wenn vor oder nach anderen Zeichen stehen.

Es sollte darauf hingewiesen werden, dass diese Formel nur die Adresse der ersten Zelle in dem Bereich zurückgibt, der die Kriterien erfüllt. Wenn Sie tatsächlich die Adressen aller Zellen wünschen, die die Kriterien erfüllen, müssen Sie sich auf ein Makro verlassen. Das Folgende ist ein gutes Beispiel:

Function FindMe(x As Range, y As String) As String     Dim r As Range     Dim sResults As String     Dim sSearch As String

Application.Volatile     sSearch = LCase(y)

For Each r In x         If InStr(1, LCase(CStr(r.Value)), sSearch) > 0 Then             sResults = sResults & r.Address & ", "

End If     Next r     If Len(sResults) > 2 Then         FindMe = Left(sResults, Len(sResults) - 2)

Else         FindMe = ""

End If End Function

Sie verwenden die Funktion, indem Sie einfach den Bereich angeben, nach dem Sie suchen möchten, sowie das, wonach Sie suchen möchten:

=FindMe(A:A, "apple")

Wenn Sie einen großen Bereich verwenden (wie in diesem Beispiel – die gesamte Spalte A), wundern Sie sich nicht, wenn die Funktion eine spürbare Zeit benötigt, um ein Ergebnis zurückzugeben. Dies ist sinnvoll, da jede Zelle im Bereich durchsucht werden muss, unabhängig davon, ob sich etwas in der Zelle befindet oder nicht.

Sie müssen mit dieser Funktion auch keine Platzhalter verwenden. Es wird davon ausgegangen, dass eine Übereinstimmung auftritt, wenn sich das Gesuchte irgendwo in der Zelle befindet. Es wird auch nicht auf die Großschreibung von dem, was Sie suchen, oder auf die Großschreibung von irgendetwas im Suchbereich geachtet.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

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

Dieser Tipp (10082) gilt für Microsoft Excel 97, 2000, 2002 und 2003.

Eine Version dieses Tippes für die Multifunktionsleistenoberfläche von Excel (Excel 2007 und höher) finden Sie hier:

Wo ist dieser Text?.