Text sinnvoll auf Länge in Spalten verteilen ?

Zwiebackliebhaber · 10. November 2019 um 03:55

Guten Tag,

ich bin auf der Suche nach einer Formel oder Makro in der ich den Text einer Zelle sinnvoll auf mehrere Spalten verteilen kann.
Hintergrund ist, das ich in einer Zelle Artikelbezeichnungen habe die bis zu 80 Zeichen lang sind. Ich muss diese Bezeichnung nun in ein Template von drei mal 30 Zeichen bringen. Wie kann ich dies sinnvoll realisieren?
Natürlich könnte man einfach nach 30Zeichen mit =Teil(Zelle;1;30) abschneiden, dies produziert aber zu 95% Kauderwelsch.
Hier ein Beispiel:
„Heute ist Donnerstag und zusätzlich ist der 1.November teilweise ein Feiertag“
Ich hätte nun gerne ein Aufteilung wie folgt:
„Heute ist Donnerstag und“
„zusätzlich ist der 1.November“
„teilweise ein Feiertag“
Ist so etwas realisierbar, also Trennung nach Leerzeichen und automatisch immer max. 30 Zeichen, ohne manuell mit Text in Spalten arbeiten zu müssen?
Hoffentlich habe ich mich verständlich ausgedrückt.
Vielen Dank vorab
Gruß
Jürgen

Helmut_Schultze · 10. November 2019 um 03:55

Hi, Jürgen,

das Mittel der Wahl wäre wohl ein Progrämmchen, das folgendes tut:

Speichere den String aus der Zelle in der Variablen strText;
Speichere den Leerstring als strNeuerInhalt;
Solange Länge von strText > 30 Zeichen
_ Suche von Stelle 30 aus rückwärts eine Leerstelle im String;
_ Speichere die Position unter intPosition;
_ Speichere die Zeichen von Beginn strText bis intPosition-1 als strNeuerInhalt;
_ Speichere strNeuerInhalt + Zeilenende als strNeuerInhalt;
_ Speichere den String von intPosition+1 bis Ende strText als strText;
Mach weiter solange;
Speichere strNeuerInhalt + strText als strNeuerInhalt;
Speicher strNeuerInhalt in der Zelle;

Gruß, Helmut

Reinhard_fb8cb7 · 10. November 2019 um 03:55

ich bin auf der Suche nach einer Formel oder Makro in der ich
den Text einer Zelle sinnvoll auf mehrere Spalten verteilen
kann.
Hintergrund ist, das ich in einer Zelle Artikelbezeichnungen
habe die bis zu 80 Zeichen lang sind. Ich muss diese
Bezeichnung nun in ein Template von drei mal 30 Zeichen
bringen. Wie kann ich dies sinnvoll realisieren?

Hi Jürgen,
wie soll denn der genaue Ablauf sein, angenommen du startest das Makro über eine Tastenkombination.?

