Hi again,

Am Tue, 11 Jun 2013 16:10:28 +0200 schrieb Claus Busch:

If you write "Hello" in D1, then in another cell:
=DialWord(D1)
The result will be:
44-33-555-555-666

if you want to write a sentence in D1, then change the code:

Function DialWord(myRange As Range) As String
Dim i As Integer
Dim myStr As String
Dim myStr1 As String
Dim c As Range

For i = 1 To Len(Trim(myRange))
If Mid(Trim(myRange), i, 1) = " " Then
myStr1 = myStr1 & "|" & Right(myStr, Len(myStr) - 1)
myStr = ""
End If
Set c = Range("A1:A26").Find(Mid(Trim(myRange), i, 1), _
LookIn:=xlValues)
If Not c Is Nothing Then
myStr = myStr & "-" & c.Offset(0, 1)
End If
Next
If myStr1 = "" Then
DialWord = Right(myStr, Len(myStr) - 1)
Else
DialWord = Right(myStr1, Len(myStr1) - 1) & "|" _
& Right(myStr, Len(myStr) - 1)
End If
End Function


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2