Mittleren Teil eines Textfeld extrahieren

Moderator: ModerationP

Mittleren Teil eines Textfeld extrahieren

Beitragvon Twiti » 27. Sep 2019, 08:36

Hallo Forum,
ich kämpfe gerade mit einem Excel Formel welche ich für ein Access Datenbank (o365) in eine Abfrage benötige.
Ziel ist es aus einem Kurztext "BU01_10-SPO9050-44-P-005A/B_00299JA0" alle Zeichen bis zum "_" und alle Zeichen ab das erste "_" von rechts zu entfernen damit folgendes übrig bleibt "10-SPO9050-44-P-005A/B".
Bei Fehler sollte der String aus dem Kurztext übernommen werden.
Hier der Excel Formel:
Code: Alles auswählen
=WENNFEHLER(WENN(H150="";"";TEIL(H150;6;LÄNGE(H150)-LÄNGE(TEIL(H150;MAX(WENN(TEIL(H150;SPALTE($1:$1);1)="_";SPALTE($1:$1)));55))-5));H150)

Spalte H ist der Spalte mit Kurztext.

Die extrahierte Zeichenfolge sollte in der Spalte Zeichnungsnummer wiedergegeben werden.
Für das Abschneiden des letzten (nach "_") Teils war ich schon auf dem Weg mit folgendem Code:
Code: Alles auswählen
Zeichnungsnummer: Glätten(Links([Kurztext];InStrRev([Kurztext];"_")-1))


Ich hab schon vieles durchprobiert, komm aber nicht zum Ergebnis.
Danke für die Hilfe.
Twiti
Windows 10/Office 2010
Dragon NaturallySpeaking statt Maus und Tastatur wegen Schwerbehinderung
Twiti
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 222
Registriert: 23. Apr 2016, 13:53

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Gast » 27. Sep 2019, 09:33

Für den mittleren Teil eines Textstrings gibt es die Funktion Mid (Teil).
Die Startposition kann man über Instr ermitteln, die zu verwendende Länge aus der Differenz von Instr und InstrRev.

BU01_10-SPO9050-44-P-005A/B_00299JA0

Solche Datenkonglomerate als Tabellenfeldinhalt sind regelmäßig anstrengend bis unbrauchbar. Dass Du beim Trennen an sich schon Probleme hast, merkst Du selber. Daneben ist es einfach Aufwand, wenn man das pro Inhalt wieder und wieder ausführen müsste.
Daher würde man vorrangig solche Inhalte einmalig in atomare Informationen zerlegen und diese in getrennten Feldern speichern. Das entspricht auch der Forderung der ersten Normalform.
Praktisch ist dann ein Zusammensetzen viel einfacher und schneller als ein Trennen.
Gast
 

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Yaslaw » 27. Sep 2019, 10:11

Wie der Gast schreibt.

Des weiteren lohnt es sich für solch komplexten Anforderungen eine keine Funktion zu schreiben
Zum Beispeil meine Funktion fromSet()
Diese arbeitet mit Split und Array. Das geht, weil das Trennzeichen auf beiden Seiten gleich ist und es dazwischen nicht vorkommt.
Ansonsten rate ich wie immer zu einer kleinen Funktion mittels RegEx

Test mit fromSet():
Code: Alles auswählen
?fromSet(1, "BU01_10-SPO9050-44-P-005A/B_00299JA0", "_")
10-SPO9050-44-P-005A/B
'Problem, _ im gesuchten Text
?fromSet(1, "BU01_10________-P-005A/B_00299JA0", "_")
10


Anwendung in der Abfrage
Code: Alles auswählen
=fromSet(1; H150; "_")


Mit regEx (bevorzugte Version) -> Test und Erläuterung des Patterns
Code: Alles auswählen
Public Function getMyPart(ByVal iValue As Variant) As Variant
    Static rx As Object
    If rx Is Nothing Then
        Set rx = CreateObject("VBScript.RegExp")
        rx.pattern = "^[^_]*_(.*)_[^_]*$"
    End If
    getMyPart = iValue
    If rx.test(NZ(iValue)) Then getMyPart = rx.replace(NZ(iValue), "$1")
End Function


Test
Code: Alles auswählen
?getMyPart("BU01_10-SPO9050-44-P-005A/B_00299JA0")
10-SPO9050-44-P-005A/B
'Kein Problem, _ im gesuchten Text
?getMyPart("BU01_10________-P-005A/B_00299JA0")
10________-P-005A/B


