Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 12:38:40
Formeländerung durch Wechsel von Excel 2021 auf 2019
24.04.2024 12:31:18
Excel - Sverweis mit Wenn-Abfrage kombinieren
24.04.2024 12:16:26
Name von aktuellem VBA-Modul ausgeben
24.04.2024 12:07:15
Werte einer Liste vergleichen und markieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1324to1328
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1324to1328
1324to1328
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

TextZahl aus Zellinhalt trennen

TextZahl aus Zellinhalt trennen
24.07.2013 23:33:35
Werner
Hallo und guten Abend Excel-Freunde
ich erhalte aus einer Datenbank CSV Daten, die wie folgt
(beispielsweise) aufgebaut sind
qwe12asdf123yx12
qwer123as1yxcv1234
q1234as12345123yx1ghjk78
ghj45
hjk4567sd345
Also immer eine Reihenfolge von Buchstaben und Zahlen - in unterschiedlicher Länge.
(das Beispiel ist exemplarisch, da die Originaldaten personenbezogene Daten enthalten.)
Ich möchte jetzt folgendes erreichen:
Immer die letzte Buchstaben/Zahlen-Kombination in die Zelle daneben schreiben
... möglichst per Formel ...
Dann sehe das Ergebnis wie folgt aus
yx12
yxcv1234
ghjk78
ghj45
sd345
Auf Excelformeln.de habe ich schon geschaut ;)
(dort kann man ALLE Zahlen vom Text trennen
- oder ALLEN Text von Zahlen - sehr gut !
Nur benötige ich etwas anderes :)
oder habe ich dort nicht richtig gesucht ? )
Freu mich auf einen Tip :)
Gruss
Werner

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: TextZahl aus Zellinhalt trennet
25.07.2013 00:09:31
Werner
Hi Gerd
mmmh .. ich mache bestimmt was falsch
mit der Formel
=VERWEIS(9^9;1*RECHTS(A1;SPALTE(1:1)))
wird aus
q1234as12345123yx1ghjk78
nur ein
78
und nicht ein
ghjk78
und mit der Formel
=TEIL(A1;LÄNGE(VERWEIS(9^9;LINKS(A1;SPALTE(1:1))*1))+1;99)
kommt
#NV
da ja nur "hier darf allerdings nur ein Ziffernblock vorkommen"

Noch nen Tip ?
Gruss
Werner

Anzeige
letzte Zeichen-Zahlenkobination in Zelle
25.07.2013 01:17:45
Matthias
Hallo Werner
Ich hab mal eine kleine VBA-Variante geschrieben.
Einfach den String aus der Liste in A1 auswählen
und Button drücken ;-)
https://www.herber.de/bbs/user/86544.xls
Gruß Matthias

Das war doch gar nicht nötig, ...
25.07.2013 02:10:27
Luc:-?
…Matti… ;-)
…und nein, Werner,
du machst nichts direkt falsch, aber auch nicht das, was du wirklich benötigst! ;->
Ausgangslage: Du hast …
1. Daten, die auf eine Kombi von KB-Folge und Zahl enden; genau diese EndKombi soll isoliert wdn; und …
2. Fmln, die a) die letzte Zahl nach BstFolge und b) umgekehrt die letzte BstFolge nach Zahl isolieren.
Also muss deine Formel beide Fmln enthalten → erst mit a) die Zahl isolieren, deren Länge festellen, von der Länge der GesamtKombi abziehen, mit dieser DiffzLänge den Text kürzen und darauf b) anwenden und schließlich a) dem Ergebnis von b) hinzufügen.
Diese MxFml von 227 Zeichen Länge sähe so aus:
{=TEIL(LINKS(A1;LÄNGE(A1)-LÄNGE(VERWEIS(9^9;--RECHTS(A1;SPALTE(1:1)))));MAX(ISTZAHL(--TEIL(LINKS(A1; LÄNGE(A1)-LÄNGE(VERWEIS(9^9;--RECHTS(A1;SPALTE(1:1)))));SPALTE(1:1);1))*SPALTE(1:1))+1;99) &VERWEIS(9^9;--RECHTS(A1;SPALTE(1:1))) }
Falls du es lieber kürzer magst, kannst du auch die UDFs MaskOn und Splint aus dem Herber-Archiv einsetzen. Das ergäbe folgende 186 Zeichen lange Fml:
=WENN(ISTZAHL(SUCHEN(" ";MaskOn(A1;"alf")));Splint(MaskOn(A1;"alf");;1;1;1);MaskOn(A1;"alf"))&
WENN(ISTZAHL(SUCHEN(" ";MaskOn(A1;"num")));Splint(MaskOn(A1;"num");;1;1;1);MaskOn(A1;"num"))
Mit zusätzlichem Einsatz der UDF Keep (ebda) wird die Fml sogar nur 158 Zeichen lang:
=WENN(ISTZAHL(SUCHEN(" ";Keep(MaskOn(A1;"alf"))));Splint(Keep();;1;1;1);Keep()) &WENN(ISTZAHL(SUCHEN(" ";Keep(MaskOn(A1;"num"))));Splint(Keep();;1;1;1);Keep())
Außerdem eignen sich die 1.beiden Fmln auch zum Einsatz benannter FmlTeile, was die ZellFmln ebenfalls übersichtlich verkürzt.
Du kannst natürlich auch den bequemen Weg nehmen und Mattis Makro für dich arbeiten lassen… ;-)
Morrn, Luc :-?

