Excel: Text aus Zellen löschen, Zahlen behalten

6 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

Ok, hier mein (etwas unkonventioneller aber hoffentlich brauchbarer) Lösungsansatz:

Die Schwierigkeit in Deiner Aufgabe besteht darin, die erste und die letzte Position einer Ziffer innerhalb des Dateinamens zu finden. Um die Positionen zu ermitteln suche ich mittels FINDEN nach den Zahlen 0..9 am Anfang und am Ende.

Deine Tabelle sollte folgenden Aufbau haben:

  • Zeile 1 bleibt leer.
  • Die Dateinamen stehen in Spalte A ab Zeile 2 (z.B. A2:A100) Jetzt schreibst Du in Zeile 1 ab Spalte B folgende Werte:
  • B1 bis K1: die Ziffern 0 bis 9
  • L1: Min
  • M1..V1: nochmal die Ziffern 0 bis 9
  • W1: Max
  • X1: Artikelnr. Es sollte jetzt wie in Bild 1 aussehen.

In Zelle B2 (unter "0") kommt nun folgende Formel:

 =WENN(ISTFEHLER(FINDEN(TEXT(B$1; "0"); $A2)>=0);"";FINDEN(TEXT(B$1; "0"); $A2))

Diese Formel kopierst Du einfach in alle Zeilen der Spalten B bis K.

In Zelle L2 ("Min") folgende Formel:

 =MIN(B2:K2)

Auch diese Formel in alle Zeilen der Spalte L kopieren.

In Zelle M2 ("0") kommt folgende Formel:

  =WENN(ISTFEHLER(FINDEN(TEXT(M$1; "0"); $A2;$L2+1)>=0);"";FINDEN(TEXT(M$1; "0"); $A2;$L2+1))

Diese nun in alle Zellen der Spalten M bis V einfügen.

In W2 ("Max") steht (analog zu L2)

  =MAX(M2:V2)

Ebenfalls kopieren.

Und zu guter letzt in Spalte X2 ("Artikelnr.")

 =TEIL(A2;L2;W2-L2+1)

Wenn Du alles korrekt eingegeben hast, sollte nun in Spalte X die Artikelnummer stehen. (s. Bild 2) Achte vor allem auf die $-Zeichen der Formeln, sonst geht das Kopieren schief.

Zur Erläuterung: In den Spalten B bis K steht jeweils das erste Vorkommen der Ziffern 0..9 In Spalte L wird der kleinste Wert der Spalten B bis K ermittelt. In den Spalten M bis V steht jeweils das letzte Vorkommen der Ziffern 0..9 In Spalte W wird der größte Wert der Spalten M bis V ermittelt. Und in Spalte X wird einfach der Teilstring zurückgegeben.

Hoffe, es hilft Dir etwas weiter.

Bild 1 - (Excel, Formel, Filter) Bild 2 - (Excel, Formel, Filter)

Hier noch ein kleiner Verbesserungsvorschlag: Statt wie o.g. gib in Spalte M2 lieber folgende Formel ein:

    =MAX((TEIL($A2;SPALTE(1:1);1)=TEXT(M$1; "0"))*SPALTE(1:1))

Achtung: Da es sich hier um eine Matrix-Formel handelt unbedingt die Eingabe mit Shift+Strg+Enter abschließen (zu erkennen, daß in der Eingabezeile die Formel dann in geschweiften Klammern steht)!!

Diese Formel sucht wirklich das letzte Vorkommen eines Zeichens. Die Formel aus der obigen Antwort kann zu Fehlern führen, wenn eine Ziffer mindestens 3-mal in in der Artikelnummer vorkommt und gleichzeitig an letzter Stelle steht. (Aus Schraube045020.jpg käme dann 04502 statt 045020 heraus). Mit der Matrixformel ist auch dieses Problem behoben.

0
@puba07

Vielen Dank für deine Mühe! Allerdings taucht noch ein kleiner Fehler auf: Manche Nummern werden abgeschnitten, es fehlen meist die letzten 1-2 Ziffern.

