Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

In 300 Zeichen 4 Bestimmte finden | Herbers Excel-Forum


Betrifft: In 300 Zeichen 4 Bestimmte finden von: Sebastian
Geschrieben am: 06.08.2012 14:52:50

Hallo Zusammen,

Ich habe wieder mal ein Spezielles Problem...:

In einer Zelle steht eine seeehr lange Zeichenfolge (ca 200 Zeichen), die mit Zahlen und Text aus Wörtern und Kutzzeichen gemsicht ist. An einer Stelle kommt eine Numerische Zeichenfolge vor, die IMMER 4-stellig ist und eine Buchhaltungs-Kontonummer repräsentiert. Diese ist nicht mmer gleich.
Das Problem ist nun: Derzeit weiß ich, dass die Kontonummer immer an der gleichen Stelle steht und ich bekomme diesen Wert über die Formel Mid(LangerString, 123, 4) heraus.

Wenn die Datei aber von einem anderen Kunden ausgewertet wird.

Wie erreiche ich, dass diese Zahl egal wo Sie steht erkannt wird? Problem dabei ist.... dass das durchsuchen des Strings schwierig ist, weil zuvor bereits 4-stellige Zahlen auftauchen (z.B. einmal das aktuelle Jahr), und über eine IF-abfrage IsNumeric(Left(Langerstring,4) = True in einer Suchschleife funktioniert das nicht, weil bereits vorher eine Zahlenwert auftauchen könnte, der 5 Stellig ist, und damit würde er über diese Funktion auch als Ergebnis gewertet.

Im Folgenden habe ich mal so einen typischen String (Der ist die Überschrift für darauf folgende Datensätze, die aber nicht die Konteninformation enthlten. Diese Info schreibe ich nach Finden der Zahl dann in die einzelnen Zeilen dazu.... das funktioniert auch soweit.):

Kanzlei-Rechnungswesen pro - RW - 13725/45323/2012 / Firma Musterman - Kontoblatt - Individueller Kontenstapel; Jahreskonto 6850 Sonstiger Betriebsbedarf.

Die Gesuchte Zahl ist die Zahl hinter dem Wort "Jahreskonto". Das Einzige, was ich bis jetzt als "einzigartig" in diesem String für die Kontonummer erkenen kann, ist, dass vor und nach der Kontonummer jeweils ein Leerzeichen steht. Das scheint immer so zu sein. Aber ich bekomme das irgendwie nicjt hin, per VBA den String so durchsuchen u lassen, dass am Ende nur die "6850" übrig bleibt. Diese Zahl könnte auch anders heißen. Wichtig ist, dass ich die Zahl irgendwie in dem String finden und identifizieren.

Freue mich über jeden Tipp.

Dake schon mal...

Freundliche Grüße

Sebastian

  

Betrifft: AW: In 300 Zeichen 4 Bestimmte finden von: Rudi Maintaire
Geschrieben am: 06.08.2012 15:19:36

Hallo,
1 String ist ein bisschen karg.
Teste mal:

Function KontoNr(strTxt As String) As Integer
  Dim i As Integer, arrTmp
  arrTmp = Split(strTxt)
  For i = 0 To UBound(arrTmp)
    If Len(arrTmp(i)) = 4 And IsNumeric(arrTmp(i)) Then
      KontoNr = arrTmp(i) * 1
      Exit Function
    End If
  Next
End Function

Gruß
Rudi


  

Betrifft: AW: In 300 Zeichen 4 Bestimmte finden von: fcs
Geschrieben am: 06.08.2012 15:25:49

Hallo Sebastian,

mit der folgenden Function "fncJahreskonto" werden die 4 Zeichen nach "Jahreskonto " zurückgegeben.

Falls das letzte ";" oder "/" immer vor/links von "Jahreskonto " steht, dann kann man den Text davor vor der Suche abtrennen - siehe Function "fncJahreskonto2".

Gruß
Franz

Sub aaTest()
  Dim strJK As String, strLangerText As String
  strLangerText = Cells(2, 1) 'Text in A2
  strJK = fncJahreskonto(strText:=strLangerText)
  strJK = fncJahreskonto2(strText:=strLangerText)
  
End Sub

Function fncJahreskonto(ByVal strText As String) As String
  Dim Pos1 As Long, strSuch
  strSuch = "Jahreskonto "
  Pos1 = InStr(1, strText, strSuch) + Len(strSuch)
  fncJahreskonto = Mid(strText, Pos1, 4)
End Function

Function fncJahreskonto2(ByVal strText As String) As String
  Dim Pos1 As Long, strSuch
  'Untersucht Text nach dem letzten "/"
  strSuch = "/"
  Pos1 = InStrRev(strText, strSuch)
  If Pos1 > 0 Then strText = Mid(strText, Pos1 + 1)
  strSuch = "Jahreskonto "
  Pos1 = InStr(1, strText, strSuch) + Len(strSuch)
  fncJahreskonto2 = Mid(strText, Pos1, 4)
End Function



  

Betrifft: AW: In 300 Zeichen 4 Bestimmte finden von: ingoG
Geschrieben am: 06.08.2012 16:28:58

Hallo Sebastian,

mit folgender Matrixformel findest Du die ersten 4 Ziffern, welche von 2 Leerstellen eingerahmt sind...

=TEIL(A1;MIN(WENN((TEIL(A1;ZEILE($1:$300);1)=" ")*(TEIL(A1;ZEILE($6:$305);1)=" ") *(NICHT(ISTFEHLER(TEIL(A1;ZEILE($2:$301);4)*1)));ZEILE($2:$301)));4)

(Eingabe abschließen mit Strg-Shift-Enter)

durchsucht werden bis zu 300 Zeichen lange Strings.

Natürlich besteht auch hier die Gefahr, dass die Suche schon vorher fündig wird, je nach dem welche Textpassagen dort vorkommen können.

Aber vielleicht hilft Dir das ja schon weiter...

Gruß Ingo

PS eine Rückmeldung wäre nett...


  

Betrifft: Virstellige Zahlen finden - mit RegExp von: Erich G.
Geschrieben am: 06.08.2012 17:35:11

Hi Sebastian,
so ginge das mit RegExp:

 BCDE
3Da sind 12345 und 4321 und 584 drin. Am Ende steht 987643219876#BEZUG!
4Da sind 12345 und 4321 und 584 drin.4321  

Formeln der Tabelle
ZelleFormel
C3=INDEX(ZahlInTxt($B$3); SPALTE(A1))
D3=INDEX(ZahlInTxt($B$3); SPALTE(B1))
E3=INDEX(ZahlInTxt($B$3); SPALTE(C1))
C4=ZahlInTxtK($B4)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Und der Code dazu (in ein normales Modul):
Option Explicit

Public Function ZahlInTxt(rngC) As Variant
   Dim strText As String
   Dim Regex As Object, Alle As Object, treffer As Object
   Dim tmp As Variant, L As Integer

   ReDim tmp(0)
   strText = " " & rngC.Value & " "
   Set Regex = CreateObject("Vbscript.Regexp")
   With Regex
      .Pattern = " [0-9]{4} "
      .Global = True
      Set Alle = .Execute(strText)
   End With

   ReDim tmp(0 To Alle.Count - 1)
   For Each treffer In Alle
      tmp(L) = 1 * treffer.Value
      L = L + 1
   Next
   ZahlInTxt = tmp
End Function

Public Function ZahlInTxtK(rngC) As Variant
   Dim Regex As Object, ObjAlle As Object

   Set Regex = CreateObject("Vbscript.Regexp")
   With Regex
      .Pattern = " [0-9]{4} "
      .Global = True
      Set ObjAlle = .Execute(" " & rngC.Value & " ")
   End With
   ZahlInTxtK = 1 * ObjAlle(0)
End Function
Die erste Funktion gibt ein Array zurück - für den Fall, dass es mehrere Treffer gibt.
ZahlInTxtK gibt immer nur den 1. Treffer zurück.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Vierstellige Zahlen finden - mit RegExp von: Sebastian
Geschrieben am: 06.08.2012 20:43:59

Hallo Erich,



Das Sieht bereits gut aus, trifft aber leider nicht ganz das Problem.

Ich hatte vergessen zu erwähnen, dass die Zahl nicht bekannt ist, die gesucht wird.

Die Einzige Eigenschaft, die bekannt ist, dass sie vierstellig ist und von 2 Leerzeichen eingeschlossen wird.

Es gibt in dem String auch nur einmal so eine Zahl, die von Leerzeichen eingeschlossen wird.



Trotzden Vielen Dank erst mal. :-)



