verketten mit Leerzeichen

[Papabaer1165]

Hallo zusammen,

ich bastel gerade an einem Problemchen rum und finde die Lösung nicht.
Das Problem ist folgendes:
ich habe eine Verschlüsselte Zeichenfolge wie z.B.:
hk tuz gyngskj ul soyzgqky gtj znay sgqk znks ixosky
Diese gilt es ins lesbare zu übersetzen.
Das Übersetzen ist kein Problem, da sich die Lösung im ascii Bereich nur verschiebt. das Ergebnis ist in diesem Fall ein englischsprachiger Text:
be not ashamed of mistakes and thus make them crimes
dazu splitte ich die Zeichenfolge (es handelt sich nur um den Inhalt einer Zelle) Zeichen für Zeichen auf und verteile die auf mehrere Zellen

Also C1="h"; D1="k"; E1=" "; F1="t"..... u.s.w.
die Werte werden dann umgewandelt in ascii und der ascii Wert um einen bestimmten Wert erhöht - bis maximal 26.

Als Ergebnis habe ich dann aus dem Kauderwelsch eine lesbare Zeile gebastelt.

Nun möchte ich das ganze wieder als ganzen Satz verketten, was an sich kein Problem darstellt - aber.....
beim verketten überspringt Excel die Leerzeichen und gibt mir den Text als ein Wort ohne Leerzeichen raus.

Die Lösung die ich suche sol mir die Leerzeichen als Leerzeichen mit in den Text einbauen - also das ein lesbarer Text mit Leerzeichen entsteht.

Wenn mir dazu jemand auif die Sprünge helfen könnte - das wäre echt der Hammer.

Danke im voraus,
Bernd

[Jonas0806]

Hallo Bernd,

wenn dein verschlüsselter Text in Zelle A1 steht, folgenden Code nach rechts ziehen

PHP-Code:

{=WENNFEHLER(INDEX(WENN(TEIL($A$1;ZEILE(INDIREKT("1:"&LÄNGE($A$1)));1)<>" ";ZEICHEN(CODE(TEIL($A$1;ZEILE(INDIREKT("1:"&LÄNGE($A$1)));1))-6);" ");SPALTE(A1));"End Of Text")} 


Achtung, dies ist eine Matrixformel. Die {} nicht mit eingeben, sondern die Formel mit Strg+Shift+Enter abschließen.

[Fennek]

Hallo,

hier eine Variante mit VBA:

Code:

Function Fen(ByVal rng As Range)

Tx = rng.Value
For i = 1 To Len(Tx)
   If Mid(Tx, i, 1) <> " " Then
       Ty = Ty & Chr(Asc(Mid(Tx, i, 1)) - 6)
   Else
       Ty = Ty & " "
   End If
Next i
Fen = Ty
End Function

Wenn der kodierte Text in A1 steht, dann in einer anderen Zelle: =Fen(A1)

mfg

[Papabaer1165]

Hallo Jonas,

ich habe absolut keinen Plan von MAtrixformeln, könntest Du mir bitte die Formel etwas näher erklären und wie genau ich die einsetzen kann?

Danke im voraus,
Bernd

[shift-del]

Für Office 365 und Excel Online:

	A	B
1	hk tuz gyngskj ul soyzgqky gtj znay sgqk znks ixosky	be not ashamed of mistakes and th[s make them crimes

Zelle	Formel
B1	{=TEXTVERKETTEN("";WAHR;ZEICHEN(WENNFEHLER(1/(1/(CODE(TEIL($A$1;ZEILE(INDIREKT("1:"&LÄNGE($A$1)));1))-32))+32-6;32)))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Jonas0806]

Hi Bernd,

ich habe absolut keinen Plan von MAtrixformeln, könntest Du mir bitte die Formel etwas näher erklären und wie genau ich die einsetzen kann?

Hatte ich das nicht in meinem ersten Post getan?

Dein Text, der verschlüsselt ist, steht in  A1. Kopiere meine Formel in irgendeine Zelle aber ohne die äußeren {}-Klammern. Danach schließt du die Formel nicht, wie gewohnt, mit Enter ab, sondern mit Strg+Shift+Enter. Danach ziehst du die Formel einfach nach rechts.

Näher erklären kann ich sie dir gerne morgen. Habe jetzt keine Lust das auf dem Smartphone zu tippen ;)