Ich weiß jetzt nicht wie man hier Bilder einfügt aber der unten stehende Link führt zu nem Screenshot. Hab mal alle Zeilen ausgeblendet wo es geklappt hat.

http://666kb.com/i/btt4yp8dx8f2mm3xq.gif

0
@PhilEs

Hallo PhilEs,

prüfe mal bitte, ob in den Zellen unter Spalt M bis V auch die Formel aus meinem Kommentar steht und ob die in der Eingabezeile in geschweiften Klammern erscheint.

  =MAX((TEIL($A2;SPALTE(1:1);1)=TEXT(M$1; "0"))*SPALTE(1:1))

Habe mal 2 Beispiele von Deinem Bild probiert und bei mir klappt es. Schau mal unter http://dl.dropbox.com/u/17078809/1.jpg nach. Wenns nicht klappt mach mal nen Screenshot, wo die Formel aus Zelle N2 zu sehen ist.

0
@puba07

Ach, Mist, mir ist einfach nur ein Flüchtigkeitsfehler passiert.

Ich hatte deine ursprüngliche Formel für M bis V eingegeben und dann nach deinem Kommentar die Verbesserte Formel nachgetragen aber vergessen, die Formel bis "V" rüberzukopieren....

Jetzt klappt alles! Vielen Dank, das wird mir einiges an Arbeit abnehmen.

0
@PhilEs

Freut mich wenn ich Dir helfen konnte. War auch eine interessante Aufgabe für mich - mal richtig quer zu denken ;-) Vielen Dank!

0

Hast Du die Möglichkeit, mehrere Spalten in Deiner Tabelle als "Hilfsspalten" zu nutzen? Die können dann bei Bedarf auch gern ausgeblendet werden. Dann hätte ich eine Lösung, die offensichtlich auch zuverlässig funktioniert - auch ganz ohne VBA.

Die Excel-Tabelle ist nur für mich. Was da letztendlich drinsteht in den Spalten interessiert nachher niemanden, kann da also soviel rumpfuschen wie ich will, hauptsache ich erreiche mein Ziel^^

0

Ich gehe davon aus, dass nur EIN Zahlenblock vorkommt.
Angenommen Dein Dateiname steht in A1.
Dann zeigt Dir diese Formel den Zahlenteil an:
{=TEIL(A1;MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:30);1)*1)*1)*ZEILE(1:30);0;30))*1);MAX((((ISTZAHL(TEIL(A1;ZEILE(1:30);1)*1)*1)*ZEILE(1:30))))-MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:30);1)*1)*1)*ZEILE(1:30);0;30))*1)+1)}
[ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die {} und macht die Formel zu einer Matrixformel.]
Wahrscheinlich lässt sich das auch einfacher lösen, aber funktionieren sollte es so. Mit Deinen drei Beispielen klappt es jeden Falls. Teste mal ausgiebig. Klappt es.

Korrektur:

In der Formel müssen die 30 noch durch 999 ersetzt werden, sonst würde der Text nur bis zur 30 Stelle "untersucht" (hatte ich zu Testzwecken so eingestellt). Also wie oben, nur diese Formel:
{=TEIL(A1;MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:999);1)*1)*1)*ZEILE(1:999);0;999))*1);MAX((((ISTZAHL(TEIL(A1;ZEILE(1:999);1)*1)*1)*ZEILE(1:999))))-MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:999);1)*1)*1)*ZEILE(1:999);0;999))*1)+1)}

0

Wenn das mit dieser einen Formel hinhaut wäre das ja super. Die kann man sich zur Not auch mal wo wegkopieren.

Ich hab die Dateinamen ab A1 untereinander aufgelistet und in B1 die Formel eingetragen. Hab diese auch ohne die geschweiften Klammern kopiert und mit STRG + Shift + ENTER abgeschlossen, die geschweiften Klammern sind daraufhin auch erschienen.

Allerdings tauchen auch hier Fehler auf:

http://666kb.com/i/btt585zmlouojsxgu.gif

0
@PhilEs

Autsch! Da habe ich nicht weit genug gedacht.
Da müssen absolute Bezüge rein, damit es beim Runterkopieren richtig bleibt. Also meine

Korrektur2:

