Microsoft Excel

Herbers Excel/VBA-Archiv

Leerstellen

Betrifft: Leerstellen von: Andreas
Geschrieben am: 18.11.2014 13:10:22

Hallo an alle Excel Freunde

ich möchte gern eine 10-stellige Zahl/Buchstabenkombination ( 3F123GR3Z5)
eingeben, Jedoch möchte ich diese Kombination mit 5 Leerstellen zwischen den einzelnen Zahlen/Buchstaben entzerren, weil es in eine Vorlage eingefügt wird.
Ich bekomme leider die Zellformation nicht gebacken.
Könnte mir jemand einen Tip geben, wie diese aussehen muss.
Danke im voraus.

mfg
Andreas

  

Betrifft: AW: Leerstellen von: Hajo_Zi
Geschrieben am: 18.11.2014 13:14:25

Hallo Andreas,

das ist Text und da geht keine Formatierung.

Gruß Hajo


  

Betrifft: AW: Leerstellen von: Rudi Maintaire
Geschrieben am: 18.11.2014 13:16:27

Hallo,
Text kann man kein spezielles Format geben.

Gruß
Rudi


  

Betrifft: AW: Leerstellen von: Michael
Geschrieben am: 18.11.2014 13:20:30

Hallo Andreas!

Da ich nicht weiß, wie genau Du das in der Tabelle brauchst, hier ein (holpriger) Versuch, wie Du das erreichen kannst. Du benötigst eine Hilfsspalte: In Spalte 1 gibst Du Deine Kombination ein, in der Zelle daneben kannst Du folgende Formel eingeben: (in meinem Bsp. steht Deine Kombination in Zelle G15

=VERKETTEN(LINKS(G15;5);" ";" ";" ";" ";" ";RECHTS(G15;5))

So hast Du Deine eingegebene Kombination "optisch getrennt"

Gib Bescheid, ob Dir das hilft!
Michael


  

Betrifft: AW: Leerstellen von: Rudi Maintaire
Geschrieben am: 18.11.2014 13:46:09

Hallo,
was sollen denn die vielen " ";???
=VERKETTEN(LINKS(A2;5);"     ";RECHTS(A2;5))
bzw.
=LINKS(A2;5) & "     " & RECHTS(A2;5)

Gruß
Rudi


  

Betrifft: AW: Leerstellen von: Michael
Geschrieben am: 18.11.2014 13:53:13

Hallo Rudi!

Danke für die Klärung. Da war ich etwas sparsam in meiner Erklärung: ich wollte damit eigentlich aufzeigen, dass "Verketten" hier mehrere Elemente aufnehmen kann, und Andreas sich hiermit evtl. noch andere Konstruktionen bauen kann, wenn gewünscht.

War gut gemeint, sorry! @ Andreas: Wenn's nur um 5 Leerzeichen geht, ist Rudis Variante selbstverständlich sinnvoller.

Michael


  

Betrifft: Man kann Texte nur formatieren, indem man ... von: Luc:-?
Geschrieben am: 18.11.2014 13:48:05

…den gewünschten Text, also so wie er im konkreten Fall aussehen soll, als benutzerdefiniertes Format mit 3 vorangestellten Semikolons einträgt, Andreas,
also hier zB ;;;"3 F 123 GR 3 Z 5" (was dann aber 6 Leerzeichen wären!).
Da dieser Vorschlag schon in der Vergangenheit abundzu gemacht wurde und deshalb auch im Archiv auffindbar ist, wundert es mich denn doch, dass diese Möglichkeit weitgehend unbekannt ist. Allerdings ist sie manuell unflexibel und sollte deshalb per VBA über das Worksheet_Change-Ereignis gesteuert wdn.
Eine andere Methode, falls die Buchstaben immer gleich blieben, wäre es, nur die Ziffern als Zahl zu verwenden und die Buchstaben erst mit dem benutzerdefinierten Format einzufügen. Würde man dann aber das sichtbare Ergebnis als Text (für Vgle u.ä.) benötigen, müsste per VBA der Zell.Text ausgelesen wdn, aus dem dann ggf auch noch die Leerzeichen entfernt wdn müssten.
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Man kann Texte nur formatieren, indem man ... von: Andreas
Geschrieben am: 18.11.2014 13:57:00

Hallo an Alle, die mir dazun geschrieben haben.

Ich wußte nicht, das man Zellen mit Buchstaben nicht formatieren kann.
Die fünf Leerzeichen sollen auch zwischen jeder einzelnen Zahl oder Buchstabe vorhanden sein
( 1.....D.....1.....2.....H.....B..... usw.)
Da sich die Buchstaben immer in Anzahl und Ort der 10-stelligen Kombination ändert
werde ich diese doch wohl die nächsten 20 Jahre (so lange muss ich noch) weiterhin so von Hand eingeben.
Ich danke Euch allen für die Mühen und die zahlreichen Antworten.

einen schönen Tag noch

Andreas


  

Betrifft: und warum keine normale Eingabe ..: von: neopa C (paneo)
Geschrieben am: 18.11.2014 15:02:39

Hallo Andreas,

... also die 10 Zeichen hintereinanderweg in einer "Eingabehilfsspalte" und die Eingabe-Ergebnisspalte mit einer Formel erzeugen?

Angenommen die "Eingabehilfsspalte" wäre Spalte E dann in die "Eingabe-Ergebnisspalte" folgende Formel:
in der ersten Zeile:

=WECHSELN(LINKS(E1;1)&"#"&TEIL(E1;2;1)&"#"&TEIL(E1;3;1)&"#"& ... &TEIL(E1;10;1);"#";" ")

Für die drei Punkte musst natürlich noch die Formelergänzung für Zeichen 4 bis 9 vornehmen.

Gruß Werner
.. , - ...


  

Betrifft: AW: und warum keine normale Eingabe ..: von: Andreas
Geschrieben am: 18.11.2014 15:18:25

Hallo Werner

also "Hut ab".
Funktioniert einwandfrei. Danke für deine Mühen.

Das erleichert die Arbeit doch auf die Dauer.

ich wünsche eine schönen Tag noch.

Andreas - Danke nochmal


  

Betrifft: Na, da hatten wir dich ja ziemlich miss- ... von: Luc:-?
Geschrieben am: 18.11.2014 16:15:42

…verstanden, Andreas;
da es ja nur um das zeichen­gerechte Positionieren in einem Formular geht, muss ja nichts formatiert, sondern nur erzeugt wdn. Also reichen die angegebenen Fml-Lösungen (speziell von Werner mit Ergebniszellen) oder Rudis DirektLösung am Original.
Falls eine kürzere Fml-Lösung unter Einsatz auch anderweitig nutzbarer universeller UDFs interessant wäre, könnte die zB so aussehen: =VJoin(Sprite(A1;"");WIEDERHOLEN(" ";5))
Hier könnte natürlich auch gleich 5 Leerzeichen statt WIEDERHOLEN(" ";5) und die universellere UDF MxJoin (im Archiv zu finden) verwendet wdn. Ansonsten folgende PgmCodes in ein allgemeines Modul (Einfügen - Modul) des (VBA-)Projekts kopieren:

Rem Verbinden aller Elemente eines beliebigen Vektors
'   Vs1.0 -LSr -cd:20130904 -1pub:20130905herber -lupd:20130904t
Function VJoin(Bezug, Optional ByVal BindeZ As String = " ")
    On Error Resume Next
    With WorksheetFunction
        If TypeName(Bezug) = "Range" Then
            Bezug = .Transpose(.Transpose(Bezug))
        End If
        If IsError(LBound(Bezug, 2)) Then
            VJoin = Join(Bezug, BindeZ)
        Else: Bezug = .Transpose(Bezug)
            If IsError(LBound(Bezug, 2)) Then
                VJoin = Join(Bezug, BindeZ)
            Else: VJoin = CVErr(xlErrRef)
            End If
        End If
    End With
End Function

Rem F18: udF erzeugt aus TextBezug(sVektor) m.def TrennZch 1 ZeilVekt(bzw 1 Matrix)Feld
'   Auf volle MxFmlFähigk b.diff QuellTxt u.Verarbeit v.MxKonstt in Textform erweiterte
'   VarKomb d.xxlFktt Splint u.TransFor aus xlAddIn FXsubset v.Autor LSr[CyWorXxl.FXss]
'   Vs2.3 -Autor: LSr -CDate: 20070427 -1Pub: 20070428 herber.de (1.2) -lUpD: 20110810n
Function Sprite(ByVal Bezug, Optional ByVal TrennZ As String = " ")
    Const orMxKlTr As String = "{ , ; }"
    Dim s As Long, z As Long, i As Integer, n As Integer, arvErg(), ZwErg As Variant, _
        DzTr As String, LiTr As String, MxKl(1) As String, MxTr(1) As String, arvBez
    On Error Resume Next
    With Application
        DzTr = .International(xlDecimalSeparator)
        LiTr = .International(xlListSeparator)
        MxKl(0) = .International(xlLeftBrace)
        MxKl(1) = .International(xlRightBrace)
        MxTr(0) = .International(xlColumnSeparator)
        MxTr(1) = .International(xlRowSeparator)
    End With
    If IsArray(Bezug) Then
        arvBez = Bezug
        If TypeName(arvBez) = "Range" Then
            ReDim arvErg(arvBez.Cells.Count - 1, 0)
        ElseIf IsError(LBound(arvBez, 2)) Then
            ReDim arvErg(UBound(arvBez) - LBound(arvBez), 0)
        ElseIf UBound(arvBez, 2) = LBound(arvBez, 2) Then
            arvBez = WorksheetFunction.Transpose(arvBez)
            If IsError(LBound(arvBez, 2)) Then
                ReDim arvErg(UBound(arvBez) - LBound(arvBez), 0)
            Else: Sprite = CVErr(xlErrRef): Exit Function
            End If
        Else: ReDim arvErg(UBound(arvBez) - LBound(arvBez), 0)
        End If
        For Each Bezug In arvBez
            GoSub eb: ZwErg = Sprite
            If UBound(ZwErg) > UBound(arvErg, 2) Then _
                ReDim Preserve arvErg(UBound(arvErg, 1), UBound(ZwErg))
            For s = 0 To UBound(ZwErg)
                arvErg(z, s) = ZwErg(s)
            Next s
            z = z + 1
        Next Bezug
        Sprite = arvErg: Exit Function
    End If
eb: If Left(Bezug, 1) = MxKl(0) And Right(Bezug, 1) = MxKl(1) Then
        If TrennZ = "" Then
            If Mid(Bezug, 2, 1) = """" Then
                i = 3: n = 2: TrennZ = """" & MxTr(0) & """"
            ElseIf IsNumeric(Mid(Bezug, 2, Len(Bezug) - 2)) Then
                i = 2: n = 1: TrennZ = MxTr(0)
            Else: Bezug = MxKl(0) & """" & Mid(Bezug, 2, Len(Bezug) - 2) & _
                          """" & MxKl(1): i = 3: n = 2
                TrennZ = """" & MxTr(0) & """"
            End If
            While i < Len(Bezug) - n
                Bezug = Left(Bezug, i) & TrennZ & Mid(Bezug, i + 1)
                i = i + Len(TrennZ) + 1
            Wend
        End If
        If CBool(InStr(Bezug, MxTr(0))) Or CBool(InStr(Bezug, MxTr(1))) Then
            If DzTr = MxTr(0) Then Bezug = Replace(Bezug, DzTr, Chr(1))
            For i = 0 To 1
                Bezug = Replace(Replace(Bezug, MxKl(i), Split(orMxKlTr)((i + _
                        1) ^ 2 - 1)), MxTr(i), Split(orMxKlTr)(i + 1))
            Next i
            If DzTr = MxTr(0) Then Bezug = Replace(Bezug, Chr(1), ".")
            Sprite = Evaluate(Bezug)
        ElseIf CBool(InStr(Bezug, Split(orMxKlTr)(1))) Or _
                CBool(InStr(Bezug, Split(orMxKlTr)(2))) Then
            Sprite = Evaluate(Bezug)
        ElseIf Not CBool(InStr(Bezug, TrennZ)) Then
            Sprite = Mid(Bezug, 2, Len(Bezug) - 2)
        Else: GoTo ns
        End If
    ElseIf TrennZ = "" Then
        i = 1
        While i < Len(Bezug)
            Bezug = Left(Bezug, i) & " " & Mid(Bezug, i + 1): i = i + 2
        Wend
        Sprite = Split(Bezug)
    ElseIf CBool(InStr(Bezug, TrennZ)) Then
ns:     If CBool(InStr(Bezug, LiTr & TrennZ)) Then
            Sprite = Split(Bezug, LiTr & TrennZ)
        Else: Sprite = Split(Bezug, TrennZ)
        End If
    ElseIf Bezug = "" Then
        Sprite = Empty
    Else: Sprite = Split(Bezug, LiTr)
    End If
    Err.Number = 0
    If Not IsEmpty(arvBez) Then Return
End Function
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Na, da hatten wir dich ja ziemlich miss- ... von: Andreas
Geschrieben am: 18.11.2014 16:21:39

Hallo Luc

wenn ihr mich missverstanden habt, tut es mir leid, war nicht die Absicht.
Ich habe mich bestimmt auch falsch ausgedrückt.
Da ich in VBA nicht zurecht komme, ist mir die Lösung von Werner recht angenehm.
Trotzdem danke ich dir für Deine Hilfe und Mühe, aber mit deiner Ausführrung
wäre ich restlich überfordert.
Schönen Abende noch

Andreas


  

Betrifft: Nett, dass du mir auch noch mal antwortest, ... von: Luc:-?
Geschrieben am: 18.11.2014 16:56:46

…Andreas,
leider aber nicht Rudi, für dessen Lösung ich mal ebenfalls Überforderung deinerseits annehmen will. ;-)
Es ist in Xl leider so, dass es MS bisher nicht für nötig hielt, manche Fktt, die es in anderer Software und auch in VBA gibt, in Xl zu implementieren, was besonders auf textver­arbeitende Fktt zutrifft. Deshalb ist man nicht schlecht beraten, wenn man sich ein kleines Arsenal in VBA pgmierter sog UDFs zulegt. Allerdings sollte man dann nicht Mappen mit Fmln, die diese enthalten, an Dritte, die nicht über diese UDFs verfügen, weitergeben (sind die UDFs in der Mappe mit ihren Pgmm enthalten, ist das nur dann ein Problem, wenn der Empfänger nicht über VBA verfügen kann/darf). Diese Fmln sollten dann zuvor durch ihre Ergebnisse ersetzt wdn.
Luc :-?


  

Betrifft: AW: Leerstellen per VBA von: Rudi Maintaire
Geschrieben am: 18.11.2014 15:23:11

Hallo,
für den markierten Bereich:

Sub aaaa()
  Dim bytIn() As Byte, strOut As String, i As Integer, rngC As Range
  For Each rngC In Selection
    strOut = ""
    bytIn = rngC.Text
    For i = 0 To UBound(bytIn) Step 2
      strOut = strOut & String(5, " ") & Chr(bytIn(i))
    Next
    rngC = Mid(strOut, 6)
  Next
End Sub

Gruß
Rudi