Ich versuche mal da entlang zu denken. Es gibt in der Arbeitsmappe ein Array, in dem die Zahlen stehen, die für die Auswertung am Schluss benutzt werden dürfen. Vielleicht kann ich die darüber in einer Prozedur oder Funktion filtern... Dieses Array kommt erst viel später ins Spiel, wenn bereits alle Sachkonten identifiziert worden sind. Vielleicht muss ich das in diesen Auswahlprozess mit einbauen.


Ich probier das mal aus...


Freundliche Grüße aus dem Frankenland :-)


  

Betrifft: AW: Vierstellige Zahlen finden - mit RegExp von: Josef Ehrensberger
Geschrieben am: 06.08.2012 20:58:14


Hallo Sebastian,

vielleicht so.

Tabelle1

 AB
1Kanzlei-Rechnungswesen pro - RW - 13725/45323/2012 / Firma Musterman - Kontoblatt - Individueller Kontenstapel; Jahreskonto 6850 Sonstiger Betriebsbedarf.6850
2Andere Kanzlei Blabla xy 9999/7777/2011/ Firma B. A. N. Krott - KontoKonto 1557 BlaBlaBla Trallalla1557
3  

Formeln der Tabelle
ZelleFormel
B1{=TEIL(A1;MAX(WENN(ISTZAHL(TEIL(A1;ZEILE(1:300); 4)*1); ZEILE(1:300)))-1;4)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4






« Gruß Sepp »



  

Betrifft: AW: Vierstellige Zahlen finden - mit RegExp von: Sebastian
Geschrieben am: 07.08.2012 08:23:32

Hallo Sepp,

Sieht gut aus. Probiere ich mal. Allerdings ist das nicht in einer Prozedur enthalten.
Trotzdem Danke....

Ich werde heute mal herumprobieren mit den Vorschlägen.

kann aber eine Weile dauern.

Freundliche Grüße

Sebastian


  

Betrifft: AW: Vierstellige Zahlen finden - mit RegExp von: Erich G.
Geschrieben am: 07.08.2012 01:28:54

Hi Sebastian,
schau dir meine Funktion bitte noch mal an.
"Ich hatte vergessen zu erwähnen, dass die Zahl nicht bekannt ist, die gesucht wird.
Die einzige Eigenschaft, die bekannt ist, dass sie vierstellig ist und von 2 Leerzeichen eingeschlossen wird."

Ich denke, genau das habe ich umgesetzt. Hier noch mal ein paar Beispiele, auch mit Ingos und Sepps Lösung:

 ABCD
1a 4567 v 9876 x456745679876
2adf 66666 5555 zzz555555555555
3a 87654 v 9876 9876987676
4a 12345 b#WERT!#WERT!2345
5    
6a_4567_v_9876_x456745679876
7adf_66666_5555_zzz555555555555
8a_87654_v_9876_9876987676_
9a_12345_b#WERT!#WERT!2345

Formeln der Tabelle
ZelleFormel
B1{=TEIL(A1;MIN(WENN((TEIL(A1;ZEILE($1:$300); 1)=" ")*(TEIL(A1;ZEILE($6:$305); 1)=" ") *NICHT(ISTFEHLER(TEIL(A1;ZEILE($2:$301); 4)*1)); ZEILE($2:$301))); 4)}
C1=ZahlInTxtK(A1)
D1{=TEIL(A1;MAX(WENN(ISTZAHL(TEIL(A1;ZEILE($1:$300); 4)*1); ZEILE($1:$300)))-1;4)}
B2{=TEIL(A2;MIN(WENN((TEIL(A2;ZEILE($1:$300); 1)=" ")*(TEIL(A2;ZEILE($6:$305); 1)=" ") *NICHT(ISTFEHLER(TEIL(A2;ZEILE($2:$301); 4)*1)); ZEILE($2:$301))); 4)}
C2=ZahlInTxtK(A2)
D2{=TEIL(A2;MAX(WENN(ISTZAHL(TEIL(A2;ZEILE($1:$300); 4)*1); ZEILE($1:$300)))-1;4)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Die Beispiele in den Zeilen 1 bis 4 habe ich in die Zeilen 5 bis 9 kopiert und dort die Leerzeichen durch Unterstriche ersetzt,
damit man mehr sieht.

Man sieht die Übereinstimmung zwischen Ingos und meinen Ergebnissen (bis auf die Zahl-Umwandlung).
Man sieht auch, dass Sepps Formel in einigen Fällen kein richtiges Ergebnis liefert.


Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Vierstellige Zahlen finden - mit RegExp von: Sebastian
Geschrieben am: 07.08.2012 08:14:55

Hallo Erich,



Das sieht ja richtig gut aus so, obwohl ich es noch nicht wirklich verstehe...

Mein Problem ist jetzt, dass ich das nicht als Formel im Tabellenblatt erreichen muss, sondern innerhalb einer Prozedur. Ich muss diesen String in in eine Prozedur einlesen, weil das Ergebnis, welches später im Tabellenblatt steht keine Formel sein darf, sondern nur die schlichte, vierstellige Zahl.



Ich hoffe, ich wirke da nicht zu anspruchsvoll in dem, was ich da bauen möchte.

Ich muss um ein wenig Geduld bitten, weil ich die Dinge heute erst einmal ausprobieren will. Aber ich muss noch ein paar andere Problemchen in dem Ganzen lösen und werde wohl erst heute Nachmittag dazu kommen die Lösungen, die Du gepostet hast, auszuprobieren.



Bis jetzt sieht die "ZahlInTxtK" - Lösung für mich am Besten aus, weil ich sie auch in eine Prozedur einbauen kann.



Trotzdem vielsten Dank, für die Hilfe, Anregung und das Engagement aller Beteiligten.



Freundliche Grüße



Sebastian


  

Betrifft: Vierstellige Zahlen finden - RegExp in Prozedur von: Erich G.
Geschrieben am: 07.08.2012 10:27:33

Hi Sebastian,
so kannst du das in VBA auch ohne Tabellenblattbezug einsetzen:

Option Explicit

Sub Testprogramm()
   Dim strBeisp As String
   
   strBeisp = "a 123 4567 xx 9876"
   MsgBox ZahlInTxtK(strBeisp)
   
   strBeisp = "a 123 567 xx 987"
   MsgBox ZahlInTxtK(strBeisp)
End Sub

Public Function ZahlInTxtK(strTxt As String) As Variant
   Dim Regex As Object, ObjAlle As Object

   Set Regex = CreateObject("Vbscript.Regexp")
   With Regex
      .Pattern = " [0-9]{4} "
      .Global = True
      Set ObjAlle = .Execute(" " & strTxt & " ")
   End With
   If ObjAlle.Count > 0 Then
      ZahlInTxtK = 1 * ObjAlle(0)
   Else
      ZahlInTxtK = "Nichts gefunden"
   End If
End Function
Eine Frage wäre noch zu klären:
Was soll die Funktion zurückgeben, wenn sie keine vierstellige Zahl im Text findet?
Hier wird jetzt eine Meldung als Text ausgegeben.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Vierstellige Zahlen finden - RegExp in Prozedur von: Sebastian
Geschrieben am: 07.08.2012 10:31:51

Hallo Erich,

Wenn die Funktion nichts Findet, wird sie eine "0" zurückgeben.
Das macht aber nichts...
Diese Null wird dan in alle folgenden Zeilen geschrieben.
Das ist aber nicht schlimm, da es "immer" (Ja ich weiß...es wird bestimmt ausnahmen geben...) die Vierstelligen Kontonummern enthalten

Danke für deine Hilfe erst mal. Werde das heute mnoch probieren.

Freundliche Grüße

Sebastian


Beiträge aus den Excel-Beispielen zum Thema "In 300 Zeichen 4 Bestimmte finden"