Excel: Formeln automatisieren

[time-machine]

hallo,

ich suche nach einer möglichkeit um in excel automatische abfolgen von formeln umzusetzen.
wir haben bei uns im Büro große mengen an kundennummern die nicht immer identisch sind und damit unser system diese zuordnen kann müssen diese angepasst werden. nun ist es so dass wir 4-6 verschiedene kundendaten haben und diese in die richtige form gebracht werden müssen.
zb: kundennummer 40726025794303 = (=LINKS(WECHSELN(WECHSELN(WECHSELN(TEIL(B4;4;20);" ";"");"/";"");"-";"");11) und dafür brauche ich natürlich verschiedene formeln.
vielleicht kann mir jemand helfen da ich in excel auch nicht so der experte bin, ob es dort nicht eine möglichkeit gibt sich das ganze zu erleichtern. ich muss sonst für die ersten 20 kunden jedesmal eine formel eingeben, für die nächsten 20 wieder ne andere usw. ich hoffe ihr versteht was ich meine?
mein ziel ist daher, eine art automatisation zu basteln per script? oder nen makro der mir das eingeben der formeln erleichtert bzw. die abfolge automatisch mit einem befehl erledigt
danke schon mal

 

[MickH74]

Hallo,

das geht mit einem Makro in VBA. Zunächst untersucht man, um welchen Typ Kundennummer es sich handelt und wandelt danach entsprechend um. Das kann man hier aber nicht in 1-2 Sätzen erklären. Darum kann ich Dir hier nur sagen, dass es grundsätzlich geht.

Gruß,
Mick

 

[Alchemist]

Aus deinem Beispiel kann ich leider nicht viele Informationen ziehen.

Aber ein paar allgemeine Anregungen:
Mit "Suchen und Ersetzen" kannst du relativ gut Zeichen ersetzen. Vielleicht bietet es sich an erst alle Kundennummern in nur Zahlen umzuwandeln?!

Per Makro gibt es die Möglichkeit nach Mustern zu suchen. Der passende Befehl heißt LIKE. Damit kannst du Muster definieren und dann guckt das Makro ob dein String zum Muster passt.

 

[Tiimati]

Kannst du vielleicht ein wenig spezifizieren welches Format der Ursprung hat und welches das Zielformat sein soll?

 

[time-machine]

Kannst du vielleicht ein wenig spezifizieren welches Format der Ursprung hat und welches das Zielformat sein soll?

Das Format im Ursprung ist eine zahlenfolge jedoch muss zb eine ziffer verschoben werden oder die kundennummer fängt erst an 4 stelle an oder die kundennummer fängt mit buchstaben an die rausgelöscht werden müssen oder aber auch sonderzeichen wie schrägstrich und bindestrich.Das zielformat ist nicht immer klar definiert, mal müssen die sonderzeichen weg, und mal haben wir das so dass die kundennummer erst an 4 stelle beginnt und die anfangsziffern weg müssen

ZB hier habe ich diese alte kundennummer als zahlenfolge: 407 10 072991524
bei dieser nummer habe ich das problem das die leerzeichen weg müssen und die nummer erst bei 10072991524 anfängt und die leerzeichen natürlich weg sollen. das ist alles gar kein problem wenn ich davon nur 2-3 kundennummern habe, dann mache ich es per formel. Ich habe allerdings teilweise sätze von 80-200 kundennummern die geändert werden müssen.

 

[Tiimati]

Wenn du "nur"4-6 verschiedene formate gemischt hast, dann könntest du ja nen Filter anwenden und dann nach zeichenfolgen und so weiter szchen und jeweils die passende formel formuliern und über die gleichenden drüberbügeln. Sonst musst du wohl wirklich mit scripten und mustern arbeiten. Wenn du dabei hilfe brauchst, musst du auch mehr muster vorgeben.

 

[Alchemist]

Das Ding ist, eine klare Formulierung des Problems ist der erste Schritt zur Lösung.

Können alle Leer- und Sonderzeichen weg? Dann markierst du einfach alle Nummern und gehst über Suchen und Ersetzen. Damit kannst du auch Leerzeichen löschen.

Sind es immer die 10 letzten Ziffern? Dann einfach mit der Rechts Funktion die letzten 10 Ziffern nehmen. Das kannst du auch über alle Nummern machen.

Ein "Problem" gibt es erst dann, wenn du zB Zahlen aus der Mitte brauchst oder den Anfang finden musst...

 

[time-machine]

Wenn du dabei hilfe brauchst, musst du auch mehr muster vorgeben.

ich habe hier ein paar kundennummern die geändert werden müssen.
Die alten kundennummern sehen zb. so aus:

alte Kundennummer
70 415276605
430-78-11155863
430/29/388616288
HAP12-90012105215
HIG14-90011662346
NSP03-90016043497
EL021-90013544189

neue kundennummer
70415276605
4307811155863
43029388616288
1290012105215
90011662346
0390016043497
2190013544189

so in etwa soll das ergebnis aussehen, wie man sieht sind die ausgangsdaten nicht immer identisch zum endprodukt bzw muss einiges geändert werden und daher ist es jede menge arbeit wenn man sie händisch abgleichen muss,da unsere kunden uns verschiedene kundendaten liefern die erst in die richtige form gebracht werden müssen.
zum teil bin lange damit beschäftigt die datensätze anzupassen und in die exceltabelle einzupflegen.

 

[Harry2k]

Hast du die Nummern direkt vermischt in einer Datei oder hast du eindeutige "Schlüssel" für das jeweilige benötigte Format ( also z.B. einen bestimmten Lieferanten/Kunden zu einer Nummer?)
weil dann könntest du da ansetzen.
Desweiteren macht es die Sache immer leichter wenn du eine Beispieldatei hochlädst, die Kundennamen kannst du ja durch fiktive Begriffe ersetzen.

 

[time-machine]

wir bekommen eindeutige schlüssel zu unseren kunden also bei uns in den tabellen sind diese eindeutig zu den kunden zuzuordnen. die schwierigkeit meiner ansicht nach bzw was die ganze arbeit ausmacht, ist die tatsache das wenn wir jetzt 20 anfragen von 20 verschiedenen kunden bekommen, haben wir auch 20 verschieden strukturierte kundennummern.
dann geht es los diese zu sortieren, mal haben welche mit bindestrich von einem kunden, der andere kunde liefert mit slash und wiederum der andere kunde packt irgendwo buchstaben dazwischen.
am ende ist für unser system nur wichtig das die ganzen sonderzeichen wie bindestriche, unterstriche und buchstaben weg sind und nur noch eine zahlenfolge übrig bleibt.
ich lade mal ne excel datei hoch, zur veranschaulichung.




Anhang anzeigen Bestandsdaten kopie.xls

 

[cumulonimbus8]

Dummstell:
Wenn ich eine Anzahl Quellen habe - warum gehe ich nicht ja nach Quelle mit einem geeigneten Konverter vor bevor ich alle Kundennummern integriere?
Oder komm auch aus den einzelnen Quellen ein Durcheinander?


NSP03-90016043497 → 0390016043497
EL021-90013544189 → 2190013544189
Das sind zwei Nullen - wo ist die Regel, dass die obere überlebt und die untere verschwindet.

CN8

 

[time-machine]

Das sind zwei Nullen - wo ist die Regel, dass die obere überlebt und die untere verschwindet.

CN8

Mein Fehler, hatte es falsch ins forum getippt, die nummern mit Anfangsbuchstaben fangen nach den Buchstaben an. Daher waren wohl einige poster verwirrt,
Nochmal es sollen alle zahlen übernommen werden.

 

[Janush]

Bau dir ne eigene Formel via VBA Editor. Die Funktion nimmt eine Zeichenkette und kegelt alle Zeichen raus, welche du nicht möchtest...easy

Edit:

Habs gerade mal ausprobiert...geht tatsächlich und hat 10 Minuten gedauert zu basteln. Immer diese Schwarzseherei hier im Office Forum ;-)