Anzeige
Ach wie dumm, jetzt habe ich die 2.Fml ...
25.07.2013 02:25:56
Luc:-?
…unnötig verlängert, denn die fktioniert auch mit 72 Zeichen Länge, nämlich so:
=Splint(" "&MaskOn(A1;"alf");;1;1;1)&Splint(" "&MaskOn(A1;"num");;1;1;1)
Luc :-?

Ich hab eben ne VBA Schwäche ;-) oT
25.07.2013 02:37:10
Matthias

letzter Buchstaben-Zahlen-Block
25.07.2013 08:46:00
WF
Hi,
folgende Arrayformel:
{=TEIL(A1;MAX(ISTZAHL(TEIL(A1;ZEILE(INDIREKT("1:"&LÄNGE(A1)-1));1)*1)*NICHT(ISTZAHL(TEIL(A1; ZEILE(INDIREKT("2:"&LÄNGE(A1)));1)*1))*ZEILE(INDIREKT("1:"&LÄNGE(A1)-1)))+1;99) }
Salut WF

dazu eine Formelvariante ohne {} ...
25.07.2013 09:23:04
neopa
Hallo Werner,
... so z.B.: =TEIL(A1;VERWEIS(9;1/(CODE(TEIL(9&A1;SPALTE(1:1);1))64);SPALTE(1:1));99)
Gruß Werner
.. , - ...
oT
Jeder Interessent ist willkommen beim Exceltreffen 11.-13.10.2013 in Duisburg
Mehr dazu, siehe http://www.exceltreffen.de/index.php?page=230

Anzeige
geht bei 15 Sonderzeichen in die Hose
25.07.2013 10:00:37
WF
Salut WF

über ein eine allgemeinere Lösungsformel, ...
25.07.2013 10:18:17
neopa
Hallo Walter,
... wenn eine solche denn notwendig werden sollte, müsste ich nochmal nachdenken. Momentan fehlt mir dazu etwas die Zeit. Allerdings lösbar ist das sicherlich auch.
Gruß Werner
.. , - ...
oT
Jeder Interessent ist willkommen beim Exceltreffen 11.-13.10.2013 in Duisburg
Mehr dazu, siehe http://www.exceltreffen.de/index.php?page=230

AW: und noch mal gekürzt...
25.07.2013 11:27:36
Frank
Hallo,
hier noch einmal eine "Abkürzung" (allerdings als Matrixformel):
=TEIL(A2;VERGLEICH(2;(CODE(TEIL(A2;SPALTE(A1:Z1);1))64);1);99)
Mit freundlichem Gruß aus der Rattenfängerstadt Hameln
Frank Arendt-Theilen
---
at-exceltraining.de

Anzeige
Blödsinn
25.07.2013 11:35:58
WF
vom Textblock wird nur der letzte Buchstabe übernommen.
WF

AW: dein Freund...
25.07.2013 14:36:37
Frank
Hallo,
dein Freund, der Fehlerteufel, hat nicht auf mich hören wollen. Echt, kein Blödsinn!. Ich habe ihm gesagt: Nimm' diese Matrixformel:
=TEIL(A1;VERGLEICH(2;1/(CODE(TEIL(9&A1;SPALTE(A1:Z1);1))64);1); 99)
aber neee...
Mit freundlichem Gruß aus der Rattenfängerstadt Hameln
Frank Arendt-Theilen
---
at-exceltraining.de

wie oben schon gesagt, ...
25.07.2013 14:47:45
WF
... geht diese Formel bei 15 Sonderzeichen in die Hose.
123wert456asus-r789
ergibt r789
und nicht (wie gewollt) asus-r789
WF

