Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1164to1168
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1164to1168
1164to1168
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Ersetzen durch nichts "funst" nicht

Ersetzen durch nichts "funst" nicht
Keule30
Hi Leute,
....vielleicht hat von euch einer Plan, warum diese relativ simple Geschichte nicht funktionieren will:
In Spalte B stehen Minutenangaben: 12:00, 05:00; 13:00
aber auch stehen in B "fehlerhafte " Angaben wie: 12min, 20min etc.
Will man diese über eine Formel auswerten, stößt man natürlich darauf, diese unterschiedlichen Formate manuell korrigiren zu müssen...was auch nicht das Problem darstellt.
Es soll hier also über eine Formel automatisch funktionieren, so dass es in Spalte C alles im richtigen Format ausspuckt.
Meine Formel in Spalte C hierfür sieht so aus:
=WENN(ISTZAHL(SUCHEN("min";B9));UND(ERSETZEN(B9;LÄNGE(B9);3;"");(B9/1440));B9)
(Durch die UND-Verknüpfung soll der entstehende Datumswert auf Minuten berechnet werden)
Soweit ich das analysieren konnte, liegt der Fehler hier:
ERSETZEN(B9;LÄNGE(B9);3;"")
Obwohl ich angegeben habe, dass stets die letzten 3 Stellen, also "min" gelöscht werden soll, löscht diese Funktion immer nur die letzte Stelle also nur das "n", so dass dann "12 mi" da steht...und diesen Wert kann Excel natürlich nicht durch 1440 teilen.
Vielleicht hab ich auch nur Tomaten auf den Augen und Ihr habt eine Idee?
Liebe Grüße!
Parameter falsch interpretiert
02.07.2010 16:03:44
NoNet
Hallo Keule,
da hast Du wohl den Parameter der ERSETZEN()-Funktion falsch interpretiert :
Ab der letzten Stelle (Länge(B9)) werden 3 Stelle ersetzt - also nur die letzte Stelle !
Was Du möchtest ist wohl folgendes :
=WENN(ISTZAHL(SUCHEN("min";B9));ERSETZEN(B9;LÄNGE(B9)-2;3;"")/1440;B9)
Gruß, NoNet
gegen    2:1

Das wäre mein Tipp
02.07.2010 21:24:32
FP
Hallo NoNet,
=TEXT(WECHSELN(A1;"min";"")/1440;"[hh]:mm")
DE : ARG 2 : 1 --> genau mein Tipp ! ! ! ( und ich hatte schon damit Recht, dass NL gegen BRA gewinnt - okay ich hatte 1:0 getippt, aber 2:1 ist ja auch nicht wesentlich anders :D )
Falls Du Dir einmal eine Turniermappe von mir anschauen willst ( für EM, WM, Quali und Meisterschaften geeignet ), schau doch mal hier http://www.office-loesung.de/fpost1633127.php#1633127 vorbei.
Kommentare ( positiv wie negativ ), Verbesserungsvorschläge usw jederzeit willkommen ;-)
Servus aus dem Salzkammergut und ( ich drücke die Daumen )
Anzeige
AW: Parameter falsch interpretiert
02.07.2010 21:27:11
FP
Hallo NoNet,
noch etwas hier geposteten Problem:
=TEXT(WECHSELN(A1;"min";"")/WAHL(TYP(A1);1;1440);"[hh]:mm")
funktioniert dann auch, wenn schon Zeitangaben in A1 stehen
Servus aus dem Salzkammergut
Franz
AW: Ersetzen durch nichts "funst" nicht
02.07.2010 16:04:23
Beverly
Hi,
meinst du das so:
=WENN(ISTZAHL(WECHSELN(B9;"min";"")*1);WECHSELN(B9;"min";"")*1/1440;B9)

