Microsoft Excel

Herbers Excel/VBA-Archiv

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

Textaufzählung unterteilen

Betrifft: Textaufzählung unterteilen von: Sebatian
Geschrieben am: 13.10.2014 16:17:02

Guten Tag,

für eine empirische Auswertung habe ich eine Aufzählung von Ansprüchen, die ich nun gerne durch eine Formel unterteilen würde.

Die Ansprüche werden durch 1., 2., 3. usw. in einer Zelle aufgelistet. Als Ziel möchte ich später die Länge jedes einzelnen Anspruchs durch die LÄNGE Funktion berechnen. Hierzu müss ich die Ansprüche allerdings zunächst einzeln aus der Zelle extrahieren. Ich habe es schon mit der LINKS, RECHTS, TEIL Funktion probiert, allerdings komme ich hierbei irgendwie nicht weiter. Könnt ihr mir helfen? Vielen Dank im Voraus!

Hier mal ein Textbeispiel:

1. A METHOD FOR ACCESSING A VIRTUAL FILE ON A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE BY EITHER A HOST OPERATING SYSTEM OR AN APPLICATION RUNNING ON SAID HOST OPERATING SYSTEM SAID METHOD COMPRISING THE UTILIZATION OF A VIRTUAL DISK DRIVE CONTROLLER DRIVER TO ATTACH SAID VIRTUAL HARD DRIVE TO SAID HOST OPERATING SYSTEM WHEREIN SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER INTERFACES WITH A DISK CONTROL DRIVER TO ACCESS A FILE ON A PHYSICAL HARD SAID FILE CORRESPONDING TO SAID VIRTUAL HARD DRIVE. 2. THE METHOD OF CLAIM 1 FURTHER COMPRISING A GUEST OPERATING SYSTEM FOR EXECUTION ON SAID VIRTUAL MACHINE BUT WHEREIN SAID GUEST OPERATING SYSTEM IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 3. THE METHOD OF CLAIM 2 WHEREIN SAID VIRTUAL MACHINE IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 4. THE METHOD OF CLAIM 1 WHEREIN SAID FILE COMPRISES A PLURALITY OF FILES. 5. A METHOD FOR A HOST OPERATING SYSTEM TO ACCESS A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE SAID METHOD COMPRISING SUBMITTING A PRIMARY REQUEST PERTAINING TO A VIRTUAL FILE ON SAID VIRTUAL HARD DRIVE TO A VIRTUAL HARD DRIVE CONTROLLER DRIVER SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER SUBMITTING A SECONDARY REQUEST PERTAINING TO A FILE ON A PHYSICAL HARD DRIVE TO A DISK CONTROLLER DRIVER FOR SAID PHYSICAL HARD DRIVE WHEREIN SAID FILE CORRESPONDS TO SAID VIRTUAL HARD DRIVE SAID DISK CONTROLLER DRIVER FULFILLING THE SECONDARY REQUEST OF SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER WITH REGARD TO SAID FILE AND SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER FULFILLING THE PRIMARY REQUEST WITH REGARD TO SAID VIRTUAL FILE. 6. THE METHOD OF CLAIM 5 WHEREIN SAID PRIMARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 7. THE METHOD OF CLAIM 6 WHEREIN SAID SECONDARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID FILE. 8. THE METHOD OF CLAIM 5 WHEREIN SAID PRIMARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 9. THE METHOD OF CLAIM 8 WHEREIN SAID SECONDARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID FILE. 10. A SYSTEM FOR ACCESSING A VIRTUAL FILE ON A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE BY EITHER A HOST OPERATING SYSTEM OR AN APPLICATION RUNNING ON SAID HOST OPERATING SYSTEM SAID SYSTEM COMPRISING AT LEAST ONE SUBSYSTEM FOR UTILIZING A VIRTUAL DISK DRIVE CONTROLLER DRIVER TO ATTACH SAID VIRTUAL HARD DRIVE TO SAID HOST OPERATING SYSTEM WHEREIN SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER INTERFACES WITH A DISK CONTROL DRIVER TO ACCESS A FILE ON A PHYSICAL HARD SAID FILE CORRESPONDING TO SAID VIRTUAL HARD DRIVE. 11. THE SYSTEM OF CLAIM 10 FURTHER COMPRISING A GUEST OPERATING SYSTEM FOR EXECUTION ON SAID VIRTUAL MACHINE BUT WHEREIN SAID GUEST OPERATING SYSTEM IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 12. THE SYSTEM OF CLAIM 11 WHEREIN SAID VIRTUAL MACHINE IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 13. THE SYSTEM OF CLAIM 10 WHEREIN SAID FILE COMPRISES A PLURALITY OF FILES. 14. A SYSTEM FOR A HOST OPERATING SYSTEM TO ACCESS A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE SAID SYSTEM COMPRISING AT LEAST ONE SUBSYSTEM FOR SUBMITTING A PRIMARY REQUEST PERTAINING TO A VIRTUAL FILE ON SAID VIRTUAL HARD DRIVE TO A VIRTUAL HARD DRIVE CONTROLLER DRIVER SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER SUBMITTING A SECONDARY REQUEST PERTAINING TO A FILE ON A PHYSICAL HARD DRIVE TO A DISK CONTROLLER DRIVER FOR SAID PHYSICAL HARD DRIVE WHEREIN SAID FILE CORRESPONDS TO SAID VIRTUAL HARD DRIVE SAID DISK CONTROLLER DRIVER FULFILLING THE SECONDARY REQUEST OF SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER WITH REGARD TO SAID FILE AND SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER FULFILLING THE PRIMARY REQUEST WITH REGARD TO SAID VIRTUAL FILE. 15. THE SYSTEM OF CLAIM 14 WHEREIN SAID PRIMARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 16. THE SYSTEM OF CLAIM 15 WHEREIN SAID SECONDARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID FILE. 17. THE SYSTEM OF CLAIM 14 WHEREIN SAID PRIMARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 18. THE SYSTEM OF CLAIM 17 WHEREIN SAID SECONDARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID FILE. 19. A COMPUTER-READABLE MEDIUM COMPRISING COMPUTER-READABLE INSTRUCTIONS FOR ACCESSING A VIRTUAL FILE ON A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE BY EITHER A HOST OPERATING SYSTEM OR AN APPLICATION RUNNING ON SAID HOST OPERATING SYSTEM SAID COMPUTER-READABLE INSTRUCTIONS COMPRISING INSTRUCTIONS FOR THE UTILIZATION OF A VIRTUAL DISK DRIVE CONTROLLER DRIVER TO ATTACH SAID VIRTUAL HARD DRIVE TO SAID HOST OPERATING SYSTEM WHEREIN SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER INTERFACES WITH A DISK CONTROL DRIVER TO ACCESS A FILE ON A PHYSICAL HARD SAID FILE CORRESPONDING TO SAID VIRTUAL HARD DRIVE. 20. THE COMPUTER-READABLE MEDIUM OF CLAIM 19 FURTHER COMPRISING INSTRUCTIONS FOR A GUEST OPERATING SYSTEM FOR EXECUTION ON SAID VIRTUAL MACHINE BUT WHEREIN SAID GUEST OPERATING SYSTEM IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 21. THE COMPUTER-READABLE MEDIUM OF CLAIM 20 FURTHER COMPRISING INSTRUCTIONS WHEREBY SAID VIRTUAL MACHINE IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 22. THE COMPUTER-READABLE MEDIUM OF CLAIM 19 FURTHER COMPRISING INSTRUCTIONS WHEREBY SAID FILE COMPRISES A PLURALITY OF FILES. 23. A COMPUTER-READABLE MEDIUM COMPRISING COMPUTER-READABLE INSTRUCTIONS FOR A HOST OPERATING SYSTEM TO ACCESS A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE SAID COMPUTER-READABLE INSTRUCTIONS COMPRISING INSTRUCTIONS FOR SUBMITTING A PRIMARY REQUEST PERTAINING TO A VIRTUAL FILE ON SAID VIRTUAL HARD DRIVE TO A VIRTUAL HARD DRIVE CONTROLLER DRIVER SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER SUBMITTING A SECONDARY REQUEST PERTAINING TO A FILE ON A PHYSICAL HARD DRIVE TO A DISK CONTROLLER DRIVER FOR SAID PHYSICAL HARD DRIVE WHEREIN SAID FILE CORRESPONDS TO SAID VIRTUAL HARD DRIVE SAID DISK CONTROLLER DRIVER FULFILLING THE SECONDARY REQUEST OF SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER WITH REGARD TO SAID FILE AND SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER FULFILLING THE PRIMARY REQUEST WITH REGARD TO SAID VIRTUAL FILE. 24. THE COMPUTER-READABLE MEDIUM OF CLAIM 23 WHEREIN SAID PRIMARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 25. THE COMPUTER-READABLE MEDIUM OF CLAIM 24 WHEREIN SAID SECONDARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID FILE. 26. THE COMPUTER-READABLE MEDIUM OF CLAIM 23 WHEREIN SAID PRIMARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 27. THE COMPUTER-READABLE MEDIUM OF CLAIM 26 WHEREIN SAID SECONDARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID FILE. 28. A HARDWARE CONTROL DEVICE FOR ACCESSING A VIRTUAL FILE ON A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE BY EITHER A HOST OPERATING SYSTEM OR AN APPLICATION RUNNING ON SAID HOST OPERATING SYSTEM SAID DEVICE COMPRISING MEANS FOR THE UTILIZATION OF A VIRTUAL DISK DRIVE CONTROLLER DRIVER TO ATTACH SAID VIRTUAL HARD DRIVE TO SAID HOST OPERATING SYSTEM WHEREIN SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER INTERFACES WITH A DISK CONTROL DRIVER TO ACCESS A FILE ON A PHYSICAL HARD SAID FILE CORRESPONDING TO SAID VIRTUAL HARD DRIVE. 29. THE HARDWARE CONTROL DEVICE OF CLAIM 28 FURTHER COMPRISING MEANS WHEREBY A GUEST OPERATING SYSTEM TO EXECUTE ON SAID VIRTUAL MACHINE BUT WHEREIN SAID GUEST OPERATING SYSTEM IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 30. THE HARDWARE CONTROL DEVICE OF CLAIM 29 FURTHER COMPRISING MEANS WHEREBY SAID VIRTUAL MACHINE IS NOT EXECUTED DURING THE STEPS OF SAID METHOD. 31. THE HARDWARE CONTROL DEVICE OF CLAIM 28 FURTHER COMPRISING MEANS WHEREBY SAID FILE COMPRISES A PLURALITY OF FILES. 32. A HARDWARE CONTROL DEVICE FOR A HOST OPERATING SYSTEM TO ACCESS A VIRTUAL HARD DRIVE OF A VIRTUAL MACHINE SAID DEVICE COMPRISING MEANS FOR SUBMITTING A PRIMARY REQUEST PERTAINING TO A VIRTUAL FILE ON SAID VIRTUAL HARD DRIVE TO A VIRTUAL HARD DRIVE CONTROLLER DRIVER SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER SUBMITTING A SECONDARY REQUEST PERTAINING TO A FILE ON A PHYSICAL HARD DRIVE TO A DISK CONTROLLER DRIVER FOR SAID PHYSICAL HARD DRIVE WHEREIN SAID FILE CORRESPONDS TO SAID VIRTUAL HARD DRIVE SAID DISK CONTROLLER DRIVER FULFILLING THE SECONDARY REQUEST OF SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER WITH REGARD TO SAID FILE AND SAID VIRTUAL HARD DRIVE CONTROLLER DRIVER FULFILLING THE PRIMARY REQUEST WITH REGARD TO SAID VIRTUAL FILE. 33. THE HARDWARE CONTROL DEVICE OF CLAIM 32 FURTHER COMPRISING MEANS WHEREBY SAID PRIMARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 34. THE HARDWARE CONTROL DEVICE OF CLAIM 33 FURTHER COMPRISING MEANS WHEREBY SAID SECONDARY REQUEST PERTAINS TO A READ OPERATION CORRESPONDING TO SAID FILE. 35. THE HARDWARE CONTROL DEVICE OF CLAIM 32 FURTHER COMPRISING MEANS WHEREBY SAID PRIMARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID VIRTUAL FILE. 36. THE HARDWARE CONTROL DEVICE OF CLAIM 35 FURTHER COMPRISING MEANS WHEREBY SAID SECONDARY REQUEST PERTAINS TO A WRITE OPERATION CORRESPONDING TO SAID FILE.

  

