Excel 2003. What VBA string function(s) can I use to accomplish the following:

I want to pass a string that may or may not contain a telephone number. I
want to check to see if it is indeed a telephone number. That is, I want to
check to see if the string contains only telephone type info (i.e.,
0123456789()- ) or conversely, I want to check if the string contains
non-telephone type info (i.e., alpha). How can I accomplish this? Thanks
for any help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Try this function:

Function PositionFirstNONNumberInString(strString As String) As Long

Dim i As Long
Dim btArray() As Byte

btArray = strString

For i = 0 To UBound(btArray) Step 2
If btArray(i) < 48 Or btArray(i) 57 Then
PositionFirstNONNumberInString = i \ 2 + 1
Exit Function
End If
Next

PositionFirstNONNumberInString = -1

End Function


RBS


"Chaplain Doug" wrote in message
...
Excel 2003. What VBA string function(s) can I use to accomplish the
following:

I want to pass a string that may or may not contain a telephone number. I
want to check to see if it is indeed a telephone number. That is, I want
to
check to see if the string contains only telephone type info (i.e.,
0123456789()- ) or conversely, I want to check if the string contains
non-telephone type info (i.e., alpha). How can I accomplish this? Thanks
for any help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Hi,

I think you must design a macro that loops through the string and checks
which characters can be converted to numbers. For example,

Public Function CheckString(str As String) As Boolean
Dim i As Long
Dim length As Long

CheckString = False
If VarType(str) = vbString Then
i = 1
length = Len(str)
Do While IsNumeric(Mid(str, i)) = True
i = i + 1
If length < i Then
CheckString = True
Exit Do
End If
Loop
Else
Call MsgBox("Enter a string") 'Add a better validation process lol!
End If

End Function

Just to give you some ideas,

--
Carlos


"Chaplain Doug" wrote:

Excel 2003. What VBA string function(s) can I use to accomplish the following:

I want to pass a string that may or may not contain a telephone number. I
want to check to see if it is indeed a telephone number. That is, I want to
check to see if the string contains only telephone type info (i.e.,
0123456789()- ) or conversely, I want to check if the string contains
non-telephone type info (i.e., alpha). How can I accomplish this? Thanks
for any help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

On Thu, 9 Nov 2006 10:11:02 -0800, Chaplain Doug
wrote:

Excel 2003. What VBA string function(s) can I use to accomplish the following:

I want to pass a string that may or may not contain a telephone number. I
want to check to see if it is indeed a telephone number. That is, I want to
check to see if the string contains only telephone type info (i.e.,
0123456789()- ) or conversely, I want to check if the string contains
non-telephone type info (i.e., alpha). How can I accomplish this? Thanks
for any help.

You can either check things they way you describe. You would loop through each
character in the text string and test it:

======================
Const sAllow As String = "[- 0-9()]"
Dim i As Long
Dim sTest As String

For i = 1 To Len(sTest)
If Not Mid(sTest, i, 1) Like sAllow Then
TelNo = False
Exit (function or sub)
End If
Next i

TelNo = True
========================

Or you could construct a more complex test to better define exactly what a
valid phone number might consist of.

For example, some valid phone numbers might be:

123-4567
123 4567
1234567
1-123-4567
1-603-123-4567
1(123) 456-7890

and maybe even

1 123 456 7890 x 1234

But something like

1234 would not be a valid phone number

And if you are not in the US, there may be even more variations.
--ron