OpenOffice.info

Code: Alles auswählen

Option Explicit

Function Vanity(ByVal sName As String) As String
   Dim aNums, i As Long, sChar As String, sResult As String
   
   aNums = Array(2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 7, 8, 8, 8, 9, 9, 9, 9)
   
   For i = 1 To Len(sName)
      sChar = LCase(Mid(sName, i, 1))
      If sChar Like "[a-z]" Then
         sResult = sResult & aNums(Asc(sChar) - Asc("a"))
      Else
         sResult = sResult & "-"
      End If
   Next
   Vanity = sResult
End Function

Sub TestIt()
   Print Vanity("0ABCdefghijklmnopqrstuvwXYZ1")
End Sub

Code: Alles auswählen

Function Vanity(x)
  Vanity = ""
  For i = 1 To 3
    tmp = Left(x, i)
    tmp2 = Right(tmp, 1)
    Select Case UCASE(tmp2)
      Case "A", "B", "C"
        tmp3 = 2
      Case "D", "E", "F"
        tmp3 = 3
      Case "G", "H", "I"
        tmp3 = 4
      Case "J", "K", "L"
        tmp3 = 5
      Case "M", "N", "O"
        tmp3 = 6
      Case "P", "Q", "R", "S"
        tmp3 = 7
      Case "T", "U", "V"
        tmp3 = 8
      Case "W", "X", "Y", "Z"
        tmp3 = 9
    End Select
    Vanity = Vanity & tmp3
  Next i
  'Vanity = VAL(Vanity) 'Falls ZahlenWERT gewünscht
End Function

Code: Alles auswählen

=WENN(UND(CODE(GROSS(A1))>64;CODE(GROSS(A1))<80);GANZZAHL((CODE(GROSS(A1))-59)/3);WENN(UND(CODE(GROSS(A1))>79;CODE(GROSS(A1))<84);7;WENN(UND(CODE(GROSS(A1))>83;CODE(GROSS(A1))<87);8;WENN(UND(CODE(GROSS(A1))>86;CODE(GROSS(A1))<91);9;"?"))))

Code: Alles auswählen

=SVERWEIS(GROSS(TEIL(A1;1;1));'Vanity-Hilfstabelle'.A1:B26;2)&SVERWEIS(GROSS(TEIL(A1;2;1));'Vanity-Hilfstabelle'.A1:B26;2)&SVERWEIS(GROSS(TEIL(A1;3;1));'Vanity-Hilfstabelle'.A1:B26;2)