Hallo Leute,
ich bin neu hier im Forum und habe eine Frage an Euch.
Für ein Projekt brauche ich eine Formel. Wie hier auf dem Bild zu sehen möchte ich in die SpalteA verschiedene Straßennamen einfügen die ich mir aus der Datenbank ziehe. Um später eine Berechnung durchzuführen benötige ich die Straßennamen und Hausnummer getrennt. Kann man in Excel eine Formel schreiben die mir die Straße in SpalteB setzt und die Hausnummern in SpalteC ? Außerdem sollten bei doppelten Hausnummern (wie in SpalteA2 zu sehen) die letzte Zahl und das Minuszeichen gelöscht werden.
Kann mir bitte jemand dabei helfen ? Ich werde noch verrückt, weil ich keine Lösung finde :s
[
Bild bitte so als Datei hochladen: Klick mich!]
Hi,
(09.03.2017, 18:14)lupo1 schrieb: [ -> ]http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=350
ich glaube, das ist noch komplizierter und mit den dort vorhandenen Formeln nicht ganz lösbar, siehe Beispiele:
Tabelle1 | A | B | C |
| 1 | Müllerstraße 120-128 | Müllerstraße | 120-128 |
| 2 | Müllerstraße 120 | Müllerstraße | 120 |
| 3 | Müllerplatz123 | #WERT! | #WERT! |
| 4 | Müllerplatz124-128 | Müllerplatz | 24-128 |
| verwendete Formeln | |
| Zelle | Formel | Bereich | N/A |
| B1:B3 | =LINKS(A1;FINDEN("#";WECHSELN(A1;" ";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))))-1) | | |
| B4 | =LINKS(A4;LÄNGE(A4)-VERWEIS(2;1/LINKS(RECHTS(A4&1;SPALTE(A4:Z4)))/ISTFEHLER(SUCHEN(".";RECHTS(A4&0;SPALTE(A4:Z4))));SPALTE(A1:Z1)-1)) | | |
| C1:C4 | =RECHTS(A1;LÄNGE(A1)-LÄNGE(B1)-1) | | |
| Excel-Inn.de |
| Hajo-Excel.de |
| XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007 |
| Add-In-Version 19.07 einschl. 64 Bit |
Hallo,
die Formellösung funktioniert doch:
Tabelle1 | A | B | C |
| 1 | Müllerstraße 120-128 | Müllerstraße | 120-128 |
| 2 | Müllerstraße 120 | Müllerstraße | 120 |
| 3 | Müllerplatz123 | Müllerplatz | 123 |
| 4 | Müllerplatz124-128 | Müllerplatz | 124-128 |
| Formeln der Tabelle |
| Zelle | Formel | | B1 | =LINKS(A1;LÄNGE(A1)-VERWEIS(2;1/LINKS(RECHTS(A1&1;SPALTE(A1:Z1)))/ISTFEHLER(SUCHEN(".";RECHTS(A1&0;SPALTE(A1:Z1))));SPALTE(A1:Z1)-1)) | | C1 | =GLÄTTEN(WECHSELN(A1;B1;)) |
|
Bezogen auf das Beispiel von Ralf wären auch diese benutzerdefinierten Funktionen eine Option:
Tabelle1 | A | B | C |
| 1 | Müllerstraße 120-128 | Müllerstraße | 120-128 |
| 2 | Müllerstraße 120 | Müllerstraße | 120 |
| 3 | Müllerplatz123 | Müllerplatz | 123 |
| 4 | Müllerplatz124-128 | Müllerplatz | 124-128 |
| Formeln der Tabelle |
| Zelle | Formel | | B1 | =StrName(A1) | | C1 | =HsNr(A1) |
|
Code:
Public Function PosHsNrInStrasse(Strasse As String) As Integer
'http://www.office-loesung.de/viewtopic.php?t=7147
Dim Zaehler As Integer
Dim Laenge As Integer
Dim X As String
Dim Ergebnis As String
Laenge = Len(Strasse)
PosHsNrInStrasse = 0
'von rechts nach links durch Strassennamen gehen
'bis auf die 3 linken Zeichen damit Strassen, die mit Zahl beginnen
'(z.B. 3. Terwestenweg) nicht als Hausnummer erkannt werden
For Zaehler = Laenge To 3 Step -1
X = Mid(Strasse, Zaehler, 1) 'aktuell zu prüfendes Zeichen
If IsNumeric(X) Then 'prüfen, ob Zeichen eine Zahl ist
PosHsNrInStrasse = InStr(Strasse, X) 'Position der Zahl
End If
Next
End Function
Public Function HsNr(Strasse As String) As String
Dim pos As Integer
Dim Laenge As Integer
pos = PosHsNrInStrasse(Strasse)
Laenge = Len(Strasse)
If pos > 0 Then
HsNr = Right(Strasse, Laenge - pos + 1)
Else
HsNr = ""
End If
'MsgBox HNr
End Function
Public Function StrName(Strasse As String) As String
Dim pos As Integer
Dim Laenge As Integer
pos = PosHsNrInStrasse(Strasse)
Laenge = Len(Strasse)
If pos > 0 Then
'Trim: führende und nachgestellte Leerzeichen entfernen
StrName = Trim(Left(Strasse, pos - 1))
Else
StrName = Strasse
End If
'MsgBox StrName
End Function
Hallo ,
hier mal noch eine Alternative Formel:
Tabelle1 | A | B | C |
| 1 | | | |
| 2 | Dingsbums113d | Dingsbums | 113d |
| 3 | Dadaistmus 124 | Dadaistmus | 124 |
| 4 | Hastiger 15-55 | Hastiger | 15-55 |
| 5 | Straße des 17. Juni 35 | Straße des 17. Juni | 35 |
| 6 | | | |
| Formeln der Tabelle |
| Zelle | Formel | | B2 | {=LINKS(A2;MAX(WENN(ISTZAHL(SUCHEN("???" &ZEILE($1:$9);A2));SUCHEN("???" &ZEILE($1:$9);A2))))} | | C2 | =GLÄTTEN(WECHSELN(A2;B2;"")) | | B3 | {=LINKS(A3;MAX(WENN(ISTZAHL(SUCHEN("???" &ZEILE($1:$9);A3));SUCHEN("???" &ZEILE($1:$9);A3))))} | | C3 | =GLÄTTEN(WECHSELN(A3;B3;"")) | | B4 | {=LINKS(A4;MAX(WENN(ISTZAHL(SUCHEN("???" &ZEILE($1:$9);A4));SUCHEN("???" &ZEILE($1:$9);A4))))} | | C4 | =GLÄTTEN(WECHSELN(A4;B4;"")) | | B5 | {=LINKS(A5;MAX(WENN(ISTZAHL(SUCHEN("???" &ZEILE($1:$9);A5));SUCHEN("???" &ZEILE($1:$9);A5))))} | | C5 | =GLÄTTEN(WECHSELN(A5;B5;"")) |
|
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! |
| Matrix verstehen |
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
VG
Steffen
Moin!
Auch wenn ich Excelformeln sehr schätze.
Besser als die beste Prothese ist es doch, das Bein gar nicht erst zu verlieren. :21:
Zitat:Wie hier auf dem Bild zu sehen möchte ich in die SpalteA verschiedene Straßennamen einfügen die ich mir aus der Datenbank ziehe.
Da solltest Du ansetzen, beim Import!
Gruß Ralf
Hi Peter,
(11.03.2017, 16:10)Peter schrieb: [ -> ]die Formellösung funktioniert doch:
nicht ganz: er wollte bei 120-128 nur die 120 als Ergebnis.
Hallo,
das habe ich übersehen, aber das könnte man dann noch mit Text-Funktionen wie =LINKS(A1;FINDEN("-";A1)-1) o.ä. erschlagen.