Hier ist der Code:

Public Function KundenNmr(ByVal KundenNummer As String) As String
Dim temp As String
Dim s As String
Dim x As Integer

For x = 1 To Len(KundenNummer)
    s = Mid(KundenNummer, x, 1)
    If IsNumeric(s) Then temp = temp & s
Next x

KundenNmr = temp
End Function

Ich muss dazusagen, das ist eine Quick & Dirty Lösung und filtert alle Zeichen raus, welche keine Zahlen sind und gibt einen String zurück. Du kannst dies aber einfach in ein Zahlenformat deiner Wahl ändern, ich würde ein LONG empfehlen. Allerdings gehen dir dann führende Nullen verloren.

Verwenden kannst du den Code ganz einfach indem du den Visual Editor aufmachst, ein Modul einfügst und diese Funktion dort hin kopierst. Dann einfach in deiner Exceltabelle =KundenNmr() benutzen. Wie schon gesagt, easy 😉

Edit 2:

OK - Long funktioniert aus irgendeinem Grund nicht, warum auch immer. Ich habe nochmal nen Zahenableger mit Double als Format gebaut. Damit bekommst du eine Zahl zurueckgeliefert. Wie gesagt, Excel loescht vorangestellte Nullen.

Public Function KundenNmr(ByVal KundenNummer As String) As Double
Dim temp As String
Dim s As String
Dim x As Integer

