On Tue, 11 Jun 2013 16:48:01 +0200, Claus Busch
wrote:

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

Thanks for help Claus. I've entered the Function and added 0-9 to
include them in the range data and have changed the code defining the
Range("A1:A37"). I'm getting the #value when a space is detected in
D1. I have it defined in the range A1-A37 (A-Z,1-0,space). IOW, I'm
not seeing the "-" when a space is entered separating words. Thanks
again for your help.