Betrifft: und wieso zeigste das nich in ner Bsp-Datei?..owT von: Oberschlumpf
Geschrieben am: 13.10.2014 16:33:27




  

Betrifft: z.B. für den Text 6. von: WF
Geschrieben am: 13.10.2014 16:37:06

Hi,

=TEIL(A1;FINDEN("6.";A1);FINDEN("7.";A1)-FINDEN("6.";A1))

Salut WF


  

Betrifft: AW: Textaufzählung unterteilen von: Daniel
Geschrieben am: 13.10.2014 16:39:56

Hi
so wie es aussieht, stehen innerhalb der Sätze keine Punkte, sondern nur am Ende des Satzes und dann nochmal nach der Nummer.
Du könntest also mit DATEN - DATENTOOLS - TEXT IN SPALTEN und dem Punkt als Trennzeichen die Aussagen in einzelne Zellen aufteilen.
Dabei stehen dann die Aussagen in jeder zweiten Spalte, dazwischen steht die Nummer.
Gruß Daniel


  

Betrifft: AW: Textaufzählung unterteilen von: Sebatian
Geschrieben am: 13.10.2014 17:54:41

Vielen Dank für Eure Antworten! Die Formel von WF erledigt alles wunderbar!

Angenommen die Zelle enthält jedoch nur EINEN Anspruch. Logischerweise wird mir dann der Fehler "#WERT!" angezeigt, da ja nix abgezogen werden kann (Es gibt höchstens ein 1. und keine weitere Aufzählung). Eine manuelle Anpassung wäre nicht gut, da es sich um mehrere 1000 Datensätze handelt.