Ausgehend von der gerade aktiven Zelle sollen die Ergebnisse in die 3 Zellen rechts daneben?
wie 1), nur für mehrere selektierte Zellen untereinander?
eine komplette festgelegte Spalte, zB G soll komplett (also alle Zellen mit Daten) nach H,I,J zersplittet werden.
Dir Artbez. steht in beliebiger Zelle, z.B. K5, du gibst in L5 ein: =Split1(K5), in M5: =Split2(K5), usw.
[Split1() usw sind UDFs, also BDFs, also benutzerdefinierte Funktionen. Split1 holt die ersten „30“, Split2 die zweiten „30“.

Gruß
Reinhard

Zwiebackliebhaber · 10. November 2019 um 03:55

Hallo Reinhard,

habe mit Makros bisher keine Erfahrung und würde falls Du mir ein entsprechendes Makro schreiben könntest ganz Deiner Anweisung wie und wo ich es installieren muss und wie es zu starten ist folgen.
Ich habe die Artikelbezeichnung immer fest in Spalte A und muss in die Spalten B,C und D, also nach rechts splitten. Da der Datensatz sehr groß ist, über 100000(Excel2007) wäre mir damit sehr geholfen.

Gruß und Danke für Nachfrage
Jürgen

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Georg_V · 10. November 2019 um 03:55

Guten Tag,

gelöscht :
Hintergrund ist, das ich in einer Zelle Artikelbezeichnungen
habe die bis zu 80 Zeichen lang sind. Ich muss diese
Bezeichnung nun in ein Template von drei mal 30 Zeichen
bringen. Wie kann ich dies sinnvoll realisieren?
Natürlich könnte man einfach nach 30Zeichen mit
=Teil(Zelle;1;30) abschneiden, dies produziert aber zu 95%
Kauderwelsch.
Hier ein Beispiel:
„Heute ist Donnerstag und zusätzlich ist der 1.November
teilweise ein Feiertag“
Ich hätte nun gerne ein Aufteilung wie folgt:
„Heute ist Donnerstag und“
„zusätzlich ist der 1.November“
„teilweise ein Feiertag“
gelöscht :

Hallo Jürgen,

das Problem muss man von hinten angehen . Man nimmt sich 30 Zeichen von rechts und sucht sich das nächste Leerzeichen.
Annahme: Text steht in A1

B1=TEIL(A1;1;LÄNGE(A1)-LÄNGE(C1)-LÄNGE(D1))
C1=TEIL(A1;LÄNGE(A1)-30+F1-LÄNGE(D1);30)
D1=TEIL(A1;LÄNGE(A1)-30+G1;30)
F1=FINDEN(" ";RECHTS(A1;30+LÄNGE(D1));1)
G1=FINDEN(" ";RECHTS(A1;30);1)

Zur Sicherheit sollte man noch eine Warnungmeldung einbauen, wenn der Text in B1 länger als 30 Zeichen wird (Murphy ist überall).

E1=WENN(LÄNGE(B1)\>30;"mehr als 30 Zeichen";"")

Das ergibt übrigens nur rein zufällig die gleiche Aufteilung, die Du auch im Beispiel gewählt hast.

MfG Georg V.

Reinhard_fb8cb7 · 10. November 2019 um 03:56

das Problem muss man von hinten angehen . Man nimmt sich 30
Zeichen von rechts und sucht sich das nächste Leerzeichen.

Hallo Georg,
mit Murphys Gesetz hast du recht, aber von rechts nach links halte ich für falsch.
Wenn das Teil 50 Zeichen lang ist, wird die Aufteilung 30-20 gewünscht und nicht 20-30*glaub*
Wobei ich deine Formeln gar nicht probiert habe *zugeb*
Gruß
Reinhard

Annahme: Text steht in A1

B1=TEIL(A1;1;LÄNGE(A1)-LÄNGE(C1)-LÄNGE(D1))
C1=TEIL(A1;LÄNGE(A1)-30+F1-LÄNGE(D1);30)
D1=TEIL(A1;LÄNGE(A1)-30+G1;30)
F1=FINDEN(" „;RECHTS(A1;30+LÄNGE(D1));1)
G1=FINDEN(“ ";RECHTS(A1;30);1)

Zur Sicherheit sollte man noch eine Warnungmeldung einbauen,
wenn der Text in B1 länger als 30 Zeichen wird (Murphy ist
überall).

E1=WENN(LÄNGE(B1)>30;„mehr als 30 Zeichen“;"")

Das ergibt übrigens nur rein zufällig die gleiche Aufteilung,
die Du auch im Beispiel gewählt hast.

MfG Georg V.

Zwiebackliebhaber · 10. November 2019 um 03:56

Hallo Jürgen,

das Problem muss man von hinten angehen . Man nimmt sich 30
Zeichen von rechts und sucht sich das nächste Leerzeichen.
Annahme: Text steht in A1

B1=TEIL(A1;1;LÄNGE(A1)-LÄNGE(C1)-LÄNGE(D1))
C1=TEIL(A1;LÄNGE(A1)-30+F1-LÄNGE(D1);30)
D1=TEIL(A1;LÄNGE(A1)-30+G1;30)
F1=FINDEN(" „;RECHTS(A1;30+LÄNGE(D1));1)
G1=FINDEN(“ ";RECHTS(A1;30);1)

Zur Sicherheit sollte man noch eine Warnungmeldung einbauen,
wenn der Text in B1 länger als 30 Zeichen wird (Murphy ist
überall).

E1=WENN(LÄNGE(B1)>30;„mehr als 30 Zeichen“;"")

Das ergibt übrigens nur rein zufällig die gleiche Aufteilung,
die Du auch im Beispiel gewählt hast.

MfG Georg V.

Hallo Georg,

vielen Dank für Deine Hilfe.
Im genannten Beispiel funktioniert es bestens.
Ich habe es dann mit einem anderen Datensatz ausprobiert, da funzt es leider nicht mehr???
Wenn der Datensatz z.B. lautet:
Datenlichtschranke DAD10-8P-SDS
dann bekomme ich folgendes Ergebnis:
#WERT! | ichtschranke DAD10-8P-SDS | DAD10-8P-SDS | #WERT! | 19 | 18 |
Hast Du oder jemand anderes noch eine Idee?
Grundsätzlich können auch Datensätze vorhanden sein unter 31 Zeichen, die dann natürlich unberührt bleiben sollen. Ok ich könnte natürlich vorab mit =Länge den Datensatz sortieren und erst ab 31 Zeichen anfangen.

Gruß
Jürgen

Georg_V · 10. November 2019 um 03:56

gelöscht :
Grundsätzlich können auch Datensätze vorhanden sein unter 31
Zeichen, die dann natürlich unberührt bleiben sollen. Ok ich
könnte natürlich vorab mit =Länge den Datensatz sortieren und
erst ab 31 Zeichen anfangen.

Gruß
Jürgen

Hallo Jürgen,

das ist eine wichtige Information! Und bevor Du nachher mit noch einen Fehler kommst: Dieser Algorithmus funktioniert nur, wenn die Leerzeichen untereinander keinen Abstand größer als 30 Zeichen haben und es sollte mindestens ein Leerzeichen vorkommen.

B1=WENN(LÄNGE(A1)\>LÄNGE(D1)+LÄNGE(C1);TEIL(A1;1;LÄNGE(A1)-LÄNGE(C1)-LÄNGE(D1));"")
C1=WENN(LÄNGE(A1)-LÄNGE(D1)\>30;TEIL(A1;LÄNGE(A1)-30+F1-LÄNGE(D1);30);TEIL(A1;1;LÄNGE(A1)-LÄNGE(D1)))
D1=WENN(LÄNGE(A1)
Solltest Du Dir wünschen, dass die Leerfelder am Anfang vermieden werden, dann füge bitte folgende Formel noch ein:


    
    =WENN(B1="";WENN(C1="";D1;C1);B1)
    =WENN(B1=H1;C1;WENN(H1=D1;"";D1))
    =WENN(B1=H1;D1;"")



Dieser Teil ist ungetestet gegen die Verwendung von mehrfachen Leerzeichen (, aber die Funktion GLÄTTEN() ist ja nicht weit).

MfG Georg V.

erichsz · 10. November 2019 um 03:56

MAKRO ist die richtige Lösung
Hallo Jürgen
Mit 100’000 Zeilen und 3 Kolonnen hast du dann 300’000 Zellen mit einer Formel. Willst du dann bei jeder Eingabe deinen processor durchrattern lassen? Und wie machst du es, dass niemand deinen Formelberg verdirbt?
Die beste Lösung ist ein MAKRO ; das erledigt die Sache definitiv in einem Druchgang. Zudem kannst du dann (wenn du willst) die Ausgangsdaten in Spalte A löschen.
Wenn die Profis sich auf die richtige Formeln geeinigt haben ist es nicht mehr weit zum guten Makro: Ich bin überzeugt, dass dir (*grins*) Reinhard dann das Makro „mit links“ schreiben wird.
Viel Erfolg!
Erich

Georg_V · 10. November 2019 um 03:57

Hallo Erich,

auch ich würde bei mehrfachen Gebrauch ein Makro programmieren bzw. bereits an der Quelle der Texte (Artikelbezeichnungen => Verdacht auf Datenbank) anpacken. Hier ging es hauptsächlich darum, der Meinung entgegen zutreten, dass dies nur Mit Makros funktioniert. Auch besitze ich kein Excel 2007 (und werde es mir auch nicht so schnell anschaffen) und deshalb scheidet der Lösungsweg für mich aus. Und da für mich auch mit Excel 2003 65.536 Zeilen kein Hindernis ist (s. meine Homepage www.familieverweyen.de) ist selbst diese Randbedingung kein Problem.

MfG Georg V.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

erichsz · 10. November 2019 um 03:57

Hallo Georg V

Auch besitze ich kein Excel 2007
(und werde es mir auch nicht so schnell anschaffen)

bestimmt nicht!

auch mit Excel 2003 65.536 Zeilen kein Hindernis ist (s. meine
Homepage www.familieverweyen.de) ist selbst diese
Randbedingung kein Problem.

Dies ist nun sehr interessant, nur habe ich es auf deiner homepage nicht gefunden! Wo steht es denn ??
Danke für Hinweis
Erich

Reinhard_fb8cb7 · 10. November 2019 um 03:57

Ich habe die Artikelbezeichnung immer fest in Spalte A und
muss in die Spalten B,C und D, also nach rechts splitten.

Hi Jürgen,

Alt+F11, Einfügen Modul, Code reinkopieren.

Makro Einmalig1() legt den Makrostartaufruf auf die Tastenkombination Strg+u, den Buchstaben kannst du ja ggfs. im Code abändern.

Makro Einmalig2() erstellt eine Schaltfläche, die das Makro startet.

Suche dir aus wie du das Makro starten willst, dann stelle den Cursor irgendwo innerhalb von Einmalig1() oder Einmalig2() und drücke F5.

Jetzt kannst du den Editor schließen.

Ich habe den Code nur sehr kurz getestet, k.A. ob er bei allen Eventualitäten korrekt funktioniert. Und ob er exakt bei 30 trennt oder bei 29,31…

Der Code:

Option Explicit
'
Sub Splitten()
Dim Zei As Long, Spa As Integer, Teil As String, Inhalt As String
Dim Letzte
Letzte = Cells(Rows.Count, 1).End(xlUp).Row
Range("B1:smiley:" & Letzte).ClearContents
For Zei = 1 To Letzte
 Spa = 1
 Inhalt = Cells(Zei, 1).Value
 While Len(Inhalt) \> 30
 If InStrRev(Left(Inhalt, 30), " ") Then
 Teil = Left(Left(Inhalt, 30), InStrRev(Left(Inhalt, 30), " "))
 Else
 Teil = Left(Inhalt, InStr(Inhalt, " ") - 1)
 End If
 Spa = Spa + 1
 Cells(Zei, Spa) = Trim(Teil)
 Inhalt = Mid(Inhalt, Len(Teil) + 1)
 Wend
 If Len(Inhalt) \> 0 Then Cells(Zei, Spa + 1) = Trim(Inhalt)
Next Zei
End Sub
'
Sub Einmalig1()
Application.MacroOptions Macro:="Splitten", Description:="", ShortcutKey:="u"
End Sub
'
Sub Einmalig2()
ActiveSheet.Buttons.Add(180, 198.75, 163.5, 45.75).Select
Selection.OnAction = "Splitten"
Selection.Characters.Text = "Splitten"
Range("A1").Select
End Sub

Gruß
Reinhard

Zwiebackliebhaber · 10. November 2019 um 03:57

Danke
Vielen Dank an alle die hier so hilfreich geantwortet haben.
Letztlich favorisiere ich die Makrolösung von Reinhard.

Gruß
Jürgen

Zwiebackliebhaber · 10. November 2019 um 03:57

Hallo Reinhard,

vielen Dank, klappt bei einem Probedatensatz von 8500 Artikeln fehlerfrei. Härteprüfung erfolgt Montag in der Firma.

Herzlichen Gruß
Jürgen

Georg_V · 10. November 2019 um 03:57

Dies ist nun sehr interessant, nur habe ich es auf deiner
homepage nicht gefunden! Wo steht es denn ??
Danke für Hinweis
Erich

Hallo Erich,

Menü „Excel“, Menü „Excel Makro (II)“, Menü „Excel CSV-Import“ oder kurz http://www.familieverweyen.de/txt_0025.htm

MfG Georg V.

erichsz · 10. November 2019 um 04:00

Hallo Georg V.
Sehr elegant, danke!
Erich