Excel Funktions-Formel gesucht

[Gudrun]

Liebe ExpertInnen,

guckt Euch bitte das Bild an:
.

.
Wie man sieht, gibt es:

• Zahl links: 1- bis 4-stellig (mit 1.000er-Punkt)

• Zahl zweite von links: 1- (incl. 0) bis 2-stellig

• zwischen den Zahlen und dem Namen sind jeweils 2 Leerzeichen

  1.086 19 Stuttgart Stadtkreis
  896 11 Stuttgart Stadtkreis
  451 1 Stuttgart Stadtkreis
  94 0 Stuttgart Stadtkreis
  5 0 Stuttgart Stadtkreis

Ich krieg’s mit LINKS() + ERSETZEN() hin, brauche aber dazu drei Hilfsspalten.
Danach kann ich die Formeln nicht einfach 43 mal runterkopieren, sondern muß sie bei jedem Wechsel der Anzahl Stellen anpassen.

Alles etwas umständlich.

Wer bitte hat Zeit und Lust, mir eine Formel zu basteln, die alle Fälle auf einen Schlag abdeckt?
(Bitte berücksichtigen: mein Excel97 kann nur max. 7 „Schachteln“ verhackstücken.)

Danke im voraus + Gruß

[Pierre]

@Gudrun

Bitte berücksichtigen: mein Excel97

Au weia. Sollen die Nutzer von w-w-w mal zusammenlegen für einen halblegalen Schlüssel eines modernen Office?

Gibt es schon die Funktion Daten->Text?
Office Support

Ansonsten gibt es hier Hinweise, wie man mit links() suchen(" ") Text teilen kann. Und ich glaube, das habe ich damals auch schon benutzt.
Office Support

Grüße
Pierre

[littlepinguin]

hi,

Danach kann ich die Formeln nicht einfach 43 mal runterkopieren, sondern muß sie bei jedem Wechsel der Anzahl Stellen anpassen.

wäre doch errechenbar mit suchen()

zwischen den Zahlen und dem Namen sind jeweils 2 Leerzeichen
jeweils 2? also ist das Beispiel falsch? da ist nur jeweils 1.

mal ganz grob:
=LINKS(A1;SUCHEN(" ";A1)-1)
=LINKS(ERSETZEN(A1;1;SUCHEN(" ";A1);"");2)
=ERSETZEN(A1;1;SUCHEN(" ";A1;SUCHEN(" ";A1)+2);"")

die 2. Zahl sieht nur schön aus, müsste man das Leerzeichen eben noch entfernen, wenns sein müsste.

grüße
lipi

lass mich raten… suchen gabs noch nicht, oder?

[Flowerwoman]

@Gudrun

Bitte berücksichtigen: mein Excel97 kann nur max. 7 „Schachteln“ verhackstücken

Du musst nicht schachteln, du kannst auch mit && verketten, da gibt es keine Grenzen.

WENN(A1=1;"1";"")&&WENN(A1=2;"2";"")&&WENN(A1=3;"3";"")&&[...]

Warum genau nimmst du kein LibreOffice? Gerade in Sachen Excel gibt es faktisch keine Inkompatibilitäten, schon gar nicht zu Excel97. Dafür kann es aber xlsx lesen und schreiben und ist vermutlich 3mal so schnell bei allem.

[Gudrun]

Hi,
sorry für späte Reaktion und ganz herzlichen Dank für Deine Mühe und ein extra Danke, daß Du meine Bitte „einfach so“ erfüllt hast.

Nr. 1:

@littlepinguin

=LINKS(A1;SUCHEN(" ";A1)-1)

paßt perfekt.

Nr. 2 + 3:

@littlepinguin

=LINKS(ERSETZEN(A1;1;SUCHEN(" ";A1);"");2)
=ERSETZEN(A1;1;SUCHEN(" ";A1;SUCHEN(" ";A1)+2);"")

leider leider nicht so ganz, denn

@littlepinguin

jeweils 2? also ist das Beispiel falsch? da ist nur jeweils 1.