For x = 1 To Len(KundenNummer)
    s = Mid(KundenNummer, x, 1)
    If IsNumeric(s) Then temp = temp & s
Next x

KundenNmr = CDbl(temp)
End Function

Und hier ist noch das Excelfile: Anhang anzeigen KundenNummernTest.zip


Happy coding

Edit 3:

Bevor ich es ganz vergesse; Wenn ihr das automatisieren wollt.....sagen wir alle Kundennummern in Spalte A und dann soll ein Knopf gedrueck werden, um alle auf einmal umzuwandeln....dann baut ihr um meinen Code einfach eine Schleife drumrum und voila, keine Formeln mehr benoetigt...easy.

 

[time-machine]

Vielen lieben dank @Janush, ich muss jetzt nur noch wissen wie ich das script ausführe? Über vba und dann die xlms öffnen geht es nicht.
Wäre es bei dein script auch möglich die kd nummer zu kürzen?
Ich habe zb ne 16 stellige kd möchte die beiden letzten stellen aber nicht geht das? Dann würde ich sie selbst anpassen wenn du mir erklären kannst wo ich ansetzen muss. Kann ich damit auch zeichen löschen zb kd 567890320- alt und oder so formulieren das er mir die kd nummer bei 7890... anfängt zu schreiben?
Wäre super, damit wäre mir sehr geholfen.

 

[Janush]

Du führst es ganz einfach dadurch aus, dass du die Formel in deiner Tabelle benutzt. Hast du meine Datei heruntergeladen? Was wird da angezeigt? Spalte B und C haben die Formel drin und wandeln Spalte A um.

Anpassen kannst Du alles wenn du kreativ bist :-)

Hab leider grade keine Zeit, schau aber heute Abend nochmal rein (Abend meiner Zeit - EDT).

Grüße :-)

 

[time-machine]

hi, die formel klappt bisher ganz gut, ich muss die formel aber in jeder tabelle einfügen oder?wenn ich jetzt mehrere spalten habe die ich bearbeiten muss, füge ich deine formel dann ein und bearbeite nacheinander formel mit spalte A,B,C usw. richtig?
dies erleichtert schon so einiges, je nach kundenauftrag müsste ich das script umformen also mit zb 14 statt 16 stelliger ziffernfolge.
wenn du mir das noch erklären kannst, bin ich dir mehr als dankbar.
wie war das mit der schleife gemeint? hab das nicht ganz verstanden
danke bisher

Edit/ da einige kunden es gerne so hätten das die kdnr erst ab der 4 ziffer beginnt zb statt 34678102345 78102345

 

[Janush]

War heute den ganzen Tag unterwegs. Schaue morgen mal rein