I'll try to explain this a little better. The phone number format works
fine. The problem I'm having is with the ELSE Statement.

I would think that with the ELSE statement CPPHONE = "" would return a Null
value or an empty cell, but what I get are brackets, underscores and hyphen.
The cell reference is formatted to General text. All I would like to get is
an empty cell.

Thank you,

"Ron Rosenfeld" wrote:

On Wed, 7 Jan 2009 11:03:01 -0800, TomP wrote:

I'm gathering numbers (text) from a screen and want to format it in telephone
format before I place it in the spreadsheet. First, I need to remove the
underscores and have that in place which works. "RemoveUnderscores" is a
subroutine that gets rid of the underscores.

The problem is when there is no phone number to grab, I get and left/right
brackets along with the underscores. I would think that

Else
CPPHONE = ""

would work.

ScreenTextB is actually pulling the object for me and it works fine.

Dim CPPHONE as String

RemoveUnderscores
If Len(ScreenTextB) = 12 Then
CPPHONE = "(" & Left(ScreenTextB, 3) & ") " &
Mid(ScreenTextB, 5, 3) & "-" & Right(ScreenTextB, 4)
Else
CPPHONE = ""

End If

Thank you for your help!

Tom

Without all your code it's hard to tell. Have to make some assumptions

I will assume that ScreenTextB will either be a null string or an underscore
delimited phone number.

So it could be:

<blank
123_4567
123_456_7890

That being the case, and wanting to format it as

(123) 456-7890

I would use:

=================
Sub TestPhone()
PhoneString ("123_456_7890")
End Sub
==================
Sub PhoneString(ScreenTextB)
Dim CPPHONE As String
CPPHONE = Replace(ScreenTextB, "_", "")
CPPHONE = Application.WorksheetFunction.Text _
(CPPHONE, "[<=9999999]###-####;(###) ###-####")
Debug.Print CPPHONE
End Sub
=================

--ron