Excel Zeichenanzahl in Zelle begrenzen
wie kann ich bei Excel die Zeichenanzahl pro Zelle begrenzen, so das dann nicht einfach weitergeschrieben werden kann???
wenn ich das über Daten-> Gültigkeit-> Textlänge begrenze, hab ich wenn der text zu lang ist nur die meldung, dass der wert ungültig ist, da er zu lang ist. dann muss ich ja wieder zählen, wie viele zeichen ich zu viel verwendet habe. kann ich einstellen, dass ich z.B. nur 25 Zeichen zur Verfügung habe und wenn diese aufgebraucht sind, ich in dieser zelle nicht weiterschreiben kann?
4 Antworten
Nach vielen Irrwegen hab ich eine einfache Lösung gefunden, zumindest, wenn in der Üprüfung im Blatt Error nichts gegenläufiges eingetragen ist:
geh in der Fehlermeldung auf Wiederholen, dann wird die Bearbeitungszeile aktiviert und aus der kannst Du Deine bisherige Eingabe rauskopieren.
Bei längeren Texten lohnt es sich, das in eine andere -entsprechend vorbereitete - Zelle reinzukopieren, zB M20.
Dann in M21 die Formel:
=links(M20;25)
und in M22 die Formel:
=Teil(M20;26;9999)
dann beide Formeln kopieren und zB daneben Inhalte einfügen-Werte (bzw alles ohne Formeln).
In Zeile 21 kannst Du den zugelassenen Text kopieren und in die vorgesehene Zelle einfügen,
in Zeile 22 kannst Du den überständigen Text zur weiteren Verwendung kopieren.
Auch nicht gerade kurz, aber zumindest kein Verlust, sollte man die Daten etwas aufwändiger wiederbeschaffen müssen.
Ich bin auch noch an einem Makro, das sie Gültigkeitsprüfung aufhebt, den Eintrag spaltet, den Überstand verfügbar macht und dann die Gültigkeitsprüfung wiederherstellen soll. Das geht aber nur, wenn der Schutz des Blattes sowas zulässt.
Auch obiger Formeleintrag funktioniert im selben Blatt nur, wenn das Blatt nicht geschützt ist, Du kannst ihn aber in jedem anderen Blatt in freien Bereichen vornehmen. Ich würde dazu einen einzelligen Bereich in einem anderen Blatt definieren und darunter die Formeln vorbereiten.
das o.g. Makro schick ich auf Wunsch trotzdem noch als Kommentar nach, wenn es ganz fertig ist -sofern Du mit Makros umgehen kannst. War immerhin eine interessante Aufgabenstellung!
Ich bin bei Excel immer sehr vorsichtig, bevor ich schreibe:
"Das geht nicht"
aber hier bin mich mir doch recht sicher, dass das in Excel nicht machbar ist.
Selbst über VBA kann man nicht während der Eingabe in eine Zelle eingreifen, da das Change-Ereignis erst mein Drücken von ENTER eintritt.
Was ich Dir anbieten kann, ist dieser Code (im Modul des Arbeitsblattes):
Private Sub Worksheet_Change(ByVal Target As Range)
If Len(Target.Value) > 25 Then
Target.Value = Left(Target.Value, 25)
MsgBox "Text auf 25 Zeichen gekürzt"
End If
End Sub
Der verhindert zwar nicht, dass mehr als 25 Zeichen eingegeben werden, kürzt aber die Eingabe NACH dem drücken von ENTER auf 25 Zeichen und gibt eine entsprechende Meldung aus. Man kann das, wenn gewünscht, auch auf einen bestimmten Bereich im Blatt beschränken und erst Abfragen OB gekürzt werden soll.
Wenn das etwas für Dich ist, dann melde Dich nochmal, falls Du in VBA nicht fit genug bist, um es "einzubauen".
Nochmals @ Oubyi:
die Sache hatt mir keine Ruhe gelassen und ich hab jetzt ein µ, das den Überlänge-Text erhält. Ich schaffe es aber nicht, zu verhindern, dass am Ende immer das Modulblatt erscheint, und deshalb blieb es vorläufig auf dem Status eines separaten Subs, nicht im Blattmodul. Vllt kannst du das noch anpassen? (bin jetzt einige Zeit ausser Gefecht)
Hier die Idee:
Sub ZeichenbegrenzungMitErhaltÜberlänge()
Dim Txt As String
'begrenztAuf3Bis9Zch ist ein Bereich, er simuliert die Eingabezelle
'schneidet den einzugebenden String (hier Variable Txt) auf hier 9 Zeichen, setzt eine Formel, in der die Überlänge erhalten ist)
Worksheets(Range("begrenztAuf3Bis9Zch").Worksheet.Name).Activate
Txt = "abcdefghijkl"
If Len(Txt) > 9 Then
Range("begrenztAuf3Bis9Zch").FormulaLocal = "=""" & Left(Txt, 9) & """&rechts(""" & Mid(Txt, 10, 999) & """;0)"
'geht, aber immer Rücksprung zum Modulblatt!
MsgBox "Text wird zu Formel und auf 9 Zeichen gekürzt um ... " & Mid(Txt, 10, 999) ': Exit Sub bewirkt auch nichts
End If
Windows(ThisWorkbook.Name).Activate '<bewirkt nichts
Worksheets(Range("begrenztAuf3Bis9Zch").Worksheet.Name).Activate '<bewirkt nichts
End Sub
Hallo lamiam.
Interessante Idee!
Den Sprung ins Modulblatt kann ich nicht nachvollziehen.
So (gekürzt)
Sub ZeichenbegrenzungMitErhaltÜberlänge()
Dim Txt As String
Txt = "abcdefghijkl"
If Len(Txt) > 9 Then
Range("begrenztAuf3Bis9Zch").FormulaLocal = "=""" & Left(Txt, 9) & """&rechts(""" & Mid(Txt, 10, 999) & """;0)"
MsgBox "Text wird zu Formel und auf 9 Zeichen gekürzt um ... " & Mid(Txt, 10, 999)
End If
End Sub
...funktioniert es bei mir einwandfrei.
In meinen Code integriert sähe es dann etwa so aus:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Txt As String
Txt = Target.Value
If Len(Txt) > 25 Then
Target.FormulaLocal = "=""" & Left(Txt, 25) & """&rechts(""" & Mid(Txt, 26, 999) & """;0)"
MsgBox "Text wird zu Formel und auf 25 Zeichen gekürzt um ... " & Mid(Txt, 26, 999)
End If
End Sub
Und auch DAS klappt einwandfrei.
Der Sprung ins Modulblatt kam daher, dass ich das Makro von dort aus aufgerufen hab. Anfängerfehler ( ||:-<{( ≡=≈ — !
hab das inzwischen auch ähnlich, klappt aber dann nicht, wenn die Eingabezelle schon begrenzt ist, da kommt Target.value überhaupt nicht vollständig an (zumindest hab ichs nicht hingekriegt)
Über eine Input-Box ginge es auch, Rohentwurf unten (führt aber zu Endlos-Schleife, die man folgenlos abbrechen kann.
Da ich wieder mal nicht aktualisiert hab vor der AW, hab ich das hier nicht gesehen und eine eigene AW mit einer ganz anderen Lösung abgeschickt! - Und dem Konzept einer weiteren Makro-Möglichkeit, auch nicht unter allen Umständen praktizierbar: Es gibt eben viele Unsicherheiten, wenn man die fragliche Datei nicht vor sich hat!
Makro mit input-Box:
Private Sub Worksheet_Change(ByVal Target As Range) 'zu www.gutefrage.net/frage/excel-zeichenanzahl-in-zelle-begrenzen
If Not Intersect(Target, Union(Range("begrenztAuf3Bis9Zch"), Range("b35"), Range("c33"))) Is Nothing Then
' union nur vorsichtshalber, wahrscheinlich ü'flüssig
Dim iBox As String
iBox = Application.InputBox("geben Sie hier den Wert ein:", "Eingabe in Zelle " & Selection.Address, , , , , , 7) 'die Richtigkeit von 7 hab ich nicht mehr überprüft
If Len(iBox) > 25 Then
Debug.Print iBox & " , Länge: " & Len(iBox)
Target.FormulaLocal = "=""" & Left(iBox, 25) & """&rechts(""" & Mid(iBox, 26, 999) & """;0)" '
MsgBox "Text wird zu Formel und auf 9 Zeichen gekürzt um ... " & Mid(iBox, 26, 999)
End If
End If
End Sub 'mit Input-Box
Jedenfalls vielen Dank für Deine Unterstützung, so macht das dann auch Spass!
Ich weiss gerade nicht was du damit erreichen willst. Aber hast du es schon mal über rechte Maustaste , und dann Zellen formatieren versucht. Da kannst du unter dem Reiter Aussrichtung einen Zeilenumbruch, bzw. eine Anpassung an die Zellengröße einstellen. Vielleicht hilft es.
Ne, das mein ich nicht. Ich hab hier Formvorgaben, nach denen in einer Zelle nur 25 Zeichen zulässig sind.
Gut wäre also, einen Text schreiben zu können, welcher nach 25 Zeichen abbricht oder eben nach Entereingabe gekürzt wird (s. Ouybi)!
Sie müssen die Validierung Anlage nutzen. Für das Jahr 2007 klicken Sie auf die Registerkarte Daten dann bei der Gruppe der Icons unter Data Tools auf der rechten Seite des Bandes schauen und klicken Sie auf das Symbol oben in der dritten Spalte der Symbole in dieser Gruppe. Klicken Sie auf den nach unten weisenden Pfeil auf der Seite des Feld Zulassen auf Textlänge und setzen Sie dann Ihre Formel für min und max. Es ist einfach genug, um zu folgen.
habs ausprobiert, geht, wenn man die Begrenzung vor der Dateneingabe vornimmt.
Leider wird aber weder der Min-Max-Wert in der Meldung ausgegeben noch kann der schon eingegebene Text kopiert werden und er verschwindet danach, also auch hier ist eine vollständige Neueingabe nötig. Schade!
DH! ich würde nur der MSG-Box noch die Zusatzinfo beifügen, was weggeschnitten wurde, also:
MsgBox "Text auf 25 Zeichen gekürzt um ... " & Mid(Target.Value,26;999)
Weiß jetzt nicht mehr, ob man aus der MSG-Box was rauskopieren kann, aber man sieht es zumindest (ich bin immer für möglichst viele Informationen in MSG-/input-boxes) halt: die MSG-Box muss vor die Zeile
Target.Value = ... schadet ja nicht! muss nur dann heissen WIRD statt WURDE