Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1864to1868
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
Inhaltsverzeichnis

restliche Texte ignorieren

restliche Texte ignorieren
18.01.2022 16:33:36
Tom
Hallo,
wie in der Datei zu sehen, möchte ich,daß in Zelle B35 nur die zahlen addiert werden.
Das klappt auch soweit ganz gut.
Nur sollte die Formel "nur Buchstaben" in den Zellen dabei ignorieren.
Ich hoffe die Datei erklärt es besser,als das, was ich hier geschrieben habe.
https://www.herber.de/bbs/user/150489.xlsx
MfG

36
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: restliche Texte ignorieren
18.01.2022 18:58:37
AlterDresdner
Hallo Tom,
=SUMMENPRODUKT((D3:D33"")*(WENNFEHLER(WERT(LINKS(D3:D33;1));0)))
MfG
AW: zu klären wäre noch ...
18.01.2022 19:58:24
neopa
Hallo Tom,
... ob es immer nur eine Ziffer ist, die zu addieren ist.
Wenn das der Fall ist, dann folgende Matrixformel: {=SUMME(WENNFEHLER(--LINKS(D3:D33;1);0))}
Die {} nicht eingeben sondern die Formel mit der Tastenkombination [Strg] + [Shift ] + [Enter] abschließen.
Gruß Werner
.. , - ...
AW: zu klären wäre noch ...
19.01.2022 15:06:46
Tom
Hallo,
danke für die Antworten .
Werde es nachher mal probieren
MfG
AW: zu klären wäre noch ...
19.01.2022 15:13:47
Tom
Hi,
Soo, habe es mal übernommen.
eine zweistellige Zahl nimmt er nicht an.
Gebe ich z.B. 11 ein, addiert er nur plus1
Anzeige
AW: genau deswegen bat ich um Klärung ....
19.01.2022 17:31:53
neopa
Hallo Tom.
... wie viel stellig die zu addierenden Zahlen vor einem Textwert max sein kann? Zweistellig? Bisher werden nur einstellige Zahlen ausgewertet.
Gruß Werner
.. , - ...
AW: genau deswegen bat ich um Klärung ....
19.01.2022 19:28:15
Tom
Hi,
ja sorry, hast recht.
Zweistellig reicht.
Danke
MfG
AW: wenn alle Zahlen zweistellig wäre ......
19.01.2022 20:01:20
neopa
Hallo Tom,
... dann bräuchtest Du nur die 1 durch die 2 zu ersetzen. Wenn es aber sowohl als auch sein kann, dann z.B. wieder mit Matrixformeleingabeabschluss so:
{=SUMME(WENN(ISTZAHL(-TEIL(B3:B33;2;1));--LINKS(B3:B33;2))+WENN(ISTZAHL(-TEIL(B3:B33;1;1))*ISTFEHLER(-TEIL(B3:B33;2;1));--LINKS(B3:B33;1)))}
Gruß Werner
.. , - ...
Anzeige
AW: wenn alle Zahlen zweistellig wäre ......
20.01.2022 12:14:55
Tom
HI,
ja,daß mit der 2 hatte ich schon rausgefunden (STOLZ)
Danke euch wieder vielmals für eure Hilfe
MfG
Tom
AW: bitteschön owT
20.01.2022 12:18:33
neopa
Gruß Werner
.. , - ...
AW: wenn alle Zahlen zweistellig wäre ......
20.01.2022 12:27:46
Tom
wäre es denn auch möglich, daß er auch NUR Zahlen ignoriert
(Ich weiss, daß das eine Salamimethode ist, aber da ich absoluter Anfänger bin, kommen die Probleme ja erst beim ausprobieren.)
Also, daß diese Formel NUR die Zahlen ausliest, die mit Buchstaben/Zeichen versehen sind und Zahlen ohne ignoriert.
Hintergrund: Wir haben FZ Stunden (Freizeit) und JAZ Stunden (JahresArbeitsZeit).
Die möchte ich deshalb, je nachdem was ich als Ausgleich haben möchte, separat berechnen können.
MfG
Anzeige
AW: ja, ist auch möglich ...
20.01.2022 12:38:03
neopa
Hallo Tom,
... stelle dazu eine neue Beispieldatei ein, aus der eindeutig hervorgeht, welche Zahlen berücksichtigt werden sollen und welche nicht.
Gruß Werner
.. , - ...
AW: ja, ist auch möglich ...
20.01.2022 13:21:54
neopa
Hallo Tom,
... dann in meiner Formel eine zusätzliche Prüfung notwendig.
So in J44::
=SUMME(WENN(ISTZAHL(-TEIL(J6:J33;2;1))*ISTTEXT(J6:J33);--LINKS(J6:J33;2)) +WENN(ISTZAHL(-TEIL(J6:J33;1;1))*ISTFEHLER(-TEIL(J6:J33;2;1))*ISTTEXT(J6:J33);--LINKS(J6:J33; 1)))
Gruß Werner
.. , - ...
Anzeige
AW: Formel mit StrG+Shift+Enter abschließen owT
20.01.2022 13:40:17
neopa
Gruß Werner
.. , - ...
Warum so kompliziert, ...
21.01.2022 15:41:38
Luc:-?
Tom?
Im BspMonat würde auch eine wesentlich einfachere Fml als die von Werner und Lupo vorgestellten zulassen. Außerdem ist auf deinem Februar-Blatt Werners Fml zwar angepasst verwendet, aber dazu die Anmerkung gemacht worden, dass hier die einzelne 4 nicht berücksichtigt wdn soll, weil sie bereits abgezogen wird. Wie passt das zusammen?
Auch die anderen Fmln scheinen mir nicht gerade optimal zu sein, was auch daran liegen mag, dass sie zwar selbsterklärend sind, aber letztlich der GesamtZusammenhang diverser unterer Summen fehlt, was insgesamt den Eindruck erweckt, dass etliche Fmln monatlich angepasst wdn müssen, was bei optimaler Fml-Bildung nicht erforderlich wäre. Folglich alles ein typischer Versuch, ein womöglich nur aus einer bestimmten Vorgehensweise resultierendes EinzelProblem auch einzeln zu lösen. Und darauf haben sich dann alle AWer (aus durchaus unterschiedlichen Gründen) auch konzentriert ohne vorab sämtliche GrundFragen zu klären. Damit muss man als FrageSteller aber rechnen, denn ein Forum ist grundsätzlich kein/e Office-Trainer/-Beratung, sondern idR auf EinzelFragen fixiert.
Zu diesem EinzelProblem:
Sollte die separate 4 in der JAZ-Summe tatsächlich entfallen sollen, ginge auch das:
{=SUMME(WENN(RECHTS(J6:J33;3)="JAZ";--LINKS(J6:J33;LÄNGE(J6:J33)-3)))}
Mit diesem reinen Zahlenwert dann auch so:
{=SUMME(WENNFEHLER(--WECHSELN(J6:J33;"JAZ";"");""))} oder so:
{=SUMME(J6:J33;WENN(RECHTS(J6:J33;3)="JAZ";--LINKS(J6:J33;LÄNGE(J6:J33)-3)))}
Da auch andere Texte im relevanten Bereich auftreten können, die aber tageweise signiert und separat erfasst wdn, folglich nur Text ohne StundenAngabe sind, fallen sie hier raus. Allerdings auch andere StundenSignierungen, da aus diesem konkreten Bsp nicht hervorgeht, was ggf noch zu erfassen wäre und dann in welcher Summe.
Übrigens, es gäbe auch noch einfachere Methoden, bspw echte Zahlen, denen Text über die Formatierung hinzugefügt wird, oder eine VBA-basierte UDF, die gemischte Zahlen-Text-Werte entsprd aufspalten kann.
Gruß, Luc :-?
PS@Lupo: Kann man auch unterschiedliche ZahlenFolgen mit SEQUENZ bilden oder nur lineare? Mit meiner UDF VSeq kann man bisher 6 verschiedene Typen bilden, u.a. Fibonacci und Collatz. Gruß, Luc :-?
Anzeige
AW: bedingt so ...
21.01.2022 19:16:05
neopa
Hallo Luc,
... ich hab jetzt nicht nachgelesen, aber ursprünglich waren verschiedenen Folgetexte unterschiedlicher Länge zu berücksichtigen. Und da können Deine Formelvereinfachungen nicht greifen.
Wenn es aber immer der gleiche oder nur wenige verschiedenen Folgetexte wären, würde ich eine Formel mit WECHSELN() bevorzugen. Weil damit auch evtl. vorhandene führende oder nachschleppende Leerzeichen keine Probleme verursachen können, wogegen bei meiner Formel noch führende Leerzeichen noch durch GLÄTTEN() entfernt werden müssen.
Gruß Werner
.. , - ...
Mehrere ganze Zahlen pro Zelle einer Spalte XL2019
20.01.2022 17:27:51
lupo1
B35:
=SUMME(WENNFEHLER(1/(1/
GLÄTTEN(TEIL(WECHSELN(
GLÄTTEN(
TEXTVERKETTEN(;;WENNFEHLER(--TEIL(B3:B33;SEQUENZ(;99);1);" "))
);" ";WIEDERHOLEN(" ";299));SEQUENZ(;999;1;299);299)));))

schließt 999 mögliche Zahlen INSGESAMT ein.
Obergrenze nicht getestet.
Dezimal- oder negative Zahlen mit Zusatzaufwand vermutlich möglich.
Anzeige
AW: gibt es SEQUENZ() schon in XL2019? owT
20.01.2022 17:37:50
neopa
Gruß Werner
.. , - ...
Keine Ahnung, also jedenfalls 2021/365/Web
20.01.2022 17:48:38
lupo1
nope (owT)
21.01.2022 08:18:30
Pierre
nope (owT)
21.01.2022 08:23:40
Pierre
AW: nachgefragt ...
21.01.2022 10:51:40
neopa
Hallo Pierre ,
... ist für mich so noch nicht eindeutig. Gibt es die Funktion SEQUENZ() schon in XL2019 oder doch nicht? Mein google-"Ergebnis"- meinte nein. In dem Zusammenhang weiter nachgefragt. Gibt es TEXTVERKETTEN() in XL2019 oder nicht? Dazu hatte ich nur ergooglt, dass es die Funktion schon in XL2016 vorhanden sei soll, was aber eindeutig auf meine Xl2016er Version nicht zutrifft. Was gilt also hier?
Gruß Werner
.. , - ...
AW: nachgefragt ...
21.01.2022 11:50:30
Pierre
Hallo Werner,
SEQUENZ gibt es bei xl2019 nicht. Sagen wir mal so: Bei meiner Version nicht, habe Home & Business.
TEXTVERKETTEN hingegen gibt es bei meiner Version schon.
Ich erinnere mich aber an eine Diskussion vor Kurzem, als uns aufgefallen ist, dass in manchen 2019er Versionen auch TEXTVERKETTEN nicht vorhanden ist.
Kurzum: Bei 2019 Home & Business gibt es TEXTVERKETTEN.
Wie genau es bei anderen 2019er Versionen aussieht, kann ich allerdings nicht sagen.
Gruß Pierre
Anzeige
AW: danke für Deine Informationen owT
21.01.2022 13:23:40
neopa
Gruß Werner
.. , - ...
Und hier für 2019
20.01.2022 17:54:19
lupo1
B35:
=SUMMENPRODUKT(WENNFEHLER(1/(1/
GLÄTTEN(TEIL(WECHSELN(GLÄTTEN(
TEXTVERKETTEN(;;WENNFEHLER(--TEIL(B3:B33;SPALTE(A:ZZ);1);" "))
);" ";WIEDERHOLEN(" ";299));SPALTE(A:ZZ)*299-298;299)));))

ZZ=702 statt 999, aber kann man selbst weiter aufblähen.
B3:B5 als Beispiel:
Abc345d456
1000
H1j2k
ergibt 1804
Und hier für 2019
20.01.2022 17:55:50
lupo1
B35:
=SUMMENPRODUKT(WENNFEHLER(1/(1/
GLÄTTEN(TEIL(WECHSELN(GLÄTTEN(
TEXTVERKETTEN(;;WENNFEHLER(--TEIL(B3:B33;SPALTE(A:ZZ);1);" "))
);" ";WIEDERHOLEN(" ";299));SPALTE(A:ZZ)*299-298;299)));))

ZZ=702 statt 999, aber kann man selbst weiter aufblähen.
B3:B5 als Beispiel:
Abc345d456
1000
H1j2k
ergibt 1804
Anzeige
AW: nach Deinem angegeb. Beispiel zu urteilen, ...
20.01.2022 19:23:21
neopa
Hallo lupo,
... hast Du Dir offensichtlich die letzte von Tom eingestellte Datei nicht angeschaut. Das was Du mit der Formel berechnest, ist nicht das von Tom angestrebte Ergebnis.
Gruß Werner
.. , - ...
Es ist eine Obermenge davon. Ist das schlimm?
20.01.2022 21:18:07
lupo1
AW der TE hat XL-Level: "Kaum Excel" angegeben owT
21.01.2022 09:03:22
neopa
Gruß Werner
.. , - ...
Meine Formel ist genauso verwendbar wie Deine
21.01.2022 10:44:50
lupo1
... und möglicherweise versteht er Deine eher als meine.
Nur selbst wird er wohl keine von beiden hinbekommen. Dann ist es auch egal, ob sie "zu komplex" oder "viel zu komplex" ist.
Außerdem antworte ich nie mit dem Anspruch nur der Beantwortung der Frage.
AW: nein, nicht im vorliegenden Fall ...
21.01.2022 10:57:56
neopa
Hallo lupo,
... denn er wollte ausdrücklich echte Zahlenwerte nicht mit addiert haben. Außerdem wollte er nur max. 2stellige führende Zahlenwerte vor Texten addieren.
Gruß Werner
.. , - ...
Anzeige
Kann man beides mit Leichtigkeit noch einschränken
21.01.2022 11:36:02
lupo1
AW: sicher, aber möglicherweise der TE nicht owT
21.01.2022 13:23:11
neopa
Gruß Werner
.. , - ...
Das Ziel meiner Formel war:
21.01.2022 12:10:48
lupo1
Bis vor den genialen ERSETZEN-mit-Null-Streich von Hydronaut https://www.herber.de/excelformeln/src/call.pl?idx=498 bei excelformeln.de gab es kein befriedigendes Trennen von Zahlen (nicht nur Ziffern) und Text, wenn diese nicht durch ein Trenn- oder Leerzeichen getrennt waren. Und auch jene Formel schafft es nur einmal im String.
Hier versuche ich es mit "Maskierung" von Nichtziffern. Der Nachteil ist momentan noch, dass
Tausenderzeichen
Dezimalzeichen
Minuszeichen
Prozentzeichen
manuell in die Prüfung müssten, da es um Ziffern, nicht um Zahlen, geht.
AW schon klar, aber so eben nur für XL365/XL21 owT
21.01.2022 13:22:41
neopa
Gruß Werner
.. , - ...
und 2019 (nachgeliefert)
21.01.2022 13:32:45
lupo1
Merke: Wer zu pingelig ist, zieht einen Rattenschwanz unnötiger Kommunikation hinterher :-)
Wegen SEQUENZ muss niemand auf einer neuen Version beharren. ZEILE/SPALTE tun es auch (nicht immer, aber hier).

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige