Office: Innerhalb einer Zelle suchen und ersetzen

Hallo Spezialisten.

Ich habe eine Liste mit verschiedenen Einträgen (einzelne Wörter und/oder mehrere Wörter und dabei können noch ein bzw. mehrstellige Zahlen vorkommen, muss aber nicht).

Nun möchte ich die Zahlen folgendermaßen ersetzen
-die erste Zahl in der Zelle in &0
-die zweite in &1
-die dritte in &2 usw.

und dabei können die Zahlen ein- aber auch mehrstellig sein.

Z.B Zelle A1

xyz 12 abc 4 efg 345
-->
xyz &0 abc &1 efg &2

Und dieses Schema soll auf jede Zelle pro Zeile angewand werden.

Hoffe meine Problem ist deutlich geworden, und mir kann irgend jemand helfen..

Vielen Dank
Century147

 

Hallo!

Vielleicht mit einer benutzerdefinierten Funktion?

Code:

Public Function test(zelle As Range) As String
Dim A, i As Integer
A = Split(zelle, " ")
i = 1
Do Until i > UBound(A)
A(i) = "&" & (i - 1) / 2
i = i + 2
Loop
test = Join(A, " ")
End Function

Kopiere den Code in ein allgemeines Modul.
Der Aufruf in der Tabelle erfolgt dann mit =test(A1)

 

Hallo Century147,

falls jede der Zahlen innerhalb der Zeichenketten durch mindestens 1 Leerzeichen begrenzt ist, kann das Ganze durch 2 kleine VBA-Makros gelöst werden:

Code:

Option Explicit

Public Sub Ersetzer()
  Dim Zelle As Range
  For Each Zelle In Selection.Cells
    Zelle.Value = Zahl2Parameter(Zelle)
  Next Zelle
End Sub
Public Function Zahl2Parameter(AktZelle As Range) As String
  Dim S$(), I%
  Dim ParamZl%
  S$ = Split(AktZelle.Value, " ")
  ParamZl% = 0
  For I% = 0 To UBound(S$)
    If IsNumeric(S$(I%)) Then
      S$(I%) = "&" & ParamZl%
      ParamZl% = ParamZl% + 1
    End If
  Next I%
  Zahl2Parameter = Join(S$, " ")
End Function

1) Obigen VBA-Code mit der Maus selektieren, dann die Tasten Strg+C drücken, um ihn in die Zwischenablage zu kopieren; ins EXCEL-Arbeitsblatt wechseln, dort die Tasten Alt+F11 drücken, um in den VBA-Code-Editor zu gelangen, dort im Menü 'Einfügen - Modul' klicken, um ins sich öffnende Codemodul-Fenster hineinzuklicken,
die Taste Strg+V drücken, um den Code aus der Zwischenablage einzufügen.
2) Wiederum ins EXCEL-Arbeitsblatt wechseln.
3) Dort den Zellbereich mit den umzuwandelnden Zeichenketten markieren, die Makro-Ausführung aufrufen, im Dialoagfenster 'Makro' das Makro 'Ersetzer' auswählen, auf Schalter 'Ausführen' klicken - und die Zeichenketten des markierten Bereichs sind umgewandelt.
4) Man kann aber auch die Funktion 'Zahl2Parameter(Zelle)' verwenden. ZB. stehe in Zelle B16 eine umzuwandelnde Zeichenkette, gibt man in Zelle B17 die Formel ein: '=Zahl2Parameter(B16)', erhält man in B17 um die umgewandelte Zeichenkette.
5) Anmerkung: Der Aufruf der Makro-Ausführung erfolgt bis EXCEL2003 anders als in EXCEL2007 (bis EXCEL2003: Menü 'Extras - Makro - Makros...'; EXCEL2007: Multifunktionsleiste Tabulator 'Ansicht', Gruppe 'Makros', Schalter 'Makros', Eintrag 'Makros anzeigen')

Gruß Exl121150

 

Hallo Ihr beiden...

Ersteinmal super vielen Dank ..

Allerdings habe ich noch das Problem das nicht alle Zahlen durch leerzeichen getrennt sind und auch Kommazahlen auftreten.

Kann man da was machen?

Gruß
Century147

 

Hallo Century147,

ich habe die Funktion im Makro entsprechend modifziert, sodass sie jetzt Deinen Anforderungen entsprechen müsste. Alles Übrige so wie gestern beschrieben.

Code:

Option Explicit

Public Sub Ersetzer()
  Dim Zelle As Range
  For Each Zelle In Selection.Cells
    Zelle.Value = Zahl2Parameter(Zelle)
  Next Zelle
End Sub
Public Function Zahl2Parameter(AktWert As String) As String
  Dim Ch$, SS$, S$, Z$, ParamZl%
  Dim Br%, ok As Boolean
  SS$ = "": ParamZl% = 0
  Ch$ = GetNxtCh(AktWert)
  Do
    S$ = Txt(Ch$, Br%): SS$ = SS$ & S$
    Z$ = Zahl(Ch$, ok)
    If ok Then
      If Right(" " & SS$, 1) <> " " Then SS$ = SS$ & " "
      SS$ = SS$ & "&" & ParamZl% & " "
      ParamZl% = ParamZl% + 1
    Else
      SS$ = SS$ & Space(Br%) & Z$
    End If
    Z$ = ""
  Loop While Len(Ch$)
  Zahl2Parameter = SS$
End Function
Private Function Txt(Ch$, ByRef AnzBr%) As String
  Dim Z$, Zr$
  Z$ = ""
  Do Until Ch$ Like "[0-9,+-]"
    Z$ = Z$ & Ch$
    Ch$ = GetNxtCh(): If Len(Ch$) = 0 Then Exit Do
  Loop
  Z$ = LTrim$(Z$): Zr$ = RTrim$(Z$)
  AnzBr% = Len(Z$) - Len(Zr$)
  Txt$ = Zr$
End Function
Private Function Zahl(Ch$, ByRef ok As Boolean) As String
  Dim Z$, Vk$, Nk$
  Z$ = "": Vk$ = "": Nk$ = ""
  If Ch$ Like "[+-]" Then
    Z$ = Z$ & Ch$
    Ch$ = GetNxtCh()
  End If
  Vk$ = Ziffern(Ch$): Z$ = Z$ & Vk$
  If Ch$ = "," Then
    Ch$ = GetNxtCh()
    Nk$ = Ziffern(Ch$)
    Z$ = Z$ & "," & Nk$
  End If
  Zahl$ = Z$
  ok = Len(Vk$) + Len(Nk$)
End Function
Private Function Ziffern(Ch$) As String
  Dim Z$
  Z$ = ""
  Do While Ch$ Like "[0-9]"
    Z$ = Z$ & Ch$
    Ch$ = GetNxtCh()
  Loop
  Ziffern$ = Z$
End Function
Private Function GetNxtCh(Optional S$ = "") As String
  Static SS$, Ps%
  If Len(S$) Then SS$ = S$: Ps% = 1
  GetNxtCh = Mid$(SS$, Ps%, 1)
  Ps% = Ps% + 1
End Function

Zahlen: können aus "+", "-", "0".."9" und "," bestehen; ihre Syntax wird überprüft (Parser)

Gruß Exl121150

 

Hallo

leider bekomme ich eine Fehlermeldung beim Ausführen des Codes

