Gib zeichen bis erste leerzeile zurück

Stefan_fad303 · 10. November 2019 um 05:18

hallo,

da ich meinem chef gestern dummerweise bei einer aufgabe gesagt habe: es wäre kein problem (und mich aber leider in der funktionsweise der val funktion geirrt habe) brauche ich leider ganz dringend einen vba-ausdruck.

kennt jemand den ausdruck, der eine zeichenkette bis zum ersten leerzeichen durchsucht und den string zurückgibt.

also stellen wir uns vor, wir haben eine spalte mit namen:

Bruno Brunlich
Kuno Kuntz
…

dann soll die Funktion nur Bruno und Kuno zurückgeben
ähnlich dem:

Funktion(Spalte,suche bis erste Leerzeile)

Vielen Dank für nen Tip
Stefan

Klaus_58a5ce · 10. November 2019 um 05:19

Hallo Stefan,
versuch mal

funktion = left(spalte,instr(1,spalte," "))

Gruß
Klaus

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

Stefan_fad303 · 10. November 2019 um 05:19

hi, danke für die antwort.

habe gott sei dank schon was im netz gefunden, allerdings bereitet es mir etwas sorgenn (mit dem Left komme ich gut klar) aber das Mid macht mir Probleme, weil ich eigentlich Zeichen zwischen dem @ und dem . auslesen will…
aber ich werd erst mal selbst noch etwas rumprobieren…

besten dank trotzdem
stefn

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hi,

so etwa …

 txt = "[email protected]"
 neu = Mid(txt, InStr(txt, "@") + 1, InStr(txt, ".") - InStr(txt, "@") - 1)

Gruß, Rainer

Manfred_Tessin · 10. November 2019 um 05:19

Hallo, Stefan!

Achte bei dem Instr noch auf folgendes: Die Funktion liefert 0 zurück, falls kein Treffer gefunden wird. Das solltest Du noch abfangen, sonst gäbe Dein Aufruf bei „Kunibert“ keinen Text (bzw. bei Mid einen Fehler) zurück.

Gruß, Manfred

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hi Manfred,

Achte bei dem Instr noch auf folgendes: Die Funktion liefert 0
zurück, falls kein Treffer gefunden wird.

ja, Du hast Recht! Ich habe auch überlegt, ob ich das noch mit einbaue, aber es ging mir nur um die Syntax dieser einen Zeile. Die Deklaration der Variablen fehlt ja auch und eine Prozedur ist das auch nicht.
OK, dann vervollständige ich das mal …

Private Function DomainName(ByVal Mailadresse As String) As String
 If InStr(Mailadresse, "@") \> 0 And InStr(Mailadresse, ".") \> 0 Then
 DomainName = Mid(Mailadresse, InStr(Mailadresse, "@") \_
 + 1, InStr(Mailadresse, ".") \_
 - InStr(Mailadresse, "@") - 1)
 Else
 DomainName = ""
 End If
End Function

Besser so?

Gruß, Rainer

Manfred_Tessin · 10. November 2019 um 05:19

Hi, Rainer!

Besser so?

Musst Du Stefan fragen. Aus meiner Sicht: Nein, ich brauche eine Public Function

Okay, lassemer das, zurück zum Ernst des Lebens,
Gruß, Manfred

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hi Manfred,

Besser so?

Musst Du Stefan fragen.

dem ist das eventuell zu viel.

Aus meiner Sicht: Nein, ich brauche eine Public Function

*gg* Die reale Anwendung würdest Du vermutlich völlig anders machen, aber Stefan verrät zu wenig.

Okay, lassemer das, zurück zum Ernst des Lebens,

Das ist alles schon ernst genug und hier sind wir ja unter uns.

Gruß, Rainer

Stefan_fad303 · 10. November 2019 um 05:19

hi,

danke euch beiden für die tiefgehende diskussion und den eigentlich schon fertig programmierten code.

ich war abwesend, weil ich dann wegen dem nicht vorhandenen @ (wie du schon selbst angesprochen hast) probleme bekommen habe und dann doch lieber in dem company-namensfeld gearbeitet habe (da habe ich das dann mit if not isnull… gelöst) … am ende musste ich aber feststellen, dass des ned so doll klappt, wenn man zwei unternehmensspalten abgleicht und aus der einen nur den ersten string nimmt, da sucht sql dann teilweise in der anderen datenbank nach allen unternehmen, die irgendwo ein a im namen haben … gibt dann ne tolle kombination und der rechner ist erst mal ausgelastet.

ich werd das jetzt also doch noch mal mit dem server (also der emailaddy machen) und danke euch zweien auf jedenfall für den wahsch. schon fertigen code (habe ihn jetzt noch ned ausprobiert) und die inhaltliche erläuterung.