ja, es sind jeweils 2
(da hat mir die www-Software die zweiten geklaut und ich hab’s nicht bemerkt).

@littlepinguin

die 2. Zahl sieht nur schön aus, müsste man das Leerzeichen eben noch entfernen, wenns sein müsste.

Ja, müßte „man“ (bitte Du! ) und auch beim Ortsnamen ist links ein Leerzeichen zuviel.
Ich habe ein bißchen rumprobiert, aber da ich die Formeln nicht wirklich durchschaue und mir das Geduldsgen fehlt, kam nix bei rum.

Bist Du so nett und machst mir die Formeln nochmal mit je 2 Leerzeichen?

Die Vorlage nochmal und jetzt in „pre“ gesetzt:

1.086  19  Stuttgart Stadtkreis
896  11  Stuttgart Stadtkreis
451  1  Stuttgart Stadtkreis
94  0  Stuttgart Stadtkreis
5  0  Stuttgart Stadtkreis

@littlepinguin

lass mich raten… suchen gabs noch nicht, oder?

Aber selbstverständlich doch!
.

.
Das nützt mir aber nix, weil ich den „Schachtelkram“ nicht gebacken kriege,

Hilfst Du mir bitte erneut?

Danke + Gruß

[littlepinguin]

hi,

=LINKS(A1;SUCHEN(" ";A1)-1)
=WECHSELN(LINKS(ERSETZEN(A1;1;SUCHEN(" ";A1)+1;"");2);" ";"")
=ERSETZEN(A1;1;SUCHEN(" ";A1;SUCHEN(" ";A1)+2)+1;"")

so?

@Gudrun

Das nützt mir aber nix, weil ich den „Schachtelkram“ nicht gebacken kriege,

wenn ich da 2 Tage später drauf schau, geht’s mir auch wieder so.

grüße
lipi

Beitrag korrigiert, so dass auch die letzte Zeile der Formel richtig dargestellt wird
Pierre MOD

[littlepinguin]

hi,

ach sry.
nicht nochmal drauf geschaut.
Es hat auch die doppelten Leerzeichen gefressen.

=LINKS(A1;SUCHEN(" ";A1)-1)
=WECHSELN(LINKS(ERSETZEN(A1;1;SUCHEN(" ";A1)+1;"");2);" ";"")
=ERSETZEN(A1;1;SUCHEN(" ";A1;SUCHEN(" ";A1)+2)+1;"")

das müsste wieder stimmen, könnte auch sein, dass es mit einem auch geht. bin gerad zu faul das Zuende zu denken.

[Gudrun]

@littlepinguin

so?

Perfekt. Ich bin begeistert.

@littlepinguin

Es hat auch die doppelten Leerzeichen gefressen.

Keine Ahnung, was Du damit meinst.
Zwischen den Gänsefüßchen sind entweder 1 oder 0 Leerzeichen, damit funzt es.

@littlepinguin

das müsste wieder stimmen

Ich sehe jetzt keinen Unterschied zu Deiner ersten Version und die erste hat ja schon geklappt.

Was ganz anderes:
Es gelingt mir nicht, die beiden Zahlen-Spalten mit der Summenformel
=SUMME() zu addieren. Ergebnis ist 0.
Wenn ich die Zellen einzeln addiere mit =C1+C2+C3 … kommt die richtige Summe raus.
Format ist überall „Standard“.

Hast Du eine Erklärung hierfür?
Das ist jetzt nicht soooooo wichtig (ich kenne die Summen), aber ich stolpere das erstemal über dieses Phänomen.

Vielen Dank + Gruß

[littlepinguin]

hi,

@Gudrun

Hast Du eine Erklärung hierfür?

sind Texte, keine Zahlen.

der Punkt muss vorn raus und dann noch in Text umwandeln.

also einmal =wert(wechseln(LINKS(A1;SUCHEN(" ";A1)-1);".";""))
und
=wert(WECHSELN(LINKS(ERSETZEN(A1;1;SUCHEN(" ";A1)+1;"");2);" ";""))