Gibt es einen Weg das man die Formel so ergänzt, dass in diesem Fall der gesamte Text aus der Zelle mit den Ansprüchen kopiert wird und somit der Fehler #WERT! verschwindet?


  

Betrifft: na ja, ginge schon von: WF
Geschrieben am: 13.10.2014 18:22:45

Hi,

wäre dann aber ne komplizierte Formel.

Die letzte Aufzählungs-Nr. ergibt #WERT!, da ja keine Folgenummer mehr kommt.

Vorschlag: gib beim ersten mal, wenn #WERT! erscheint folgende Formel ein (hier war's bei 36 der Fall):
=TEIL(A$1;FINDEN(ZEILE(A36)&".";A$1);999)

Kann man auch grundsätzlich/allgemein einbauen - dann wird die Formel aber irre lang.

WF


  

Betrifft: Das geht mit einer dafür geeigneten UDF auch ... von: Luc:-?
Geschrieben am: 13.10.2014 19:46:17

…mit einer kürzeren ZellFml, Sebastian;
dann natürlich als mehrzellige MatrixFml 1spaltig über hier 36 Zeilen:
A5:A40: {=GROSS2(GLÄTTEN(MTRANS(SplitVx(" "&A1;" "&ZEILE(INDIREKT("1:"&MAX(WENNFEHLER(--SplitVx(A1;".");0))))&". ";-1))))}
GROSS2 ist hier Zugabe (kann auch KLEIN sein oder weggelassen wdn). Mauszeiger mal auf den 1.UDF-Namen bewegen und Info lesen!
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Das geht mit einer dafür geeigneten UDF auch ... von: Ewald
Geschrieben am: 15.10.2014 15:55:00