Grüsse
und Besten Dank
Stefan

Reinhard_fb8cb7 · 10. November 2019 um 05:19

Hola os dos,

Besser so?

Musst Du Stefan fragen.

dem ist das eventuell zu viel.

Glaub nicht, aber das hier wird ihm zuviel sein

Option Explicit
'
Sub test()
MsgBox Auslesen("[email protected]")
End Sub
'
Function Auslesen(Wort)
 Auslesen = Application.WorksheetFunction.Substitute(Application.WorksheetFunction \_
 .Substitute(Wort, Left(Wort, InStr(Wort, "@")), ""), Mid(Application.WorksheetFunction \_
 .Substitute(Wort, Left(Wort, InStr(Wort, "@")), ""), InStr(Application.WorksheetFunction \_
 .Substitute(Wort, Left(Wort, InStr(Wort, "@")), ""), ".")), "")
End Function

Das ist alles schon ernst genug und hier sind wir ja unter
uns.

Aber nur fast unter euch, mal vom Mossad, BND, CIA, KGB, … ganz abgesehen *gg*

Gruß
Reinhard

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hi Stefan,

den String zerlegen kann SQL nicht, aber wenn es darum geht, Datensätze aus einer Tabelle zu filtern sollte SQL immer schneller sein, als die Tabelle mit VB in einer Schleife zu bearbeiten.

Gruß, Rainer

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hallo Reinhard,

Option Explicit
’
Sub test()
MsgBox Auslesen(„[email protected]“)
End Sub
’
Function Auslesen(Wort)
Auslesen =
Application.WorksheetFunction.Substitute(Application.WorksheetFunction
_
.Substitute(Wort, Left(Wort, InStr(Wort, „@“)), „“),
Mid(Application.WorksheetFunction _
.Substitute(Wort, Left(Wort, InStr(Wort, „@“)), „“),
InStr(Application.WorksheetFunction _
.Substitute(Wort, Left(Wort, InStr(Wort, „@“)), „“),
„.“)), „“)
End Function

*gg* das sieht ja putzig aus. VBA hat eben doch noch ein paar Funktionen, die VB nicht kennt.

Das ist alles schon ernst genug und hier sind wir ja unter
uns.

Aber nur fast unter euch, mal vom Mossad, BND, CIA, KGB, …
ganz abgesehen *gg*

Immer noch ‚unter uns‘ VB/VBA-Programmierern. Ich habe ja Anwesende nicht ausgeschlossen. Die anderen, die Du da erwähnst, verstehen ja doch nicht, was wir hier schreiben.

Gruß, Rainer

Stefan_fad303 · 10. November 2019 um 05:19

jup, zerlegen tue ich auch in vba und das Suchen dann in SQL

Stefan_fad303 · 10. November 2019 um 05:19

hi, habe den code mal ausprobiert - mit dem minus hat das ned geklappt aber der rest schon

will also erst mal den teil rechts von @ in eine eigene spalte kopieren, und die dann noch mal zerlegen, weil man ja oft auch mehere .te hat zb. [email protected] …

so schaut der teil aus

DomainName = Left(DomainName, InStr(DomainName_pre, „.“) + 1)

das komische ist:

meist funktioniert er perfekt aber bei z.B.
[email protected]

gibt er nur altanus zurück (ich habe die domain etwas verändert);