grüße
lipi

[Gudrun]

@littlepinguin

sind Texte, keine Zahlen.

Ja schon, aber wieso funktioniert die Einzel-Addi?
Egal.
Mit Wert() vornedran ist jetzt alles perfekt, perfekter, am perfektesten.

Gruß

[littlepinguin]

@Gudrun

Ja schon, aber wieso funktioniert die Einzel-Addi?

keine Ahnung.
Das ist in openoffice aber witziger weise genau so. zelle+zelle geht, summe() geht nicht.

Man muss auch nicht alles wissen, recht wenn man die Lösung findet.

grüße
lipi

[Gudrun]

@Pierre

Au weia. Sollen die Nutzer von w-w-w mal zusammenlegen für einen halblegalen Schlüssel eines modernen Office?

Meinetwegen. Ich würde die „Spende“ aber für was anderes nutzen, weil ich kein anderes Office möchte.

@Pierre

Gibt es schon die Funktion Daten->Text?

Klar gibts die.
Ergibt aber wegen
„913 54 Freiburg im Breisgau Stadtkreis“
immer 6 Spalten

und wegen der notwendigen Unterscheidung zwischen:
828 45 Karlsruhe Landkreis
und
317 4 Karlsruhe Stadtkreis

sind 3 Spalten das einzig Sinnvolle

@Pierre

Ansonsten gibt es hier Hinweise, wie man mit links() suchen(" ") Text teilen kann.

Ich schrieb doch, daß ich imstande bin, Text zu teilen, aber nach der eleganten Lösung suche.

Die Frage „wer bastelt mir“ bedeutet, daß ich dazu selber nicht fähig bin.

Und der Hinweis „wer hat Zeit und Lust“ bedeutet im Umkehrschluß, daß, wer keine Zeit und/oder keine Lust hat, einfach weiterklickt.

Ich werde diese „Mode“ (bei mir fällt das unter Unart!), daß jemand Zeit und Energie verschwendet, um auf glasklar formulierte Fragen alles mögliche zu antworten, nur keine Antwort auf die Frage, nie verstehen.

P.S. die Erweiterung Deiner baden-württembergischen Geografie-Kenntnisse ist umme.

Gruß

.

[Pierre]

@Gudrun

Ich werde diese „Mode“ (bei mir fällt das unter Unart!), daß jemand Zeit und Energie verschwendet, um auf glasklar formulierte Fragen alles mögliche zu antworten, nur keine Antwort auf die Frage, nie verstehen.

Ich werde diese „Macke“ (Mode möchte ich es nicht nennen) wohl nie verstehen, dass Leute sich über Antworten beschweren, die ihnen nicht gefallen. Aber nun gut. Jeder soll nach seiner Façon selig werden.

Grüße
Pierre

[Niclaus_Wurthberg]

Hallo Gudrun
Vor lauter Nebenschauplätzen sieht man gar nicht mehr, ob nun Dein Problem gelöst ist oder nicht. Falls nicht: Hast Du es schon einmal mit „Blitzvorschau“ (Excel 2013) versucht?

ZU SPÄT - Ich habe erst jetzt realisiert, dass Du mit Excel 97 arbeitest, und kann diesen Beitrag nicht mehr löschen, nur noch bearbeiten.

Niclaus

[Gudrun]

Hi,

@Niclaus_Wurthberg

ob nun Dein Problem gelöst ist oder nicht.

danke der Nachfrage.
Alles zu meiner Zufriedenheit gelöst:

@Gudrun

Mit Wert() vornedran ist jetzt alles perfekt, perfekter, am perfektesten.

Das einzige, was offenblieb, war die eher akademische Frage, warum die Summenformel nicht funktioniert, wenn die Formel ohne WERT() gestaltet ist.

@Gudrun

Was ganz anderes:
Es gelingt mir nicht, die beiden Zahlen-Spalten mit der Summenformel
=SUMME() zu addieren. Ergebnis ist 0.
Wenn ich die Zellen einzeln addiere mit =C1+C2+C3 … kommt die richtige Summe raus.
Format ist überall „Standard“.

Wie oben auch geschreiben, es ist nicht wichtig (bzw. mit WERT() vornedran jetzt erledigt). Wenn Du aber auf Anhieb eine Erklärung hast, dann tu Dir keinen Zwang an.
Hier nochmal zum Vergleich:

=LINKS(A1;SUCHEN(" „;A1)-1) —>Summenformel funzt nicht, Einzel-Addi funzt
=WERT(LINKS(A1;SUCHEN(“ ";A1)-1)) —>Summenformel funzt.

Gruß

[Niclaus_Wurthberg]

Hallo Gudrun

Wenn Du aber auf Anhieb eine Erklärung hast,
Die Erklärung ist oben schon geliefert worden: Die Formel LINKS() ergibt einen Text (hier verschiedene Ziffern, aber keine „Excel-konforme“ Zahlen). Deshalb funktioniert SUMME() nicht. Warum es mit Einzelsummanden trotzdem geht (C1 + C2 …), das wird ein Redmonder-Geheimnis bleiben.

Eine Variante hätte ich Dir noch für SUMME(), so dass Du die Formel WERT() nur einmal eingeben musst: =SUMME(WERT(C1:C5))
Diese Formel musst Du als Matrix-Formel eingeben: Statt nur mit RETURN musst Du sie abschliessen mit CTRL-SHIFT-RETURN. Es werden dann automatisch die Klammern „{“ und „}“ eingesetzt. Ob das in Deiner Excel-Version schon klappt, weiss ich allerdings nicht (mehr).

Einen schönen Sonntag und viele Grüsse Niclaus

[Gudrun]

Hi,

@Niclaus_Wurthberg

das wird ein Redmonder-Geheimnis bleiben.

grandiose Erklärung, die immer auf Unerklärliches paßt.

@Niclaus_Wurthberg

Eine Variante hätte ich Dir noch für SUMME(), so dass Du die Formel WERT() nur einmal eingeben musst: =SUMME(WERT(C1:C5))

Danke, funktioniert.

@Niclaus_Wurthberg

als Matrix-Formel eingeben

@Niclaus_Wurthberg

Ob das in Deiner Excel-Version schon klappt, weiss ich allerdings nicht (mehr).

Aber selbstverständlich doch!
Mit einer Matrix-Formel hatte ich ca. 1998 das erstemal zu tun.
Um die Stunden vom Arbeitszettel zu addieren, brauchte ich bis dahin zwei Hilfsspalten zum Umwandeln der Std. und Min. und fand dann in einem TB-Buch „Excel - Tipps und Tricks“ diese ellenlange Matrix-Formel, die ich jetzt grad mal aus den Tiefen meiner Festplatte ans Tageslicht geholt habe:
=WECHSELN(FEST(GANZZAHL(SUMME(STUNDE(I5:I695))+SUMME(MINUTE(I5:I695))/60)+REST(SUMME(MINUTE(I5:I695))/60;1)*0,6;2);";";":")

Ich wußte auch damals nicht, was ich tat , habe nur stur abgetippt (eine Heidenarbeit!), aber das Ergebnis war einfach super.

Soviel dazu aus dem Nähkästchen.

Schönen Rest-1. Mai + Gruß

[toshni]

'nth gibt das nte Wort in einem String zurück
'T=Text,I=Index,S=Separator
'z.B.: nth("Das ist ein Test",2) => "ist"
'z.B.: nth("Das ist ein Test",-2) => "ein"
Function NTH$(ByVal T$, ByVal I%, Optional S$ = " ")
 Dim ARR
 ARR = Split(S & T, S)
 If I >= 0 Then
  NTH = ARR(I)
 Else
  NTH = ARR(UBound(ARR) + 1 + I)
 End If
End Function

Die Rückgabe von nth kann natürlich in Zahl, Datum usw. konvertiert werden. Gruss Thomas