dafür genügt letztendlich
02.07.2010 21:31:16
FP
Hallo,
=WECHSELN(A1;"min";"")/WAHL(TYP(A1);1;1440)
und die Ergebniszelle als Zeit formatieren ( z.B. [hh]:mm
Servus aus dem Salzkammergut
Franz
Anzeige
AW: dafür genügt letztendlich
05.07.2010 10:36:19
Keule30
Aloha,
vielen Dank an alle für die vielen Antworten und Vorschläge.
Ehrlich gesagt weis ich grad gar nicht, welche der vorgeschlagenen Formeln nun die effizienteste ist?
Genauer gesagt, gibt es auch zwei Möglichkeiten, einmal der Typ, dass "min" gelöscht und durch 1440
geteilt werden muss, es kann aber auch sein, dass "min:" mit Doppelpunkt da steht, was dann auch
ersetzt werden müsste.
(Bitte nicht fragen warum auch "min" mit Doppelpunkt möglich ist, klingt komisch ..ist aber so)
Liebe Grüße
AW: dafür genügt letztendlich
05.07.2010 11:07:21
Beverly
Hi,
=WENN(ISTZAHL(WECHSELN(WECHSELN(B9;"min";"");":";"")*1);WECHSELN(WECHSELN(B9;"min";"");":";"") *1/1440;B9)

Anzeige
Formel die "alles" nach MIN umrechnet
05.07.2010 12:25:17
FP
Hallo,
=WECHSELN(A1;LINKS("min:";3+ANZAHL(1/SUCHEN(":";A1)));"")/WAHL(TYP(A1);(A1>1)*1439+1;1440)
in A1 darf dabei stehen: z.B. 23 Minuten
min23
min:23
23:00 ( als Zeitwert mit Format mm:ss )
23 ( als dezimaler Zahlwert )
Servus aus dem Salzkammergut
Franz
AW: Formel die "alles" nach MIN umrechnet
05.07.2010 13:00:31
Keule30
Ihr seid echt genial!
Ich muss mir das jetzt erst mal in Ruhe "reinziehen".
AW: Formel die "alles" nach MIN umrechnet
05.07.2010 14:33:18
Keule30
Hallo Franz,
Deine Formel funktioniert super! Eine Verständnisfrage habe ich dazu:
Durch "LINKS" werden die ersten 3 Zeichen der Zeichenfolge "min" genommen. Danach:
"+ANZAHL(1/SUCHEN..". Die Suchen - Funktion nach dem Doppelpunkt leuchtet auch ein, aber warum
ANZAHL und eins geteilt durch Suchen? Was bedeutet das?
Liebe Grüße
Anzeige
ANZAHL ...
05.07.2010 15:03:58
FP
Hallo,
... ermittelt normalerweise wieviele Zahlen in einem bestimmten Bereich stehen.
Hier wird es "zweckentfremdet" so verwendet: ermittle wieviele Zahlen durch die Rechnung 1/x entstehen.
Ist x ein Fehlerwert weil die Suche nach ":" fehlschlägt, so ist ANZAHL 0.
Dabei merke ich gerade, dass man 1/ in diesem Fall ruhig weglassen kann, da SUCHEN ja entweder eine Zahl oder einen Fehlerwert zurückgibt ( habe wieder mal zu umständlich gedacht ).
verkürzte und funktionierende Formel daher:
=WECHSELN(A1;LINKS("min:";3+ANZAHL(SUCHEN(":";A1)));"")/WAHL(TYP(A1);(A1>1)*1439+1;1440)
Servus aus dem Salzkammergut
Franz
Anzeige
AW: ANZAHL ...
06.07.2010 08:51:37
Keule30
Hallo Beverly & Franz,
Entschuldigt bitte erstmal, dass ich das gleiche hier schon einmal geschrieben bzw. gepostet habe, war mir aber nicht sicher ob Ihr es lest, da ich mir zuletzt selbst geantwortet habe (es sind die ersten Male, dass ich etwas in einem Forum schreibe).
wie gesagt, danke und es funktionieren natürlich beide Formeln super.
Eine kleine abgewandelte aber fast identische Problematik noch, hier ein Beispiel:
Sartzeit Spalte A : bis Endzeit in Spalte B: in Spalte C ist Arbeitszeit in Min gesucht
23:30 bis 1:00
06:30 bis 7:00
12 min:
bei "12min:"wurde Arbeitszeit ohne Startzeit/Endzeit eingetragen (insgesamt, kann in Spalte A oder B stehen)
15 min (insgesamt)
12:00 bis 12:15
Wie vorhin, gilt es die Arbeitszeit in Minuten zu ermitteln, die Uhrzeitdifferenz daher über diese Grundformel:
=WENN(B1<A1;1+B1-A1; B1-A1)~f~
Weil ab und zu auch Leerzeilen auftreten und ich bei diesen keine Uhrzeitdifferenz als Null-Minuten angezeigt bekommen möchte, diese Ergänzung durch ISTZAHL (Zellen die leer sind werden nicht als 0:00 angezeigt):
~f~=WENN(UND(ISTZAHL(A1);ISTZAHL(B1);(B1<A1));1+B1-A1;WENN(UND(ISTZAHL(A1);ISTZAHL(B1);(B1>A1));B1-A1;……
…….jetzt geht die Formel mit dem Part von Franz weiter, um das mit dem „min“ und „min:“ heraus zu rechnen:
WENN(ISTZAHL(WECHSELN(B1;LINKS("min:";3+ANZAHL(1/SUCHEN(":";B1)));"")*1);WECHSELN(B1;LINKS("min:";3+ANZAHL(SUCHEN(":";B1)));"")/1440; WENN(ISTZAHL(WECHSELN(A1;LINKS("min:";3+ANZAHL(SUCHEN(":";A1)));"")*1);WECHSELN(A1;LINKS("min:";3+ANZAHL(SUCHEN(":";A1)));"")/1440;""))))
Obwohl die Formel von Franz funktioniert, bleibt diese in der Kombination an der Stelle „Suchen“ hängen und ich hab leider keinen Schimmer warum?
Ich wäre euch für eure Hilfe sehr dankbar!
Anzeige
probier's mal damit
06.07.2010 13:14:49
FP
Hallo,
{=WAHL(ANZAHL(A2:B2;1/TYP(A2:B2));WECHSELN(WENN(A2="";B2;A2);LINKS(WENN(A2="";B2;A2); 3+ANZAHL(SUCHEN(":";WENN(A2="";B2;A2))));"")/1440;MAX(A2:B2)/1440;B2+(A2>B2)-A2) }
Servus aus dem Salzkammergut
Franz
AW: probier's mal damit
06.07.2010 13:21:26
FP
Hallo,
funktioniert auch ohne {}
=WAHL(ANZAHL(A2:B2;1/TYP(A2:B2));WECHSELN(WENN(A2="";B2;A2);LINKS(WENN(A2="";B2;A2); 3+ANZAHL(SUCHEN(":";WENN(A2="";B2;A2))));"")/1440;MAX(A2:B2)/1440;B2+(A2>B2)-A2)
Servus aus dem Salzkammergut
Franz
oder mit {} aber kürzer
06.07.2010 13:27:05
FP
Hallo,
{=WAHL(ANZAHL2(A2:B2);MAX(--WECHSELN(A2:B2&0;LINKS("min:";3+ANZAHL(SUCHEN(":";A2:B2)));""))/14400; B2+(A2>B2)-A2) }
Servus aus dem Salzkammergut
Franz
Anzeige
Feedback und 3 kleine Fragen
07.07.2010 14:47:41
Keule30
Hallo Beverly & Franz,
ich hätte nicht gedacht, dass ich das noch hin bekomme: aber Dank eurer Beiden Hilfe konnte ich mir nun sowohl aus Beverly’s als auch aus den Formeln von Franz meine „perfekte Formel“ zusammenbauen: )))
=WENN(UND(ISTLEER(A7);ISTLEER(B7));"";WAHL(ANZAHL2(A7:B7);(WECHSELN(WECHSELN(A7&B7;"min";"");":";"")*1/1440);B7+(A7>B7)-A7))
Um Euch ein Feedback geben zu können: hier die jeweiligen Vorteile/Nachteile zu euren Formeln: 1) Beverly’s:
=WENN(ISTZAHL(WECHSELN(WECHSELN(B9;"min";"");":";"")*1);WECHSELN(WECHSELN(B9;"min";"");":";"") *1/1440;B9)
  • Vorteil: Diese Formel ersetzt „min:“ und „min“ unabhängig davon ob zwischen der Zahl und dem min noch Freizeichen sind; SUPER!
  • Nachteil: Diese Formel kann nur auf eine Bezugsspalte angewendet werden; was aber damals von mir auch gar nicht anders „gefordert“ war (vgl. Nachricht von Keule vom 06.07.2010 !
    2) Franz:
    {=WAHL(ANZAHL2(A2:B2);MAX(--WECHSELN(A2:B2&0;LINKS("min:";3+ANZAHL(SUCHEN(":";A2:B2)));""))/14400; B2+(A2>B2)-A2) }
  • Vorteil 1: Diese Formel berechnet die Uhrzeitdifferenz (Arbeitszeit in Minuten), zwischen zwei Spalten, auch wenn in der einen oder anderen Zelle von Spalte A oder B bereits die Gesamtzeit schon eingetragen ist (z.B. 12 min).
  • Vorteil 2: Diese Formel ist genial kurz gehalten
  • Nachteil 1: Diese Formel funktioniert nicht wenn ein Freizeichen zwischen der Zahl und „min“ vorkommt.
  • Nachteil 2: Diese Formel kann man nicht mit der Formelauswertung in Excel auswerten, um sie besser zu verstehen (Excel bleibt ab einem best. Punkt dann immer hängen und muss beendet werden).
    Hier nur noch 3 kurze Fragen:
…Wie gesagt: ich hatte nun die Vorteile eurer Formeln erkannt und mir versucht die für mich „ _
perfekte“ zu entwickeln.
Am Anfang wollte es nur nicht klappen weil ich ähnlich wie auch Franz:
…WECHSELN(A2:B2;"min";"");":";"")*1/1440);B7+(A7>B7)-A7))…
geschrieben habe.
Die Betonung liegt auf dem Bereich A2:B2.
So hatte es einfach nicht funktionieren wollen. Durch Zufall hatte ich dann anstelle des  _
Doppelpunktes   ein &-Zeichen gemacht und plötzlich funktionierte es.
Frage 1) 	Warum kann ich anstelle des &-Zeichens nicht auch wie Franz einen Doppelpunkt  _
schreiben?
Frage 2)	Und warum funktioniert dieses &-Zeichen nicht auch bei der vorangestellten „ISTLEER- _
Funktion“?
Frage 3)	Weshalb muss ich nach der „WECHSELN-Funktion“ alles mit *1 multiplizieren?
Vielen Dank für eure Antwort im Voraus.

    • Anzeige
    neue noch kürzere Formel
    07.07.2010 22:09:11
    FP
    Hallo,
    ... noch dazu ohne {}
    =WAHL(ANZAHL2(A2:B2);VERWEIS(999;RECHTS(A2&B2;SPALTE(2:2))/1440); B2+(A2>B2)-A2)
    @1. / 2. Frage - keine Ahnung, bei mir würde es funktionieren
    @3. WECHSELN gibt immer "Text" zurück, durch Multiplilkation mit 1 ( oder Addition von 0 ) wird aus einer "Textzahl" wieder eine Zahl
    Servus aus dem Salzkammergut
    Franz
    AW: dafür genügt letztendlich
    05.07.2010 19:47:50
    Keule30
    Hallo Beverly & Franz,
    wie gesagt, danke und es funktionieren natürlich beide Formeln super.
    Eine kleine abgewandelte aber fast identische Problematik noch, hier ein Beispiel:
    Sartzeit Spalte A : bis Endzeit in Spalte B: in Spalte C ist Arbeitszeit in Min gesucht
    23:30 bis 1:00
    06:30 bis 7:00
    12 min:
    bei "12min:"wurde Arbeitszeit ohne Startzeit/Endzeit eingetragen (insgesamt, kann in Spalte A oder B stehen)
    15 min (insgesamt)
    12:00 bis 12:15
    Wie vorhin, gilt es die Arbeitszeit in Minuten zu ermitteln, die Uhrzeitdifferenz daher über diese Grundformel:
    =WENN(B1<A1;1+B1-A1; B1-A1)~f~
    Weil ab und zu auch Leerzeilen auftreten und ich bei diesen keine Uhrzeitdifferenz als Null-Minuten angezeigt bekommen möchte, diese Ergänzung durch ISTZAHL (Zellen die leer sind werden nicht als 0:00 angezeigt):
    ~f~=WENN(UND(ISTZAHL(A1);ISTZAHL(B1);(B1<A1));1+B1-A1;WENN(UND(ISTZAHL(A1);ISTZAHL(B1);(B1>A1));B1-A1;……
    …….jetzt geht die Formel mit dem Part von Franz weiter, um das mit dem „min“ und „min:“ heraus zu rechnen:
    WENN(ISTZAHL(WECHSELN(B1;LINKS("min:";3+ANZAHL(1/SUCHEN(":";B1)));"")*1);WECHSELN(B1;LINKS("min:";3+ANZAHL(SUCHEN(":";B1)));"")/1440; WENN(ISTZAHL(WECHSELN(A1;LINKS("min:";3+ANZAHL(SUCHEN(":";A1)));"")*1);WECHSELN(A1;LINKS("min:";3+ANZAHL(SUCHEN(":";A1)));"")/1440;""))))
    Obwohl die Formel von Franz funktioniert, bleibt diese in der Kombination an der Stelle „Suchen“ hängen und ich hab leider keinen Schimmer warum?
    Ich wäre euch für eure Hilfe sehr dankbar!
    Anzeige

    306 Forumthreads zu ähnlichen Themen

    Zahlen aus aus Text extrahieren und addieren
    Bernd

    Hallo,
    ich würde gerne aus einer Zelle, die sowohl Zahlen, aber auch Text enthält, die Zahlen "extrahieren" und miteinander addieren. Der Inhalt der Zelle B2 z.B. würde so aussehen:
    '+15 Autos;-10 Waschmaschinen;-3 Schränke
    Nun möchte ich in Zelle C2 das Ergebnis sehen: 2
    Wie kö...
    11.09.2008 16:20:36
    Thread-ID: 1009173
    10 Beiträge
    Anzeige
    Leerzeichen links aus TextBox entfernen
    Lenni

    Moin Excellianer!
    Wie kann man in einer TextBox (in einem UserForm) Leerzeichen links des ersten Wortes so rausfiltern, dass der TB-Eintrag wirklich mit einem Zeichen beginnt? ...und keinem Leerzeichen.
    Vielen Dank für Eure Hilfe!
    Viele Grüße aus dem hohen Norden!
    Lenni
    19.02.2009 21:58:00
    Thread-ID: 1052293
    5 Beiträge
    Zahlen aus Zelle entfernen
    Peter

    Hallo,
    ich muss 2 zellen miteinander vergleichen.
    In Spalte AN:AN stehen Werte Bsp 312A706 dazu stehen in Spalte AO:AO 000030-002894-K03-12A-070-600
    Jetzt muss geprüft werden ob das A aus AN mit dem A aus AO identisch ist. Wenn ja dann Gutaussage
    siehe Bsp-Datei
    https://www....
    16.09.2009 20:34:31
    Thread-ID: 1103353
    10 Beiträge
    Anzeige
    Minus Zeichen entfernen | Herbers Excel-Forum
    Mister B
    Hallo und guten Morgen,
    mit einem Autostart Makro importiere ich eine Zahl aus einer anderen Tabelle. Alles soweit OK. Wenn die Zahl mit einem Minuszeichen beginnt, möchte ich, dass das Makro dieses gleich entfernt. Wenn die Zahl ohne Minuszeichen ist soll nichts passieren. Ich habe einiges...
    05.01.2010 10:57:00
    Thread-ID: 1127227
    4 Beiträge
    Zählen bzw. Summe aus Text und/oder Zahlen
    Detlev

    Hallo Community
    Bin ein Frischling hier und bitte auch gleich um Doppelhilfe.
    Ich brauche zwei Formeln, um aus zwei bzw. drei unterschiedlichen Bedingungen ein Ergebnis zu erhalten.
    zur 1 Formel:
    Ich benötige die Anzahl von "XYZ" in Spalte C, nur wenn in Spalte A eine "1" steht...
    21.01.2010 00:33:24
    Thread-ID: 1131577
    6 Beiträge
    Daten trennen mit Semikolon und zählen
    Jockel

    Hi,
    ich habe eine Textbox1 und eine Textbox2. In der ersten Textbox werden entweder einzelne Informationen geschrieben:
    ZB: 123456 oder 987456 oder 0123456. also 6 oder 7 stellige Schlüssel
    Die Textbox2 bliebe dann bei Eingabe EINES Schlüssels leer.
    So weit noch OK
    Nun soll...
    11.02.2010 10:04:03
    Thread-ID: 1137624
    4 Beiträge
    Zahlen aus Textzelle auslesen
    Heike

    Guten Tag an die Excel-Profis,
    nach erfolgloser Recherche und vergeblichen Tests von Formeln (siehe Datei) aus diesem Forum und auch anderen Excel-Tipps-Seiten, bitte ich um Eure Hilfe.
    Aus einer Spalte mit ca. 2000 Materialnummern, die aus Text-Zahlen-Zeichenkombinationen bestehen, müs...
    21.04.2010 20:22:08
    Thread-ID: 1153195
    9 Beiträge
    ">" vor einer Zahl entfernen + sverweis
    Roberto

    Hallo,
    ich habe in der Spalte A Zahlen vom 0 bis 55 mit oder ohne das Zeichen "Größer als". Nun möchte ich die Zahlen in der Spalte B mit Hilfe von SVERWEIS gruppieren. Sprich, wenn in der Spalte A 0,1,18,23,45 stehen, steht in der entsprechenden Zelle der Spalte B 0,1,2,3,5 (siehe unten)....
    20.06.2010 14:19:31
    Thread-ID: 1162955
    6 Beiträge
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige
    Archiv - Verwandte Themen
    Forumthread
    Beiträge
    9
    2
    4
    9
    8
    3
    5
    3
    8
    5
    8
    3
    5
    2
    4
    11
    2
    5
    8
    4
    8
    3
    2
    1
    3