Anzeige
wie bereits begründet ...
25.07.2013 18:12:50
neopa
Hallo Frank,
... Dein zweiter Formelansatz, der eine Variation meines ersten Formelansatzes darstellt, ergibt im Prinzip das gleiche Formelergebnis, wie meine erste Formel. Das kannst Du auch mit meiner Beispieltabelle schnell überprüfen.
Insofern hält offensichtlich bisher nur meine zweite Formel den Kriterien von WF stand.
Gruß Werner
.. , - ...
oT
Jeder Interessent ist willkommen beim Exceltreffen 11.-13.10.2013 in Duisburg
Mehr dazu, siehe http://www.exceltreffen.de/index.php?page=230

Anzeige
meine Mittagspause könnte geholfen haben ...
25.07.2013 12:58:53
neopa
Hallo @all,
... mein erster kurzer Formelvorschlag berücksichtigt einige (wahrscheinlich unwahrscheinliche) Sonderzeichen nicht.
Ich hätte es mir nun einfach machen können und die Formel von WF etwas entschlacken. Dazu siehe C2 & D2. Aber der Ausbau meines ersten Ansatzes schien mir geeigneter.
Mein nachfolgender Formelvorschlag ist aus Zeitgründen jedoch nicht auf alle Eventualitäten getestet:
 ABCDE
1Texteneopa WF neopa
2qwe152asdf123yx11yx11yx11yx11yx11
3qwe12asdf123>yx123asdf123>yx123>yx123>yx123>yx123
4qwer123as1@yxcv123456789112as1@yxcv123456789112@yxcv123456789112@yxcv123456789112@yxcv123456789112
5q1234as12345123yx1ghjkghjkghjkghjkghjk
6:ghj45#NV:ghj45:ghj45:ghj45
7hjk4567sd345sd345sd345sd345sd345
8a1230a1230a1230a1230a1230
9-999#NV-999-999-999
10 #NV#BEZUG!#WERT! 
110123a3210a3210a3210a3210a3210
12     

Formeln der Tabelle
ZelleFormel
B2=TEIL(A2;VERWEIS(9;1/(CODE(TEIL(9&A2;SPALTE(2:2); 1))<58)/(CODE(TEIL(A2;SPALTE(2:2); 1))>64); SPALTE(2:2)); 99)
C2{=TEIL(A2;MAX(ISTZAHL(TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)-1)); 1)*1)*NICHT(ISTZAHL(TEIL(A2; ZEILE(INDIREKT("2:"&LÄNGE(A2))); 1)*1))*ZEILE(INDIREKT("1:"&LÄNGE(A2)-1)))+1;99)}
D2{=TEIL(A2;MAX(ISTZAHL(-TEIL(A2;ZEILE(A$2:INDEX(A:A;LÄNGE(A2)-1)); 1))*ISTFEHL(-TEIL(A2; ZEILE(A$3:INDEX(A:A;LÄNGE(A2))); 1))*ZEILE(A$2:INDEX(A:A;LÄNGE(A2)-1)))+1;99)}
E2=TEIL(A2;VERWEIS(;-TEIL(9&LINKS(A2;LÄNGE(A2)-(LÄNGE(VERWEIS(;-RECHTS(A2&9;SPALTE(2:2))))-2)); SPALTE(2:2); 1); SPALTE(2:2)); 99)
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
oT
Jeder Interessent ist willkommen beim Exceltreffen 11.-13.10.2013 in Duisburg
Mehr dazu, siehe http://www.exceltreffen.de/index.php?page=230

Anzeige
So, Werner, jetzt hast du 9 fktionierende ...
25.07.2013 15:54:32
Luc:-?
…Fmln für die Lösung deines Problems (9, wenn man eine Variante meiner xlFmlnKombiFml unter Einsatz der UDF Keep bzw benannter FmlTeile hinzurechnet) und eine Knopfdruck-Komfort-Lösung, was wohl reichen dürfte.
Alles vor WFs AW sind pragmatische Ansätze aus Vorhandenem bzw ein „Sorglos“-Paket, ab WFs AW die Spielwiese der FmlFreaks. Da Durchzusteigen kann lohnend sein, wenn man die Zeit aufbringen will und kann. ;-)
Die FmlLängen*: xlFmlnKomb=227, xlFmlnKb+Keep=171, V1:Splint+MaskOn=186, dito+Keep=158, V2:Splint+MaskOn=72, WF=171, neopa1=107, neopa2=128, F.Arendt-Theilen=100
* von {} bereinigt
Gruß Luc :-?