Anwendung in der Abfrage
Code: Alles auswählen
=getMyPart(H150)
item: Ich habe es mir aus gesundheitlichen Gründen abgewöhnt unformatierten Code zu lesen (Auch SQL-Statements sind formatierbar)
item: Schreibt mir keine PN mit Fragen die im Forum beantwortet werden können - ich mache kein persönliches coaching
Benutzeravatar
Yaslaw
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 2923
Registriert: 02. Jul 2014, 15:25
Wohnort: Winterthur

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon knobbi38 » 27. Sep 2019, 11:01

@Yaslaw:
kleine Anmerkung zu fromSet():
Wenn du in der Deklaration "ByVal iSet As String" durch "ByVal iSet As Variant" ersetzen würdest, gäbe NZ(iSet) einen Sinn, aber so könnte man es auch weglassen, da iSet niemals NULL sein kann.

Hallo Twiti,

in einem anderem Forum hatte einer genau das gleiche Problem. Hier eine kleine Funktion, die ich mal dafür geschrieben habe, welche auch in einer Abfrage oder einem Ausdruck verwendet werden kann:
Code: Alles auswählen
Public Function fnParseText(ByVal Value As Variant, ByVal Index As Integer, _
    Optional Delimiter As String = ";") As Variant
  Dim avntValue As Variant

  fnParseText = Null

  If IsNull(Value) Then Exit Function

  On Error Resume Next

  avntValue = Split(Value, Delimiter)
  fnParseText = avntValue(Index - 1)

  If Err.Number <> 0 Then
    fnParseText = Null
  End If
End Function

Der Aufruf wäre dann z.B.:
LinkerTeil = fnParseText([Feldname],1,"_")
MittelTeil = fnParseText([Feldname],2,"_")
RechterTeil = fnParseTest([Feldname],3,"_")

Gruß Ulrich
knobbi38
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1491
Registriert: 02. Jul 2015, 14:23

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Yaslaw » 27. Sep 2019, 11:21

knobbi38 hat geschrieben:@Yaslaw:
kleine Anmerkung zu fromSet():
Wenn du in der Deklaration "ByVal iSet As String" durch "ByVal iSet As Variant" ersetzen würdest, gäbe NZ(iSet) einen Sinn, aber so könnte man es auch weglassen, da iSet niemals NULL sein kann.

Ach Mist, der Klugscheisser hat recht :)
Merci für den Hinweis. Variant ist eh besser, dann kann an aus der Abfrage einfach das Feld mitgeben und muss sich nicht noch um Null kümmern.
Hab die Funktion angepasst.
item: Ich habe es mir aus gesundheitlichen Gründen abgewöhnt unformatierten Code zu lesen (Auch SQL-Statements sind formatierbar)
item: Schreibt mir keine PN mit Fragen die im Forum beantwortet werden können - ich mache kein persönliches coaching
Benutzeravatar
Yaslaw
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 2923
Registriert: 02. Jul 2014, 15:25
Wohnort: Winterthur

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Gast » 27. Sep 2019, 11:39

Das drumherum verdeutlicht recht anschaulich, dass man eine solche Aufspaltung in atomare Informationen besser nur einmal ausführen sollte oder noch besser bei Möglichkeit das Zusammenkleben solcher gleich an der Quelle unterbindet, auch wenn solche Ausdrücke sehr wichtig aussehen.
Gast
 

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Gast » 27. Sep 2019, 11:47

Bezüglich weglassen:
Code: Alles auswählen
Public Function fnParseText(...) As Variant
  ...
  fnParseText = Null
  ...

Der Initialwert bei einer Variablen/Funktion vom Typ Variant ist definiert NULL, eine Extrazuweisung daher entbehrlich.
Gast
 

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon knobbi38 » 27. Sep 2019, 13:32

@Gast:

in dem Fall könnte man das auch weglassen.

Wenn man lieber eine Leersting übergeben möchte, kann man das an dieser Stelle schnell ändern und dann wird nur noch im Fehlerfall eine NULL zurückgegeben. Hat also nur praktische Gründe und deshalb steht es dort.

Gruß
knobbi38
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1491
Registriert: 02. Jul 2015, 14:23

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon knobbi38 » 27. Sep 2019, 13:33

@Yaslaw: 8-)

Grüße Ulrich
knobbi38
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1491
Registriert: 02. Jul 2015, 14:23

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Twiti » 30. Sep 2019, 05:56

