Excel Zeichen aus Zelle entfernen, Zahl behalten
Wieder mal ne Frage zu Excel:
Ich habe eine Tabelle bei der in der Spalte H eine Auflistung von Zahlen ist, die ein "H" hinten dranhaben. Also ['irgendeine zahl' H]. Gibt es jetzt iwo eine Möglichkeit, das H aus allen Zahlen zu entfernen? Ich möchte die Summe aller dieser Zahlen berechnen und diese Formel erkennt nur reine Zahlen. Die Art ist eig egal, ob Formel, Makro (VBS).
Vielen Dank schon im Voraus für die Antworten
Panther12889
5 Antworten
=LINKS(A1;LÄNGE(A1)-1)*1
jup, dann einfach: =LINKS(A1;LÄNGE(A1)-2)*1
Haben alle Zahlen das H?
Falls nicht mit ner WENN abfragen
=WENNFEHLER(A1*1;LINKS(A1;LÄNGE(A1)-2)*1)
AHA, das mit dem WENNFEHLER hat funktioniert :D Ich habe jetzt zwar eine Spalte hintendran in der jede Zahl so umgerechnet wird, und ich nehme von dieser Spalte die Summe, aber das ist es mir wert, solange es nur funktioniert. Gerne bin ich allerdings weiterhin offen für andere Lösungen, z.B. für eine mit der ich NUR in der Summenzelle eine Formel habe, wie Oubyi einen Vorschlag gemacht hat.
Vielen vielen Dank dass du/ihr mir das ausrechnen mit dem Taschenrechner erspart habt! :D
LG Panther12889
Da käme noch was hinzu:
Das Zeichen H DARF entfernt werden, ich brauche es nicht mehr. Ich hab versucht mit "ersetzen" als Suchwort "* H" zu nehmen (irgendein text oder zahl mit einem h hintendran) und so auch sämtliche zahlen ohne sonstigen text gefunden. allerdings kann ich beim "ersetzen durch" nicht einfach nichts eingeben, da dann die ganze zelle gelöscht wird. einfach * geht auch nicht, weil dann nur ein * in der zelle steht.
ersetzen: ...kann ich beim "ersetzen durch" nicht einfach nichts eingeben...
Geht normalerweise schon, aber vllt hast Du den Haken bei "nur ganze Zelle..."? angekreuzt?
Ach ja, und ich sehe gerade, Du ersetzt ja "*H" ! Ersetze mal nur H durch nichts (=leer lassen).
Der * wird beim Strg+h-Dialog nicht benötigt, anders als in Formeln, wie zB zählenwenn.
Zrellen ohne H verursachen aber bei der Formel in meiner Antwort keinen Fehler!.
In derselben Tabelle befinden sich allerdings auch noch andere H'2 im Text... Die werden dann mitersetzt.
noch andere H'2 im Text... Die werden dann mitersetzt.:
Du kannst das Ersetzen auf die markierten Zellen beschränken, wenn Du mehr als eine markierst.
Nur bei nur EINER ausgewählten Zelle werden im gesamten Blatt ALLE ersetzt!.
Und wenn Du Leerzeichen+H ersetzt bzw wechselst, werden nur H ersetzt, denen ein Leerzeichen vorausgeht.
oder Du wechselst per Formel nur in Rechts(xx;2) aus, alles möglich!
Wenn der Buchstabe immer das letzte Zeichen in der Zelle ist, funktioniert folgende Formel:
=WERT(LINKS(A1;LÄNGE(A1)-1))
Die Formel schneidet das letzte Zeichen von A1 ab und verwandelt Text der übrigbleibt (jetzt ohne Buchstaben) in eine Zahl.
Das geht leider nicht, es werden nur #-Zeichen ausgegeben. Könnte es (wie bei den beiden oberen) auf wegen dem Leerzeichen sein?
Vielen Dank für die Antwort schonmal :D
Panther12889
Ich möchte die Summe aller dieser Zahlen berechnen und diese Formel erkennt nur reine Zahlen.
DIESE Formel errechnet die Summe auch, wenn hinter den Zahlen EIN Buchstabe steht:
=SUMMENPRODUKT((LINKS(A1:A30;LÄNGE(A1:A30)-1)*1))
Allerdings dürfen keine Leerzelle in dem Bereich sein, sonst wird es komplizierter.
Nun ja, dann wird es höchstwahrscheinlich auch komplizierter, es hat ja ein Leerzeichen, und zwar zwischen der Zahl und dem Buchstaben. Oder meinst du nur den Bereich den ich entfernen will? Dann wärs mir egal wenn da noch ein Leerzeichen hintendransteht. Ich probier gerade aus... THX jedenfalls ^^
Es leuchtet ein, funktioniert bei mir aber nicht. Lauter # als Ausgabe... Da wäre ja das Leerzeichen zwischen Zahl und Buchstaben... Schlussendlich muss ichs halt doch von Hand machen...
Wenn da immer ein Leerzeichen ist, dann ändere die Formel so ab:
=SUMMENPRODUKT((LINKS(A1:A30;LÄNGE(A1:A30)-2)*1))
Sonst beschreibe die Möglichkeiten nochmal genau.
Ich schaue morgen wieder 'rein.
Ich habe eine Lösung gefunden (siehe unten). Gerne bin ich allerdings weiterhin offen für andere Lösungen, z.B. für eine mit der ich NUR in der Summenzelle eine Formel habe, wie du schon einen Vorschlag gemacht hast :D.
Vielen Dank für deine Bemühungen und das erneute vorbeisehen jetzt schon
Panther12889
Laut Deinem Bild unten hast Du sowohl Leerzellen in der Spalte als auch ein Hochkomma als Tausendertrennzeichen und den Punkt als Dezimaltrennzeichen.
Da wundert es mich, dass Chris' Formel für die einzelnen Zellen funktioniert.
Ich denke am besten wäre es, wenn Du eine Beispieldatei bei einem Upload-Service (z.B. http://www.file-upload.net) hochladen und hier den Link einstellen könntest. Dann kann ich mir das mal "direkt vorknöpfen".
es scheint sich um eine Verwechslung von LeerZEICHEN und LeerZELLE zu handeln?
Wenn im Bereich LeerZELLEN sind, dann
{=SUMME(WENN(a1:a30<>"";WECHSELN(a1:a30;"H";"");0)*1)}
das toleriert auch mehrere Leerzeichen zwischen Zahl und H sowie vor der Zahl.
Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel stattdessen mit der Kombination Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!
Noch ein Tipp:
Wenn Du in der Bearbeitungszeile einen selbständigen Formelteil markierst und F9 drückst, erhältst Du ein(e) Ergebnis{liste}. Allerdings dann die Zelle mit Esc verlassen, Enter würde das gezeigte Ergebnis festschreiben! (wenn versehentlich, dann rückgängig machen!). Kürze dazu Deine Listen aber erst mal auf eine übersichtliche Länge (ALLE ADRESS-TEILE ! ) !
um auch nicht umwandelbare Texte ohne Fehler zu verarbeiten (sie zu ignorieren), folgende -erheblich kompliziertere- Formel:
=SUMME(WENN((A1:A30<>"")*NICHT(ISTFEHLER(WECHSELN(A1:A30;"H";"")*1));WECHSELN(A1:A30;"H";"");0)*1)
oder - geringfügig kürzer durch dann-sonst-Umstellung -:
=SUMME(WENN((A1:A30="")+ISTFEHLER(WECHSELN(A1:A30;"H";"")*1);0;WECHSELN(A1:A30;"H";""))*1)
Auch das sind Matrixformeln !
noch S drin: dann
anstatt WECHSELN(A1:A30;"H";"") ein
WECHSELN(WECHSELN(A1:A30;"H";"");"S";"")
da ich Deinen Kommentar erst spät bemerkt habe, ist mir die Zeit davongelaufen, also:
alle WECHSELN(A1:A30;"H";"")
ersetzen durch
WECHSELN(WECHSELN(A1:A30;"H";"");"S";"")
Wird bei zunehmender Zeichenzahl natürlich immer komplizierter!
Wechseln unterscheidet Groß-klein, macht aber keinen Fehler, wenn das zu ersetzende Zeichen nicht vorhanden ist.
wenn ich mir Deinen Screenshot anschaue:
Bist Du Dir sicher, dass Du alle Zahlen zusammenzählen sollst?
Das sieht aus, wie eine Soll/Haben-Aufstellung, und da steht Soll für minus! Das erforderte dann eine ganz andere Zusammenstellung:
=SUMME(WENN((A1:A30="") +ISTFEHLER(WECHSELN(A1:A30;"H";"")*1);0;WECHSELN(A1:A30;"H";""))*1) -SUMME(WENN((A1:A30="") +ISTFEHLER(WECHSELN(A1:A30;"S";"")*1);0;WECHSELN(A1:A30;"S";""))*1)
die Bedingung bei WENN(:
(A1:A30="")+ISTFEHLER(...); steht für leer ODER Fehler
Hab das jetzt nicht mehr getestet -ich mag jetzt wirklich nicht mehr und muss auch weg- , müsste aber gehen.
Trotz meines Fluchs vorhin jetzt doch noch die für Dich (hoffentlich) brauchbare Formel (hat mich dann auch selber noch interessiert, ob es wirklich so einfach zu erweitern geht):
{=SUMME(WENN((A1:A30="") +ISTFEHLER(WECHSELN(RECHTS(WECHSELN(RECHTS( WECHSELN(WECHSELN(A1:A30;"'";"");".";",");3);" H";" ");3);" S";" ")*1);0;WECHSELN(WECHSELN(WECHSELN(WECHSELN(A1:A30;"'";"");".";",");" H";" ");" S";" ")*1))}
evtl noch ein Leerzeichen rausmachen.
Wenn es sich aber doch um Haben/Soll-Buchungen handelt, musst Du das analog oben selbst umstellen.
Und auch dies ist wieder eine Matrix-Formel!
Tut mir Leid, ich habe allerdings auch noch ein paar Zahlen drinn, die mit " S" enden... Erst vorhin bemerkt...
Trotzdem danke für die Antwort :D
Das geht leider nicht, es werden nur #-Zeichen ausgegeben. Könnte es (wie oben) auf wegen dem Leerzeichen sein?
Vielen Dank für die Antwort schonmal :D
Panther12889