Anzeige
jedem wie er es mag ...
25.07.2013 18:18:51
neopa
Hallo Luc,
... Du bist der UDF-Freak, wir eben die Formelfreaks. Beide Typen, werden von vielen nicht wirklich verstanden. Wir haben nur den kleinen Vorteil, dass Formeln immer und überall und sofort eingesetzt werden können und die XL-Mappen mit solchen auch an anderen PC´s so sofort genutzt werden können.
Ich muss gleich offline gehen, Dir einen schönen Abend noch,
Gruß Werner
.. , - ...
oT
Jeder Interessent ist willkommen beim Exceltreffen 11.-13.10.2013 in Duisburg
Mehr dazu, siehe http://www.exceltreffen.de/index.php?page=230

Anzeige
Na, dann dito! Aber leider gräbt euch MS ...
25.07.2013 19:58:13
Luc:-?
…immer mehr das Wasser ab, Werner;
inzwischen sind es schon sehr viele, wenn nicht gar die Mehrheit der Fktt, die sich weigern, die von diversen FktsVerschachtelungen erzeugten Datenfelder anzunehmen. Meinen UDFs geht das erst so, wenn MS VBA ganz streicht! ;-)
Nebenbei halte ich viele ellenlange Fmln für von Laien kaum durchschaubar, geschweige denn, dass sie von allein darauf kommen würden, und damit wenig praxisfreundlich. Mit etlichen universellen UDFs ist da doch eher (weniger entwicklungszeitaufwendig für den Anwender) was zu machen. Allerdings kann die Ergebnistabelle dann idR nicht mit Fmln an Andere weitergegeben wdn, aber das sollte man ohnehin nur ausnahmsweise tun.
Leuten mit Formel-/FktsHorror kann natürlich beides egal sein! ;->
Gruß Luc :-?

dem seh ich gelassen entgegen ...
30.07.2013 10:34:52
neopa
Hallo Luc,
... ich bin zwar "erst" 2005 in Excel eingestiegen, aber bereits da waren die Wesentlichsten Funktionen für Formeln teils schon 10 Jahre alt. Und sie "laufen" heute noch immer und werden sicher auch in 10 Jahren noch ihren Dienst tun.
Übrigens sind es meiner Meinung nach nicht unbedingt nur die ellenlangen Formeln die abschreckend wirken. Oft sind es auch die ultrakurzen, die gerade auch einige Fortgeschrittene manchmal zweifeln lassen. Wie auch immer, ob Standardformeln oder Hilfszellenformeln, Formelmonster oder "Kunst"Formeln, Formeln auf Basis von UDFs, entscheidend ist für mich, dass ich nicht nur helfen kann sondern auch Spaß dabei haben möchte.
Warum sonst schwimmen manche 25 km oder Laufen 100 km oder ... Fortbewegen könnten sie sich allemal mit weniger Kraftanstrengung und auch wesentlich schneller, ob zu Wasser oder an Land.
Gruß Werner
.. , - ...
oT
Jeder Interessent ist willkommen beim Exceltreffen 11.-13.10.2013 in Duisburg
Mehr dazu, siehe http://www.exceltreffen.de/index.php?page=230

Ja, da haste recht, ...
30.07.2013 15:11:07
Luc:-?
…Werner,
aber mir scheint, hinter der MS-NeuFktsGestaltung scheint ein System zu stecken. Es bestünde ja eigentl keine Notwendigkeit, bspw RANG auf Zellbereiche zu beschränken. Meine UDF RankIn akzeptiert auch Datenfelder. Es wäre also leicht möglich gewesen, das auch RANG zu gestatten.
Übrigens hätten WF & Co ihre 3 Bücher in fast gleichem Umfang auch schon 10 Jahre früher veröffentlichen können, ohne dass es FmlProbleme gegeben hätte. MS zehrt bis heute von diesem Grundgerüst, ohne es auf neuere Kreationen anzuwenden (…?).
Falls das mit den superkurzen Fmln auch eine Anspielung auf gewisse FmlVerkürzungsFreaks sein sollte (auch, wenn 92 Zeichen nicht wirklich kurz ist!), habe ich dazu ja meine Meinung schon mal kund getan (einer der Fälle, wo ich mit WF u.PH übereinstimme). Das ist so ein schmales Hobby-Feld, dass man sich nur wundert, dass das Leute befriedigen kann (extreme Sprach- und wohl auch Software-Abhängigkeit). Und da heißt es doch immer, die Sprache der Mathematik ist international! Was hier betrieben wird, ist das GgTeil! ;->
Gruß Luc :-?