??? kapiert jemand, warum er plötzlich abbricht? (bei den meisten cuttet er richtig

Stefan_fad303 · 10. November 2019 um 05:19

hoppla, ich glaube ich hab jetzt erst kapiert, was instr eigentlich tut: es gibt schlicht und einfach eine zahl zurück…
muss meinen geposteten code erst noch mal überdenken

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hallo,

hi, habe den code mal ausprobiert - mit dem minus hat das ned
geklappt aber der rest schon

komisch, das war getestet, hat so funktioniert.

will also erst mal den teil rechts von @ in eine eigene spalte
kopieren, und die dann noch mal zerlegen, weil man ja oft auch
mehere .te hat zb. [email protected] …

OK, geht auch. Welche Teile Du benötigst, musst Du wissen. Ein Hinweis noch: Wenn Du den letzten Punkt suchen willst, geht das mit InstrRev(Zeichenfolge1, Zeichenfolge2[, starten[, vergleichen]]) an bequemsten.

so schaut der teil aus

DomainName = Left(DomainName, InStr(DomainName_pre, „.“) + 1)

Ist das ein Tippfehler? Im Left() hätte ich zwei mal die selbe Variable erwartet.

das komische ist:

meist funktioniert er perfekt aber bei z.B.
[email protected]

gibt er nur altanus zurück (ich habe die domain etwas
verändert);

Da hast Du uns vermutlich ein Zeichen wegretuschiert, das den Fehler auslöst.

??? kapiert jemand, warum er plötzlich abbricht? (bei den
meisten cuttet er richtig

Dazu reichen die Informationen nicht. Das kannst Du aber mit dem Debugger selbst gut prüfen. Kennst Du den Debugger?

Gruß, Rainer

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hallo,

hoppla, ich glaube ich hab jetzt erst kapiert, was instr
eigentlich tut: es gibt schlicht und einfach eine zahl
zurück…

ja, Instr() gibt als Zahl zurück, an welcher Stelle der gesuchte String im Quellstring beginnt.

In ‚Testtext‘ beginnt die Zeichenfolge ‚tex‘ ab dem Zeichen 5.
Instr(„Testtext“,„tex“) gibt also 5 zurück.

Gruß, Rainer

Reinhard_fb8cb7 · 10. November 2019 um 05:19

Hallo Rainer,

*gg* das sieht ja putzig aus. VBA hat eben doch noch ein
paar Funktionen, die VB nicht kennt.

Stimmt, leider hat jedes VBa (Excel,Word,Access,CorelDraw,Outlook,…) wiederum eigene Funktionen.

Wie du eins untendrunter zu SQL gesagt hast, eingebaute Excelfunktionen sind auch zigmal schneller als alle Vb oder Vba-Versuche. Manche sprechen von 1000mal schneller.
Mangels XL2007 kann ich es nicht testen, aber wenn ich die Gesamtsumme aller Zellen in 1 Million Zeilen und 16xxx Spalten haben will, so gebe ich einfach ein:
=Summe(„A1:ZZZ1000000“)
und kann mutmaßlich das Ergebnis gleich ablesen.
Alle Zellen per Vba abklappern kann schon sehr viel länger dauern.

Und mit
Application.Worksheetfunction
hat man Zugriff auf alle normalen Excelfunktionen wie Summe, usw.
Substitute entspricht der Excelfunktion Wechseln() (oder Ersetzen(), weiß grad nicht).

Ich nehme an Replace aus VB macht annähernd das gleiche, also Zeichenketten in einem String wechseln/ersetzen, zur Not gegen „“.

Die deutschen Begriffe in Excel wie z.B. Summe müssen in Excel-Vba durch die englischen Worte ersetzt werden, also hier Sum.

Es gibt da ein nettes Add-Inn (von HansHerber) das sich im VB-Editor installiert, dann langt es z.B. „Summe“ auszuwählen, automatisch wird dann im Code Application.Worksheetfunction.Sum eingetragen.
Bei Interesse kann ich dir das schicken bzw. den Link zum Runterladen.
Denn manche Übersetzungen sind nicht so daß man sie erraten könnte, ZählenWenn–>CountIf geht ja, aber daß Sverweis auf englisch vlookup heißt kann man nicht erahnen…

Gruß
Reinhard

Chewpapa_22ca98 · 10. November 2019 um 05:19

Hallo Reihard,

Substitute entspricht der Excelfunktion Wechseln() (oder
Ersetzen(), weiß grad nicht).

Ich nehme an Replace aus VB macht annähernd das gleiche, also
Zeichenketten in einem String wechseln/ersetzen, zur Not gegen
„“.

Ja. Aber Deinen Code habe ich so verstanden, daß Du auch 'Ersetze alle vor dem Zeichen ‚x‘ duch ‚…‘ schreiben kannst, das macht replace nicht. Replace macht nur: Ersetze y durch y, fertig.

Es gibt da ein nettes Add-Inn (von HansHerber) das sich im
VB-Editor installiert, dann langt es z.B. „Summe“ auszuwählen,
automatisch wird dann im Code
Application.Worksheetfunction.Sum eingetragen.
Bei Interesse kann ich dir das schicken bzw. den Link zum
Runterladen.

Ich arbeite nicht mit VBA. Wenn es nicht gerade mal ist, um eine einfache Antwort zu Excel hier zu beantworten und den Code zu testen, verwende ich Excel gar nicht. Privat habe ich es noch nicht mal.
Bei komplizierten Fragen zu VBA bin ich dann auch lieber ganz still.

Gruß, Rainer

Stefan_fad303 · 10. November 2019 um 05:21

Da hast Du uns vermutlich ein Zeichen wegretuschiert, das den
Fehler auslöst.

hi,

die sache hat sich geklärt - das bezog sich auf den post von mir, wo ich grade mitbekam, was instr eigentlich wirklich macht…
er hat dann eben die ersten 8 zeichen von rechts weggekuttet und bei manchen kam er zufällig vorm @ an … bei anderen eben nicht.