[Jockel]

Für ... Excel Online...

Hi Detlef, wie schließt man denn in EO die Matrix-Formel ab..?

[Castor]

Hallo, Bernd;
das kann man in Analogie zu shift-dels Lösung relativ simpel mit StandardFktt und für die, denen TEXTVERKETTEN nicht zV steht, mit einer UDF komplett erledigen - ohne irgendwelche Zwischenschritte! Allerdings arbeite ich in nachfolgender Formel nicht mit ASCII, weil bei der Codierung offensichtlich auch nicht damit gearbeitet wurde (das Ergebnis weicht ab → [ statt u !). Dadurch wird meine singulare (1zellig-1wertige) Matrixformel deutlich länger als Detlefs:
{=VJoin(TEIL(" abcdefghijklmnopqrstuvwxyz";REST(WENNFEHLER(SUCHEN(TEIL(A1;SPALTE(INDIREKT("ZS1:ZS"&LÄNGE(A1);0));1);"abcdefghijklmnopqrstuvwxyz");-20)+20;26)+1;1);"")}
Was eine Matrixformel (mitunter wird sie auch Array-Formel genannt) ist und wie man sie bildet, steht in der Xl-Hilfe (und auch in Xl-Foren ist vieles - Beispiele und Erläuterungen - zu finden). Diese Form ist unerlässlich, wenn eine Formel mehrere Werte liefert und alle auf 1× in einen dadurch fest verbundenen Zellbereich geschrieben werden sollen → plurale (mehrzellig-mehrwertige) Matrixformel. Mitunter muss so Xl aber auch bei nur einem erwar­teten Ergebniswert (singular) „gesagt“ werden, dass alle Werte eines erst zu berechnenden Hauptarguments einbezogen werden sollen. Das kann mitunter so komplex sein, dass die Auswahl nur einer Zelle, auch bei nur einem erwarteten Ergebniswert, nicht reicht; 2 Zellen müssen dann mindestens ausgewählt werden → duale (2zellig-1wertige) Matrixformel. In manchen Fällen (zB im Zusammenhang mit bestimmten INDEX-Argumen­tie­run­gen) reicht primär nicht mal das und man muss die Formel noch ergänzen.
Morrn, Castor

[shift-del]

Hi Detlef, wie schließt man denn in EO die Matrix-Formel ab..?

Hm, vermutlich überhaupt nicht.

[shift-del]

Allerdings arbeite ich in nachfolgender Formel nicht mit ASCII, weil bei der Codierung offensichtlich auch nicht damit gearbeitet wurde (das Ergebnis weicht ab → [ statt u !). Dadurch wird meine singulare (1zellig-1wertige) Matrixformel deutlich länger als Detlefs:
{=VJoin(TEIL(" abcdefghijklmnopqrstuvwxyz";REST(WENNFEHLER(SUCHEN(TEIL(A1;SPALTE(INDIREKT("ZS1:ZS"&LÄNGE(A1);0));1);"abcdefghijklmnopqrstuvwxyz");-20)+20;26)+1;1);"")}

Meine korrigierte Formel wäre dann:

Code:

{=TEXTVERKETTEN("";WAHR;ZEICHEN(WENNFEHLER(1/(1/((CODE(TEIL($A$1;ZEILE(INDIREKT("1:"&LÄNGE($A$1)));1))-6<97)*26+CODE(TEIL($A$1;ZEILE(INDIREKT("1:"&LÄNGE($A$1)));1))-6-52))+52;32)))}