Argumenttyp ByRef unverträglich :-(

Nochmal zum Verständnis

Zahlen kommen als
- aa 2 33 f
- 345V
-1,2D 1,5

vor

Vieln Dank für deine Mühe :-D :-D :-D

Gruß
Century147

 

Hallo Century147,

habe nocheinmal versucht, die Funktionen zu erstellen mit möglichst vielen expliziten Deklarationen. Bei mir gibt es keinen ByRef-Fehler.

Code:

Option Explicit
Public Sub Ersetzer()
  Dim Zelle As Range
  For Each Zelle In Selection.Cells
    Zelle.Value = Zahl2Parameter(Zelle)
  Next Zelle
End Sub
Public Function Zahl2Parameter(AktWert As String) As String
  Dim Ch$, SS$, S$, Z$, ParamZl%
  Dim Br%, ok As Boolean
  SS$ = "": ParamZl% = 0
  Ch$ = GetNxtCh(AktWert)
  Do
    S$ = Txt(Ch$, Br%): SS$ = SS$ & S$
    Z$ = Zahl(Ch$, ok)
    If ok Then
      If Right(" " & SS$, 1) <> " " Then SS$ = SS$ & " "
      SS$ = SS$ & "&" & ParamZl% & " "
      ParamZl% = ParamZl% + 1
    Else
      SS$ = SS$ & Space(Br%) & Z$
    End If
    Z$ = ""
  Loop While Len(Ch$)
  Zahl2Parameter = SS$
End Function
Private Function Txt(ByRef Ch$, ByRef AnzBr%) As String
  Dim Z$, Zr$
  Z$ = ""
  Do Until Ch$ Like "[0-9,+-]"
    Z$ = Z$ & Ch$
    Ch$ = GetNxtCh(): If Len(Ch$) = 0 Then Exit Do
  Loop
  Z$ = LTrim$(Z$): Zr$ = RTrim$(Z$)
  AnzBr% = Len(Z$) - Len(Zr$)
  Txt$ = Zr$
End Function
Private Function Zahl(ByRef Ch$, ByRef ok As Boolean) As String
  Dim Z$, Vk$, Nk$
  Z$ = "": Vk$ = "": Nk$ = ""
  If Ch$ Like "[+-]" Then
    Z$ = Z$ & Ch$
    Ch$ = GetNxtCh()
  End If
  Vk$ = Ziffern(Ch$): Z$ = Z$ & Vk$
  If Ch$ = "," Then
    Ch$ = GetNxtCh()
    Nk$ = Ziffern(Ch$)
    Z$ = Z$ & "," & Nk$
  End If
  Zahl$ = Z$
  ok = Len(Vk$) + Len(Nk$)
End Function
Private Function Ziffern(ByRef Ch$) As String
  Dim Z$
  Z$ = ""
  Do While Ch$ Like "[0-9]"
    Z$ = Z$ & Ch$
    Ch$ = GetNxtCh()
  Loop
  Ziffern$ = Z$
End Function
Private Function GetNxtCh(Optional S$ = "") As String
  Static SS$, Ps%
  If Len(S$) Then SS$ = S$: Ps% = 1
  GetNxtCh = Mid$(SS$, Ps%, 1)
  Ps% = Ps% + 1
End Function

Deine Beispiele:
Zahl2Parameter("aa 2 33 f") = "aa &0 &1 f"
Zahl2Parameter("345V ") = "&0 V"
Mein Beispiel:
Zahl2Parameter(" ö-,Ä -,345+45xyz 534,6abs")="ö-,Ä &0 &1 xyz &2 abs"

Ich habe einen Parser eingebaut, der überprüft, ob eine gültige Zahl vorliegt (darf aus "+", "-", "0".."9" und "," bestehen; kommt jedoch "+", "-" bzw. "," ohne Ziffernbegleitung vor, werden sie nicht als gültige Zahl, sondern nur als normaler Text erkannt.) Gültige Zahlen werden ' &Nr ' ersetzt.

Gruß Exl121150

 

Hallo so wie in deinem Beispiel klappt das Super, nur beim Selectieren und dann Makro ausführen kommt der Fehler.

Kommt deshalb da ein Range als String übergeben wird .

Nach einer Anpassung klappt auch das Super :-)

Code:

Option Explicit 
Public Sub Ersetzer() 
  Dim Zelle As Range 

Dim str as String


  For Each Zelle In Selection.Cells 
str = zelle.value 
    Zelle.Value = Zahl2Parameter(str) 
  Next Zelle 
End Sub 
Public Function Zahl2Parameter(AktWert As String) As String

Vielen Dank ohne deine Hilfe hätte ich das wohl nie hinbekommen...

Gruß
Century147