Frage von Krefelder81, 29

Excel Formel- Zeichen Schema erkennen?

Zahlen -> N

Buchstaben -> A

Sonderzeichen sollen 1:1 ausgegeben werden.


Beispiel:

1 ->N

11 ->NN

500 -> NNN

B -> A

UMM -> AAA

E100 -> ANNN

100/TU/50! -> NNN/AA/NN!

Expertenantwort
von Iamiam, Community-Experte für Excel, 29

Das geht m.E. am besten über ein Makro (nur Dateityp xlsm und nervige Warnhinweise, und nicht transformierbar in andere Tabellenkalkulationen) , zB als µ-Funktion:

Function ZeichenKategorien(Ort As Range)
Dim i As Integer, Start As String : Start = ""
For i = 1 To Len(Ort)
If Asc(Mid(Ort, i, 1)) > 47 And Asc(UCase(Mid(Ort, i, 1))) < 58 Then
Start = Start & "N" 'Ziffern
ElseIf Asc(UCase(Mid(Ort, i, 1))) > 64 And Asc(UCase(Mid(Ort, i, 1))) <91 Then
Start = Start & "A" 'Standard-Buchstaben
ElseIf Asc(UCase(Mid(Ort, i, 1))) = 196 Or Asc(UCase(Mid(Ort, i, 1))) = 214 Or Asc(UCase(Mid(Ort, i, 1))) = 220 Then
Start = Start & "A" 'Umlaute Ä, ö, ü
ElseIf Asc(Mid(Ort, i, 1)) = 223 Then Start = Start & "N" 'ß
Else: Start = Start & Mid(Ort, i, 1) 'alles Andere…@,€;µ.+... auch höhere Unicode-Zch ohne Fehler
End If
Next i
ZeichenKategorien = Start
End Function

'da ließen sich etliche auch mit Or noch aneinanderhängen, aber so finde ich es übersichtlicher. Habs getestet, bei mir tutets.

Als (Ort) ist nur eine Einzelzelle möglich. Der Vorteil der Formel ist, dass der Bezug beliebig gesetzt werden kann und relativ sich verändert beim Kopieren. Nicht allzuviele auf einmal möglich, je nach Rechner-Kapazität.

Das Ganze ließe sich auch als Ereignismakro gestalten, das den umgewandelten Wert bei Eingabe entweder zB rechts daneben oder sonstwohin schreibt oder aber die Eingabe sofort überschreibt (aber damit unwiderbringlich vernichtet). Wenn Du das willst, melde Dich nochmal, ebenso bei Fragen zur Installation der Funktion oder Sonstigem.

Expertenantwort
von Oubyi, Community-Experte für Excel, 25

Ausnahmsweise schlage ich hier eine Lösung mit Hilfsspalten vor:

Angenommen die Daten stehen in Spalte A ab a1.
Dann in C1 diese Formel und (für max 10 Zeichen) bis nach L1 kopieren und anschließend den gesamten Bereich C1:L1 nach unten soweit wie nötig:

=WENNFEHLER(WENN(UND(CODE(TEIL($A1;SPALTE(A1);1))>=48;(CODE(TEIL($A1;SPALTE(A1);1))<=57));"N";WENN(UND(CODE(TEIL($A1;SPALTE(A1);1))>=65;(CODE(TEIL($A1;SPALTE(A1);1))<=89));"A";TEIL($A1;SPALTE(A1);1)));"")

In B1 und runterkopieren dann diese Formel (für die max. 10 Zeichen)

=C1&D1&E1&F1&G1&H1&I1&J1&K1&L1

ACHTUNG! Ich habe erstmal nur Großbuchstaben vorgesehen und keine Umlaute. Falls das nicht reicht müsste ich das UND() nochmal erweitern, sollte aber auch kein Problem sein.

Das lässt sich natürlich auch leicht auf beliebig viele Zeichen erweitern.
Wirklich elegant ist das mit den Hilfsspalten zwar nicht, aber das in eine Matrix zu zwängen, dazu fehlt mir die Lust (und vlt. auch das Wissen)).

Aber vielleicht hat ja noch jemand eine bessere Idee?!

Klappt es?

Kommentar von Iamiam ,

DH! auf so eine Formel wäre ich nicht gekommen, hab eher an eine Matrixformel gedacht, (geht doch inzwischen auch mit Verketten? oder irre ich mich da?) analog der Quersummen-Formel wenn auch mit vielen Bedingungen, hab mich dann aber doch nicht drangetraut.

Expertenantwort
von DeeDee07, Community-Experte für Excel, 29

Das folgende ist eine Modell-Formel, die die Buchstaben A und B zu N, die Ziffern 1 und 2 zu A tauscht:

=WECHSELN(WECHSELN(WECHSELN(WECHSELN(A1;"A";"N");"B";"N");"1";"A");"2";"A")

Und nach diesem Muster kann man dann alle anderen Buchstaben und Ziffern ersetzen, aber die anderen 24 Buchstaben (ggf. noch 26 Kleinbuchstaben) und 8 Ziffern überlasse ich dir ;-)

Zum Glück erlaubt Excel bis zu 64 Ebenen in einer Formel.

Zugegeben, für diese Formel verdiene ich keinen Eleganz-Preis...

Kommentar von Iamiam ,

die Kleinbuchstaben kann man mit Gross(A1) überführen und danach vergessen, was Vergleichbares zu Ziffern fällt mir nicht ein (alles  länger/unübersichtlicher als 10x wechseln)

Kommentar von Iamiam ,

ich hab mir mal den "Spass" gemacht, das auszufeilen, es funkt::

=WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(GROSS(A1);"A";"A");"B";"A");"C";"A");"D";"A");"E";"A");"F";"A");"G";"A");"H";"A");"I";"A");"J";"A");"K";"A");"L";"A");"M";"A");"N";"A");"O";"A");"P";"A");"Q";"A");"R";"A");"S";"A");"T";"A");"U";"A");"V";"A");"W";"A");"X";"A");"Y";"A");"Z";"A");"ß";"A");"Ä";"A");"Ö";"A");"Ü";"A");"0";"N");"1";"N");"2";"N");"3";"N");"4";"N");"5";"N");"6";"N");"7";"N");"8";"A");"9";"A")

überraschenderweise funktioniert ß in Gross(A1)

Die Argumentkontrolle (Klammern) hab ich übrigens mit

=Länge(A4)-Wechseln(A4;"(";"") bzw 
-Wechseln(A4;")";"") gemacht.


Kommentar von Iamiam ,

aufgrund deiner Rückmeldung hab ich nochmal reingeschaut und prompt einen Fehler entdeckt: Argumentkontrolle über

=Länge(A4)-Länge(Wechseln(A4;"(";""))

Das mit A anstatt N hab ich beim anpassen vergessen, natürlich!

Kommentar von DeeDee07 ,

Alle verfügbaren Daumen hoch!

Allerdings werden die Ziffern 8 und 9 zu A (statt N).

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten