Microsoft Excel

Herbers Excel/VBA-Archiv

Textteil zwischen zwei Zeichen löschen


Betrifft: Textteil zwischen zwei Zeichen löschen
von: Joe
Geschrieben am: 20.11.2017 17:58:08

Hallo zusammen,

ich hätte folgende Problemstellung und würde mich freuen, wenn ihr mich hierbei unterstützen könntet:

Im Excel habe ich die Zeichenfolgen
00001|"yk,ab,ac,,"|"qwert,x2"|
00002|"bz,hz,,,"|"zuio,bz"|Stein|
...
Nun würde ich gerne die Zeichen ,ab,ac,, bzw. ,hz,,, aus den Feldern löschen. Da in der Datei hunderte von Datensätzen sind fällt eine manuelle Bereinigung weg.

Über eure Hilfe würde ich mich sehr freuen.

LG
Joe

  

Betrifft: AW: Textteil zwischen zwei Zeichen löschen
von: Hajo_Zi
Geschrieben am: 20.11.2017 18:03:48

Hallo Joe,


Tabelle1

 AB
1300001|"yk,ab,ac,,"|"qwert,x2"|00001|"yk,,ac,,"|"qwert,x2"|
1400002|"bz,hz,,,"|"zuio,bz"|Stein|00002|"bz,,,,"|"zuio,bz"|Stein|

verwendete Formeln
Zelle Formel Bereich N/A
B13:B14=WECHSELN(WECHSELN(WECHSELN(A13;"ab";"");"Ac";"");"hz";"")  
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.14 einschl. 64 Bit


GrußformelHomepage

Ich gebe keinen Dank für eine Rückmeldung, da ich durch solche Beiträge nicht meine Beitragszahl erhöhen muss.
Also ich schreibe keine Beiträge mit dem Betreff "Gerne u. Danke für die Rückmeldung. o.w.T."
Rückmeldung ist ja in der Heutigen Zeit nicht üblich und die wenigen die eine Rückmeldung geben,
mögen mir das verzeihen, das kein Danke für eine Rückmeldung kommt.

Beiträge von Werner, Luc, robert und folgende lese ich nicht.


  

Betrifft: AW: Textteil zwischen zwei Zeichen löschen
von: UweD
Geschrieben am: 20.11.2017 18:06:38

Hallo

- Alle Zellen markieren
- Suchen und ersetzen
- suchen nach ,ab,ac,,
- ersetzen durch (Nichts)


das Gleiche für ,hz,,,

LG UweD


  

Betrifft: AW: Textteil zwischen zwei Zeichen löschen
von: Joe
Geschrieben am: 20.11.2017 18:16:15

Hallo UweD und Hajo_Zi,

vielen Dank für eure Antworten.
Bei den Zeichen ist die Gegebenheit, dass diese sich immer unterscheiden und die Lösungen so leider nicht umsetzbar sind.

LG
Chris


  

Betrifft: dann musst Du konkreter werden
von: Matthias L
Geschrieben am: 20.11.2017 18:30:16

Hallo

Ich vermute mal Du willst bei

00001|"yk,ab,ac,,"|"qwert,x2"|

als Ergebnis:
00001|"yk"|"qwert,x2"|

Gruß Matthias


  

Betrifft: AW: Textteil zwischen zwei Zeichen löschen
von: Daniel
Geschrieben am: 20.11.2017 18:20:05

HI
ich gehe mal davon aus, dass du immer die Zeichen folge zwischen dem ersten und zweiten "|" löschen willst.

dann mit dieser Formel in einer Hilfsspalte:

=LINKS(A1;FINDEN("|";A1))&TEIL(A1;FINDEN("|";A1;FINDEN("|";A1)+1);9999)
wenn der erste Text (die Zahlenfolge) immer aus 5 Zeichen besteht, auch etwas einfacher:
=LINKS(A1;6)&TEIL(A1;FINDEN("|";A1;7);9999)
Gruß Daniel


  

Betrifft: nach dem 1. Komma im 2. Segment löschen
von: lupo1
Geschrieben am: 20.11.2017 19:35:17

B1: =WECHSELN(A1;","&
WECHSELN((TEIL(
WECHSELN(GLÄTTEN(TEIL(
WECHSELN(A1;"|";WIEDERHOLEN(" ";99));100;99))
;",";","&WIEDERHOLEN(" ";99));100;298));" ";);)


Das kann man sich hier aneignen: http://www.office-hilfe.com/support/showthread.php/22254


  

Betrifft: klassische Alternative mit zählendem WECHSELN
von: lupo1
Geschrieben am: 20.11.2017 19:48:51

B1: =WECHSELN(A1;TEIL(A1;
SUCHEN("^";WECHSELN(A1;",";"^";1));1+
SUCHEN("°";WECHSELN(A1;",";"°";4))-
SUCHEN("^";WECHSELN(A1;",";"^";1)));)



  

Betrifft: nach dem 1. Komma im 2. Segment löschen - kürzer
von: lupo1
Geschrieben am: 20.11.2017 20:00:20

B1: =WECHSELN(A1;","&WECHSELN(TEIL(WECHSELN(A1;",";","&WIEDERHOLEN(" ";99));100;298);" ";);)


  

Betrifft: ...und alle noch kürzer! ;-)
von: Luc:-?
Geschrieben am: 21.11.2017 03:59:49

Morrn, Lupo & Joe;
Deine Fmln sind bisher die einzigen, die das von Matti vermutete Ergebnis liefern, aber doch ziemlich lang… ;-]
Damit erhält man also ff Ergebnisse für die beiden Bspp:
00001|"yk"|"qwert,x2"|
00002|"bz"|"zuio,bz"|Stein|
Ich hätte hier allerdings weniger an WECHSELN als an ERSETZEN gedacht, zB:
=ERSETZEN(A1;SUCHEN(",";A1);SUCHEN("""";A1;SUCHEN(",";A1))-SUCHEN(",";A1);"")
Diese Fml wäre schon mal nur ca halb so lang. Man könnte aber auch definierte Namen für gleiche TeilFmln oder UDFs in einer Fml verwenden, hier mal Letzteres anstelle von Ersterem für die obige Fml:
=ERSETZEN(A1;Rmd(SUCHEN(",";A1));SUCHEN("""";A1;Rmd())-Rmd();"")
Die UDF Rmd (s.unten!) ist eine sog MerkFkt, deren Aufgabe darin besteht, ein Teilergebnis zu merken und in der gleichen Fml an der gewünschten Stelle wieder anzuwenden. Also im Prinzip das, was auch mit einem definierten Namen erreichbar wäre. Rmd „merkt“ sich aber nicht nur Werte, sondern, falls möglich, auch Objekte, also analog INDEX ggf ZellBereiche. Ich habe sie aber auch in einer anderen Fml dieser Art, die aber deutlich länger ist und eine weitere UDF zur Ermittlung des 2.-3.Arguments von ERSETZEN benutzt, angewendet:
=ERSETZEN(A1; Rmd(VLike(A1;"*|""??,*,*,*,*""|*";-1)+4);VLike(A1;"*|""??,*,*,*,*""|*";1)-Rmd()-1;"")
Mit der UDF VLike kann genau nach der in den Bspp zu erkennenden Struktur des 2.Blockes gefragt wdn. Existiert diese nicht, kann auch nichts ersetzt wdn und ERSETZEN liefert hier wg des dann negativen 3.Arguments #WERT!. In der LangForm (ohne Rmd) sähe die Fml dann so aus:
=ERSETZEN(A1;VLike(A1;"*|""??,*,*,*,*""|*";-1)+4;VLike(A1;"*|""??,*,*,*,*""|*";1)-VLike(A1; "*|""??,*,*,*,*""|*";-1)-5;"")
Das wäre dann schon meine längste, aber immer noch kürzer als Lupos kürzeste. Meine kürzeste wäre übrigens eine mit einer anderen, recht einfachen, alten UDF, die sich aber (deshalb) aufhängen kann, wenn die TextStruktur nicht passt (also PickOn nur anwenden, wenn alle Texte passen!):
=WECHSELN(PickOn(A1;"1,5,6";",");",";"";1)
Mit dem 2.Argument der UDF wird vorgegeben, welche TextTeile, die sich über die Kommata definieren, ausgewählt wdn sollen. D.h., im 2.Block müssen immer 4 Kommata vorhanden sein und ab dem 3.Block darf nur noch ein Komma auftreten! Aber auch Lupos Fmln haben Probleme, wenn die TextStruktur nicht stimmt. Die lange liefert uU ein verstümmeltes Ergebnis, die kurze #WERT!
Ungefähr so lang wie die 1. ERSETZEN-Fml mit Rmd wäre die folgd Fml auf WECHSELN-Basis mit der den OriginalText splittenden UDF Splint, mit der auch gleichzeitig eine Auswahl aus den erzeugten TextTeilen getroffen wdn kann:
=WECHSELN(A1; Splint(A1;"|";2;2);LINKS(Splint(A1;"|";2;2);3)&"""")
Hier wird der komplette 2.Block durch seinen 1.Begriff ersetzt. Man kann hier aber auch zusätzlich die bereits bekannte UDF VLike mit der Xl-Fkt TEIL verwenden, was nachfolgd Fml ergäbe:
=WECHSELN(A1;Splint(A1;"|";2;2);TEIL(A1;VLike(A1;"*|""??,*,*,*,*""|*";-1)+1;3)&"""")
WECHSELN-Anwendung ist auch für eine weitere Fml vorgesehen, die ebenfalls eine UDF (MaskOn) verwendet, die aber ähnlich wie die ERSETZEN-Fmln den überflüssigen TextTeil auf der Basis von (per Fml) zu setzenden Markierungen gänzlich entfernt:
=WECHSELN(MaskOn(WECHSELN(WECHSELN(A1;",";"´";4);",";"`";1);"mrk`´";1);" """;"""")
Das ist für meinen Geschmack allerdings etwas zuviel gewechselt, was daran liegt, dass die bereits vorhandenen Markierungen nicht für diese Operation taugen.
So, damit wäre ich nun fast durch. Sicher gibt's noch mehr Möglichkeiten, mit und ohne UDFs (mir fallen auch nicht immer gleich die am besten passenden UDFs ein, dafür sind's zuviele), bei großen Datenmengen wäre aber eine spezielle SubProzedur sinnvoll (weil schneller), besonders dann, wenn diese Arbeit öfter mal anfällt. Aber nichtsdestotrotz habe ich doch noch einen speziellen Fml-Exoten, den ich wenigstens vorstellen will, zumal er nicht mal die längste Fml benötigt und außerdem eine INDEX-Anwen­dung zeigt, die die Wenigsten in all meinen Forumsjahren für möglich hielten. Einige kennen das „Phänomen“ aber inzwischen. INDEX, zu dessen (speziellen) Möglichkeiten ich demnächst eine Präsentation veröffentlichen will, liefert normalerweise nicht mehrere Werte so, dass sie in nur einer Zelle vereinigt wdn können. Mit einem speziellen Fml-Zusatz und der Auswahl von 2 Zellen für die Fml, also als von mir so klassifizierte duale MatrixFml, ist es aber doch möglich:
{=WAHL({1.2};""; WECHSELN(VJoin(INDEX(VSplit(A1;",");{1.5.6})&T({1.5.6});",");",";"";1)) }
Diese Fml, die die UDFs VSplit (splitten auf Komma) und VJoin (wieder verbinden mit Komma) benutzt, benötigt 2 Zellen, deren linke durch zusätzliche Anwendung der Xl-Fkt WAHL einen LeerText erhält. Allerdings muss auch noch das 1.Komma nachträglich per WECHSELN entfernt wdn.
Einfach mal alle ausprobieren! Die Links zu den Pgmm (ggf zusätzliche Hinweise auf weitere UDFs und Enumerationen beachten!) liegen in den Fmln unter den unterstrichenen Namen (maussensitiv!).

Rem Merkt Argument u.gibt stets zuletzt angegebenes zurück
'   (remind); jetzt auch ZellBezüge (Objekte)!
'   Vs1.1 -LSr:CyWorXxl cd:201202__ -fpub:20171121 -lupd:20170902n
Function Rmd(Optional Ausdruck)
    Static Vorwert As Variant
    If Not IsMissing(Ausdruck) Then
        If TypeName(Ausdruck) = "Range" Then
            Set Vorwert = Ausdruck
        Else: Vorwert = Ausdruck
        End If
    End If
    If TypeName(Vorwert) = "Range" Then
        Set Rmd = Vorwert
    Else: Rmd = Vorwert
    End If
End Function
Feedback nicht unerwünscht! Gruß, Luc :-?

„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …


  

Betrifft: Rmd ist wichtig ...
von: lupo1
Geschrieben am: 21.11.2017 06:47:51

... und wird (eingebaut als Funktion) durch mich in Excel immer schon schmerzlich vermisst.

Laurent Longré hat dies in Morefunc schon versucht oder geliefert. Ich habe es nie angewendet oder gebraucht, da ich nie Add-ins verwende, aber GETV und SETV, wie es dort heißt, oder Rmd in einer XLL- oder .DLL- oder COM-AddIn-Variante vereinen Kompaktheit, Schnelligkeit und Hilfszellen-Verzicht.

Zu meinen längeren Formeln dieses Threads: Mein erster Versuch erlaubt auch unbeachtliche Kommata im ersten Segment der |-Unterteilungen. Ich war gedanklich am Anfang davon noch gefangen, da es ja immerhin um den Extrakt innerhalb eines "|"-Feldes innerhalb eines Satzes ging. Im dritten und ersten "Feld" sollte alles möglich sein dürfen.

Dessen Verkürzung im dritten Versuch mag etwas länger sein, als das immer wieder geniale ERSETZEN, aber es benötigt nur 2mal statt 5mal die Nennung von A1. Und das auch nur, weil hier ein Teilstring aus einem Vollstring herausgeschnitten wird. Ansonsten sogar immer nur 1mal. Vorteilhaft, weil die Funktion in manchen Fällen auf eine gesonderte Hilfszelle A1 verzichten kann, sondern auch dessen Ermittlung gleich aufnehmen kann. Letzteres kommt, zugegeben, selten vor.


  

Betrifft: Danke! Habe aber auch UDF-Fmln mit nur 1xiger …
von: Luc:-?
Geschrieben am: 21.11.2017 12:51:02

…Nennung von A1 dabei, Lupo! ;-)
Gruß, Luc :-?


  

Betrifft: Alles andere wäre ja auch sonderbar ;-)
von: lupo1
Geschrieben am: 21.11.2017 12:55:36

... aber ich bin kein UDF-Verwender, wie ich schon mal schrieb. Höchstens Sub-.


  

Betrifft: Ich weiß, aber auf COMAddIns musst du noch warten!
von: Luc:-?
Geschrieben am: 21.11.2017 12:57:49

;-]
Luc :-?


  

Betrifft: Rmd ("Remind") stünde MS gut als Festeinbau an
von: lupo1
Geschrieben am: 21.11.2017 13:34:47

... aber möglicherweise haben sie noch keinen Weg gefunden, das im Speichermodell für höchste Performance zu definieren.

1 Rmd ist übrigens evtl. zu wenig. 4 bis 9 sollten es schon sein, um genug Luft zu haben.


  

Betrifft: IdR fktioniert 1 Fml auch mit nur 1 Rmd, sogar ...
von: Luc:-?
Geschrieben am: 21.11.2017 14:46:49

…für unterschiedliche TeilFmln, Lupo,
wenn man bei ihrer Platzierung die fml-interne BerechnungsReihenfolge beachtet. Ansonsten habe ich auch noch andere, analoge UDFs.
MS wird das sicher nicht tun, denn es gibt ja auch die Möglichkeit, einen Namen dafür zu definieren (um SchreibAufwand einzu­spa­ren). Dem Fml-Interpreter dürfte es eh egal sein, denn er wird gleiche TeilFmln ohnehin nur 1× berechnen.
Das Ganze ist ja auch nur deshalb von gewisser Relevanz, weil MS dieses umständliche Fml-Prinzip, das sich an GleichungsLösung orientiert, benutzt. Hätte MS sich an ArbeitsAnweisungen orientiert (Schritt­folgen mit interner Bewahrung und Durchreichung von ZwischenErgebnissen), wäre auch die Notation, nicht nur die Berechnung, redundanzfrei.
Luc :-?


  

Betrifft: Variablenschachtelung ...
von: lupo1
Geschrieben am: 23.11.2017 07:24:28

... ist fürwahr anspruchsvoll, da auch in Formeln eine nicht durchbrechbare Kapselungslogik besteht. In einer "Nebenkapsel" entstehende Variablen könnten also von der Logik her nicht verwendet werden; es sei denn, eine Formel würde vorab interpretiert und einzelne Variablenspeicher gefüllt.

Bist Du sicher, dass mittlerweile EXAKT gleiche Formelteile erkannt und nur einmal gerechnet werden? Wo steht das, falls Du es weißt?


  

Betrifft: Thesentest: "Gleiches in Fmln wd nur 1x gerechnet"
von: lupo1
Geschrieben am: 23.11.2017 08:15:47

Hallo Luc, ;)

erst mal vielen Dank für die schöne Formulierung, dass aus singulären Sprach- und Excel-Profis noch keine fehlerfreie Dokumentation entstehen kann; hat mir gut gefallen! Das ewige Problem der Interdisziplinarität.
_________________________________________________________________________________________
Hier meine Performance-Überprüfung mit 500.000 Formeln zum Betreff:

A1[:A500000]: =WENNFEHLER(
SVERWEIS(ZEICHEN(REST(ZEILE();10)+65);F$1:G$9;2;0);
SVERWEIS(ZEICHEN(REST(ZEILE();10)+65);F$1:G$9;2;1))
benötigt 13 Sekunden ("sprachgezählt")

B1[:B500000]: =WENN(ISTFEHLER(
SVERWEIS(ZEICHEN(REST(ZEILE();10)+65);F$1:G$9;2;0));
SVERWEIS(ZEICHEN(REST(ZEILE();10)+65);F$1:G$9;2;1);
SVERWEIS(ZEICHEN(REST(ZEILE();10)+65);F$1:G$9;2;0))
benötigt 20 Sekunden ("sprachgezählt")

Versuchsergebnis:

Es ergibt sich ein zeitliches Berechnungsverhältnis von 2:3, genau wie die Anzahl enthaltener SVERWEISe, trotz der enthaltenen Wiederholung in der zweiten Formel (kursiver Teil, in beiden Formeln dargestellt). Es passt zur Gegenbehauptung:

"Excel erkennt gleiche Ausdrücke innerhalb einer Formel nicht!",

und als evtl. Störfaktor sind unterschiedliche Leistungen von ISTFEHLER zu WENNFEHLER nicht anzunehmen. Also:

"Es werden zumindest nicht in jedem Falle gleiche Ausdrücke in Excelformeln vorab oder on-the-fly ermittelt".

Versuchskritik: Aber vielleicht reicht ja ein Test bzw. ein Testaufbau noch nicht aus?

Versuchsanmerkung 1: Surface Pro 4 m3, 4 GB, 128 GB SSD, XL2010

Versuchsanmerkung 2: Die jeweils andere der beiden Formelspalten A und C wurde natürlich gelöscht, obwohl sie mangels Berührung des Abhängigkeitsbaums nicht erneut gerechnet worden wäre.

Versuchsanmerkung 3: mit Daten in F1:G9:
A 10
B 9
C 8
D 7
F 6
G 5
H 4
I 3
J 2


  

Betrifft: MS hält sich da bedeckt und schreibt nur ...
von: Luc:-?
Geschrieben am: 23.11.2017 21:53:41

…allgemein von FormelOptimierung, Lupo,
worunter man ja alles mögliche verstehen kann. Bei WENN und WAHL ist das ubU nachweisbar, in solchen Fällen wie in deinen Bspp könnte es vermutet wdn, muss aber nicht sein. Das hängt ganz davon ab, wie der FmlText-Interpreter pgmmiert wurde (denn möglich wäre es, falls nicht zu komplex).
Hinzu kommt ggf, dass die Optimierung weder konsequent war noch ausreichend an (spätere) Hinzufügungen angepasst wurde, weil das mittlerweile ein nachgeordnetes Problem für MS sein mag (bzw schon immer war?).
Luc :-?


  

Betrifft: AW: Thesentest: "Gleiches in Fmln wd nur 1x gerechnet"
von: Daniel
Geschrieben am: 24.11.2017 09:31:04

Es gibt auch im Standardexcel eine ganz einfache Methode, wie man erreichen kann, dass sich wiederholende Teilformeln nur einmal und nicht mehrfach berechnet werden:
- man lagert die Teilformel in eine Hilfszelle aus und verweist in der Hauptformel anstelle der Teilformel auf die Hilfszelle.

Gruß Daniel


  

Betrifft: Das wusste ich noch nicht. Danke!
von: lupo1
Geschrieben am: 24.11.2017 10:34:56




  

Betrifft: ;-)) owT
von: Luc:-?
Geschrieben am: 24.11.2017 14:38:04

:-?


  

Betrifft: AW: Textteil zwischen zwei Zeichen löschen
von: Joe
Geschrieben am: 21.11.2017 19:37:36

Hallo zusammen,

ich merke schon hier sind die Experten am Werk ;)
Die Lösungsvorschläge vom Daniel und lupo1 habe ich ausprobiert und funktionieren klasse! Die weiteren Lösungen werde ich auch noch unter die Lupe nehemen.

Vielen Dank für eure hilfreichen Antworten!

LG
Joe


  

Betrifft: Wenn Lupos Fmln das Gewünschte liefern, ...
von: Luc:-?
Geschrieben am: 21.11.2017 20:37:52

…können Daniels das nicht auch tun, Joe,
denn sie zeigen ein anderes Ergebnis!
Luc :-?