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

Does the RemoveUnderscores subroutine remove the underscores from
ScreenTextB? If not, then your problem is you are testing the length of
ScreenTextB and if it doesn't change, then you will be in the wrong part of
your If..Then test.

--
Rick (MVP - Excel)


"TomP" wrote in message
...
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

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

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

On Wed, 7 Jan 2009 13:55:37 -0800, TomP wrote:

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,

The solution I provided would not only give you the correct format, but would
also return nothing if CPPHONE = "".

I thought that was what you wanted.

It was an alternative since you have not told us what is contained in
ScreenTextB at the time you enter this IF statement.

Since the ELSE statement is not being executed, ScreenTextB must contain 12
characters at that time.
--ron

On Wed, 07 Jan 2009 17:10:40 -0500, Ron Rosenfeld
wrote:

On Wed, 7 Jan 2009 13:55:37 -0800, TomP wrote:

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,

The solution I provided would not only give you the correct format, but would
also return nothing if CPPHONE = "".

I thought that was what you wanted.

It was an alternative since you have not told us what is contained in
ScreenTextB at the time you enter this IF statement.

Since the ELSE statement is not being executed, ScreenTextB must contain 12
characters at that time.
--ron


Also, the code snippet you provide does not show us how you manage to get
CPPHONE into a cell.
--ron

I went ahead and removed the function "RemoveUnderscores" which helped a
little. The scenario I'm giving is if there are no phone numbers to grab,
the ELSE statement should provide an empty cell, but now it shows "( )-" in
the cell.

Thank you!

Dim CPPHONE As String

ScreenTextB = CurrentScreenObject.getstring(18, 55, 12)
' RemoveUnderscores
If Len(ScreenTextB) = 12 Then
CPPHONE = "(" & Left(ScreenTextB, 3) & ") " &
Mid(ScreenTextB, 5, 3) & "-" & Right(ScreenTextB, 4)
Else
CPPHONE = ""

End If

Range("I32") = CPPHONE

"Ron Rosenfeld" wrote:

On Wed, 07 Jan 2009 17:10:40 -0500, Ron Rosenfeld
wrote:

On Wed, 7 Jan 2009 13:55:37 -0800, TomP wrote:

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,

The solution I provided would not only give you the correct format, but would
also return nothing if CPPHONE = "".

I thought that was what you wanted.

It was an alternative since you have not told us what is contained in
ScreenTextB at the time you enter this IF statement.

Since the ELSE statement is not being executed, ScreenTextB must contain 12
characters at that time.
--ron


Also, the code snippet you provide does not show us how you manage to get
CPPHONE into a cell.
--ron