Hallo Ulrich,
Sorry dass ich erst jetzt antworte und mich bedanke.
War dann doch viel komplizierter als ich es gedacht hatte, mit nur einem Formel in der Aufruf funktioniert es wohl nicht.

Jetzt habe ich versucht im Falle eines Fehlers (z.B.: es gibt kein 2 "_ "in den Kurztext) dies folgendermaßen zu lösen:
Code: Alles auswählen
Zeichnungsnummer: wenn(fnParseText([Kurztext];2;"_")="";[Kurztext];fnParseText([Kurztext];2;"_")

funktioniert leider nicht, es sollte der Spalte Zeichnungsnummer bei einem Fehler nicht leer bleiben sondern den Kurztext übernehmen.
Wo muss ich hier ansetzen? Eventuell in der Funktion?

Danke im Voraus.
Twiti
Windows 10/Office 2010
Dragon NaturallySpeaking statt Maus und Tastatur wegen Schwerbehinderung
Twiti
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 222
Registriert: 23. Apr 2016, 13:53

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Yaslaw » 30. Sep 2019, 07:11

Deine Aktuelle Version von fnParseText?
item: Ich habe es mir aus gesundheitlichen Gründen abgewöhnt unformatierten Code zu lesen (Auch SQL-Statements sind formatierbar)
item: Schreibt mir keine PN mit Fragen die im Forum beantwortet werden können - ich mache kein persönliches coaching
Benutzeravatar
Yaslaw
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 2923
Registriert: 02. Jul 2014, 15:25
Wohnort: Winterthur

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon knobbi38 » 30. Sep 2019, 12:21

Hallo Twiti,

die Funktion fnParseText gibt im Fehlerfall eine NULL zurück, deshalb klappt der Vergleich auf "" (Leerstring) nicht.
Der Prüfung fnParseText([Kurztext];2;"_")="" müßte in dem Fall so aussehen:
Code: Alles auswählen
isNull(fnParseText([Kurztext];2;"_")
oder
nz(fnParseText([Kurztext];2;"_"),"") = ""

wobei die erste Variante einfacher wäre.

Die Funktion selber würde ich nicht ändern, denn die könnte auch an anderen Stellen wiederverwendet werden.

Gruß Ulrich
knobbi38
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1491
Registriert: 02. Jul 2015, 14:23

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Ein_anderer_Gast » 01. Okt 2019, 08:30

Gast hat geschrieben:Bezüglich weglassen:
Code: Alles auswählen
Public Function fnParseText(...) As Variant
  ...
  fnParseText = Null
  ...

Der Initialwert bei einer Variablen/Funktion vom Typ Variant ist definiert NULL, eine Extrazuweisung daher entbehrlich.

Diese Aussage ist nicht richtig, zumindest wenn die Auswertung in VBA erfolgt.

VBA-Beispiel-Funktionen in einem Modul:
Code: Alles auswählen
Public Function MitNullZuweisung() As Variant
  MitNullZuweisung = Null
End Function

Public Function OhneNullZuweisung() As Variant
End Function

Aufrufe und Ergebnisse im VBA-Direktbereich:
Code: Alles auswählen
? "  1."  MitNullZuweisung() "  2."  MitNullZuweisung()="" "  3."  IsNull(MitNullZuweisung()) "  4."  IsEmpty(MitNullZuweisung())
  1.Null  2.Null  3.Wahr  4.Falsch


? "  5." OhneNullZuweisung() "  6." OhneNullZuweisung()="" "  7." IsNull(OhneNullZuweisung()) "  8." IsEmpty(OhneNullZuweisung())
  5.  6.Wahr  7.Falsch  8.Wahr

Ohne Wert-Zuweisung ist der Zustand von der Variant-Funktion Empty mit entsprechenden Vergleichsergebnissen (="" ergibt Wahr, IsNull() ergibt Falsch).
Ein_anderer_Gast
 

Re: Mittleren Teil eines Textfeld extrahieren

Beitragvon Twiti » 02. Okt 2019, 14:25

Dank an allen,
der Lösung von Ulrich funktioniert
VG
Twiti
Windows 10/Office 2010
Dragon NaturallySpeaking statt Maus und Tastatur wegen Schwerbehinderung
Twiti
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 222
Registriert: 23. Apr 2016, 13:53


Zurück zu Access Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 7 Gäste