Option Explicit
Sub TestbyFormula()
Dim c As Range, cx As Range, str As String
Application.TransitionNavigKeys = False
Set c = Cells(Rows.Count, 1)
For Each cx In Selection.Cells
With cx
If .Value <> "" And .PrefixCharacter <> "" Then
c.Formula = Replace("=CLEAN(xx)", "xx", cx.Address(0, 0))
str = CStr(c.Value)
str = Trim(str)
.Clear
.NumberFormat = "@"
.Value = str
If .PrefixCharacter <> "" Then MsgBox "hat Präfix"
End If
End With
Next cx
c.Clear
End Sub
Sub TestbyFunction()
Dim cx As Range, str As String
Application.TransitionNavigKeys = False
For Each cx In Selection.Cells
With cx
If .Value <> "" And .PrefixCharacter <> "" Then
str = WorksheetFunction.Clean(.Text)
str = Trim(str)
.Clear
.NumberFormat = "@"
.Value = str
Debug.Print .Address(0, 0)
End If
End With
Next cx
End Sub