Hallo,

dieses Makro zeigt dir alles an,

da brauchst du nicht nochmal zählen. anstatt Msgbox kann auch in die Tabelle geschrieben werden.

Sub Info()
Dim a
Dim b
Dim str
Dim zif
Dim arr
Dim i
a = ActiveCell.Value
If IsNumeric(Mid(a, 1, 1)) And Mid(a, 2, 1) = "." Then
    str = 1 & ";"
    zif = Mid(a, 1, 2) & ";"
End If
For i = 2 To Len(a)
    If IsNumeric(Mid(a, i, 1)) And Mid(a, i + 1, 1) = "." Then
        If IsNumeric(Mid(a, i - 1, 1)) Then
        str = str & i - 1 & ";"
        zif = zif & Mid(a, i - 1, 3) & ";"
        Else
        str = str & i & ";"
        zif = zif & Mid(a, i, 2) & ";"
        End If
    End If
Next
str = str & Len(a)
arr = Split(str, ";")
MsgBox "Anfangspositionen der Methoden" & Chr(13) & str
MsgBox "Methodennummern" & Chr(13) & zif
For i = 0 To UBound(arr) - 1
If i < UBound(arr) - 1 Then
b = Mid(a, arr(i), arr(i + 1) - 1 - arr(i))
Else
b = Mid(a, arr(i), arr(i + 1) - arr(i) + 2)
End If
MsgBox "Methode " & i + 1 & " hat folgenden Text " & Chr(13) & b & Chr(13) & "und eine Länge  _
von " & Len(b) & " Zeichen"
Next
End Sub
Gruß Ewald