DANKE an ALLE :))) ... bin
25.07.2013 16:55:41
Werner
schwer beeindruckt :O
und versuche jetzt "step by step"
auch die Formeln zu vestehen !
Es funktioniert .. und das ist im ersten Schritt das Wichtigste.
Der 2. Schritt ist das Verstehen / Lernen für mich ;)
Also vielen Dank
Gruss
Werner

306 Forumthreads zu ähnlichen Themen

Zahlencode mit Punkte Formatieren
runny

Wer kann mir weiter helfen.
Ich habe einen 10-stelligen Zahlencode 0265006214 und diesen soll Excel autom. in das Format 0265.006.214 bringen.
Also nach der 4.Stelle und der 7.Stelle einen Punkt (SAP-Format). Zelle formatieren und ####.###.### habe ich schon ohne Erfolg geprüft.
Er...
28.08.2008 00:14:00
Thread-ID: 1005247
3 Beiträge
Anzeige
Zellinhalte Zahlen und Text extrahieren
Marky

Hallo Excel Spezialisten !
Ich würde gerne aus zwei nebeneinander stehenden Spalten die Zellinhalte extrahieren.
Die Zellinhalte bestehen aus Zahlen und Text. Die aus den Zellen zu extrahierenden Zahlen und Texte
stehen immer in den Spalten K10 und N10.
Die Zielzellen sind immer...
29.09.2008 12:55:00
Thread-ID: 1013076
6 Beiträge
Zelle mit 50 stelliger Zahl
Mathias

Hallo guten morgen,
kann mir bitte jemand sagen wie ich eine Zelle so formatieren kann das darin eine ca. Zahl mit 50 Stellen angezeigt wird.
Folgendes Problem,die Zahl sind so auf 350015+28€ d.h. sie wird als Text angezeigt, formatiere ich diese als Zahl bekomme ich zwar etliche Stell...
21.10.2008 08:33:45
Thread-ID: 1017842
3 Beiträge
Anzeige
Text_Zahlenwert suchen und Zahlen "ablegen"
Lemmi

Hallo zusammen,
ich möchte aus einer drei spaltigen Tabelle Zahlen auslesen, die dann Monatsweise abgelegt werden sollen!
Es soll immer nach einem " Schlüsselwort" (...kann auch eine x beliebige Zahlfolge in der Benennungsliste sein) die Auslesung der Zahlenwerte durchgeführt werden.
03.01.2009 22:11:00
Thread-ID: 1036655
4 Beiträge
Text und Zahlen teilen
Bernie

Hallo Leute,
ich suche ganz dringen nach einem Makro, mit dem ich aus einer Tabelle mit Straßennamen - diese sind oft chaotisch geschrieben- z.B. Bahnhofstraße45A
a) die Straße in eine Spalte
b) die Hausnummer (mit A usw.) in eine Spalte
ausgeben kann.
Hat jemand ein zünd...
05.03.2009 14:39:23
Thread-ID: 1056861
7 Beiträge
Text und Zahlen in einer Zelle
Sammy

Hallo liebes Forum
Ich bekomme eine Liste mit ca. 200 Namen mit Personalnummeren.
die Zelle um die es geht sieht so aus.
Zelle A1 143(Gerda Becker)
Ich benötige allerdings die Zahl 143 in einer Zelle und (Gerda Becker) in einer Zelle.
Falls das nicht möglich ist, würde e...
24.05.2009 10:20:18
Thread-ID: 1076915
7 Beiträge
Zahl und Datum als formatierten Text übergeben
k

Hallo,
sicherlich stelle ich mich nur wieder zu blöd an, aber ich komm nicht weiter.
Ich habe in einer Tabelle Werte (Umsatzzahlen) sowie Datums-Werte(Tag der Rechnung) und möchte diese gern aber als "TEXT" an ein Listview-element weiter gegeben.
Dabei soll aber optisch es noch wie...
21.08.2009 10:03:57
Thread-ID: 1097614
7 Beiträge
Zahlenwert von Text trennen
Text

Hallo zusammen,
habe mal wieder ein kleines Problem mit Zahlen und Einheiten!
Ich habe heute eine Zahl mit Einheit (verschiedenen Einheiten) in einer Zelle z. B. in A1 123,03 Nm oder A2 123,123456 kg
Wie kann ich die Einheit von dem Zahlenwert trennen?
Mein ergebnis soll Zelle...
15.09.2009 19:31:11
Thread-ID: 1103044
5 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
2
11
5
2
14
3
6
6
6
4
3
3
3
7
4
5
3
5
22
11
6
2
5
3
1