{=TEIL(A1;MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE($1:$999);1)1)1)ZEILE($1:$999);0;999))1);MAX((((ISTZAHL(TEIL(A1;ZEILE($1:$999);1)1)1)ZEILE($1:$999))))-MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE($1:$999);1)1)1)ZEILE($1:$999);0;999))*1)+1)}

Sorry, war ein blöder Flüchtigkeitsfehler, ich bin wohl doch schon zu sehr aus der Übung.
SO müsste es jetzt aber hinhauen.

0
@Oubyi

Da war ich wohl etwas voreilig mit Vergabe der hilfreichsten Antwort.

So funktioniert es super.

Hab heute aber noch einen Lieferanten entdeckt, der bei ner kleinen Anzahl an Artikeln Dateinamen wie "12345_AB-06.jpg" hat.Da übernimmt die Formel die alten Dateinamen einfach so wie sie sind. Ist aber nicht so wild, da es nur ein paar ganz wenige sind die ich in 10min per Hand ändern kann.

Super Hilfe, danke!

0

Excel VBA - Werte mehrerer Tabellen in eine Tabelle untereinander kopieren?

Ich habe mehrere Tabellen die gleich aufgebaut sind. Am Ende dieser Tabellen stehen 3-4 Zellen welche Werte und/oder Text enthalten. Diese letzten Zellen möchte ich in einer gesonderten Tabelle untereinander auflisten.

Leider bin ich selbst ein totaler VBA Neuling und brauche daher eure Hilfe.

Danke im Voraus

...zur Frage

Textzellen in Excel nur den Text in Anführungszeichen anzeigen lassen?

Ich habe verschiedene Zellen untereinander mit teilweise Wörter oder sätze eingeschlossen in Anführungszeichen. Das ist in Spalte A:A. In Spalte B:B soll nur der Text in "" ausgegeben werden.

...zur Frage

Zahl und Text in einer Zelle, ich kann damit aber nicht rechnen weil excel die Zellen als Wort sieht

Ich habe in den Zellen 11 Fahrten , 10 Fahrten, 7 Fahrten usw. was muss ich machen damit excel die zellen auch als zahl sieht damit ich mit den zellen rechnen kann und die summe aller fahrten weiß.

wenn ich jetzt alles einfach so summiere dann kommt " #WERT! " raus.

danke

...zur Frage

Zellen verknüpfen im Microsoft Excel

Hallo, wie verknüpft man im Excel mehrere Zellen so miteinander, dass falls in einer irgendwelche Änderungen auftreten (z.B der Text wird geändert), das gleich auch in der Verknüpften Zelle geschieht?

Gibt es überhaupt so eine Funktion?

Ich bedanke mich :)

...zur Frage

Excel: Wie Zellen ohne Eintrag in Spalte löschen?

Ich habe eine Spalte, in der nur hin und wieder eine Zahl steht. Nun will ich daraus eine komprimierte Spalte erzeugen, in der alle Zellen ohne Eintrag gelöscht sind. Wie mache ich das am einfachsten? Macht es einen Unterschied, ob die Spalte in regelmäßigen Abständen oder unregelmäßigen Abständen Lücken hat?

...zur Frage

Folgende Mail rhetorisch und business mäßig an Kollegen formulieren?

Hallo, wie würdet ihr folgende Mail rhetorisch und business mäßig formulieren damit es besser klingt, die an Kollegen gehen, für die ich eine Lösung habe für ein Excel Problem. Sie wollten wissen, wie man die aktuelle und vorherige Kalenderwoche in Excel per Formel anzeigen lassen kann ohne es händisch einzutragen.

Könnt ihr meinen Text rhetorisch und fließender formulieren?

"Hallo zusammen,

um die aktuelle und vorherige Kalenderwoche mit dem Zusatztext “KW“ in Excel anzeigen zu lassen, bitte folgende Formeln einfügen.

 Aktuelle KW: (Excel Formel 1)

Vorherige KW: (Excel Formel 2)

Beide Zellen benutzerdefiniert formatiert als: KW 00

Viele Grüße, XY"

...zur Frage

Was möchtest Du wissen?