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

On Thu, 8 Jan 2009 05:34:36 -0800, TomP wrote:

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

The problem with your routine is that, as written, ScreenTextB will ALWAYS be
12 characters long, whether or not there is a phone number in the string.

I think what you want is for the value you are operating on to be a phone
number.

One way to handle the problem would be to remove all of the non-numeric
characters from ScreenTextB before processing it.

I don't know what the "spacers" are in CurrentScreenObject, so I chose to
include only those values which are numeric in the routine below. But, if you
know for sure, that the only non-numeric characters might be <underscore or
<space, or <nbsp, for example, you could probably write a simpler routine.

But try this:

=======================
Sub PhoneString(ScreenTextB)
Dim CPPHONE As String
Dim sTemp As String
Dim i As Long
For i = 1 To Len(ScreenTextB)
sTemp = Mid(ScreenTextB, i, 1)
If sTemp Like "#" Then
CPPHONE = CPPHONE & sTemp
End If
Next i
CPPHONE = Application.WorksheetFunction.Text _
(CPPHONE, "[<=9999999]###-####;(###) ###-####")
Debug.Print CPPHONE
End Sub
==========================
--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

The problem with your routine is that, as written, ScreenTextB will ALWAYS
be
12 characters long, whether or not there is a phone number in the string.

I think what you want is for the value you are operating on to be a phone
number.

One way to handle the problem would be to remove all of the non-numeric
characters from ScreenTextB before processing it.

I don't know what the "spacers" are in CurrentScreenObject, so I chose to
include only those values which are numeric in the routine below. But, if
you
know for sure, that the only non-numeric characters might be <underscore
or
<space, or <nbsp, for example, you could probably write a simpler
routine.

But try this:

=======================
Sub PhoneString(ScreenTextB)
Dim CPPHONE As String
Dim sTemp As String
Dim i As Long
For i = 1 To Len(ScreenTextB)
sTemp = Mid(ScreenTextB, i, 1)
If sTemp Like "#" Then
CPPHONE = CPPHONE & sTemp
End If
Next i
CPPHONE = Application.WorksheetFunction.Text _
(CPPHONE, "[<=9999999]###-####;(###) ###-####")
Debug.Print CPPHONE
End Sub
==========================

The OP may want to consider this slightly shorter alternative to the
next-to-the-last statement from your code which uses the native VB functions
instead...

CPPHONE = Format(CPPHONE, Right("(###) ###-####", _
14 + 7 * (Len(CPPHONE) = 7)))

--
Rick (MVP - Excel)

This is what I did and it worked great! There is so much to learn and I
appreciate your feedback!

Tom

Dim sTemp As String
Dim i As Long
For i = 1 To Len(ScreenTextb)
sTemp = Mid(ScreenTextb, i, 1)
If sTemp Like "#" Then
CPPHONE = CPPHONE & sTemp
End If

Next i
CPPHONE = Format(CPPHONE, Right("(###) ###-####", 14 + 7 *
(Len(CPPHONE) = 7)))

"Rick Rothstein" wrote:

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

The problem with your routine is that, as written, ScreenTextB will ALWAYS
be
12 characters long, whether or not there is a phone number in the string.

I think what you want is for the value you are operating on to be a phone
number.

One way to handle the problem would be to remove all of the non-numeric
characters from ScreenTextB before processing it.

I don't know what the "spacers" are in CurrentScreenObject, so I chose to
include only those values which are numeric in the routine below. But, if
you
know for sure, that the only non-numeric characters might be <underscore
or
<space, or <nbsp, for example, you could probably write a simpler
routine.

But try this:

=======================
Sub PhoneString(ScreenTextB)
Dim CPPHONE As String
Dim sTemp As String
Dim i As Long
For i = 1 To Len(ScreenTextB)
sTemp = Mid(ScreenTextB, i, 1)
If sTemp Like "#" Then
CPPHONE = CPPHONE & sTemp
End If
Next i
CPPHONE = Application.WorksheetFunction.Text _
(CPPHONE, "[<=9999999]###-####;(###) ###-####")
Debug.Print CPPHONE
End Sub
==========================

The OP may want to consider this slightly shorter alternative to the
next-to-the-last statement from your code which uses the native VB functions
instead...

CPPHONE = Format(CPPHONE, Right("(###) ###-####", _
14 + 7 * (Len(CPPHONE) = 7)))

--
Rick (MVP - Excel)

On Thu, 8 Jan 2009 10:38:29 -0500, "Rick Rothstein"
wrote:

The OP may want to consider this slightly shorter alternative to the
next-to-the-last statement from your code which uses the native VB functions
instead...

CPPHONE = Format(CPPHONE, Right("(###) ###-####", _
14 + 7 * (Len(CPPHONE) = 7)))

That'll work also. It requires three VBA functions. My routine does not check
for "illegal" phone numbers (e.g. not seven or 10 digits) and the two
approaches give different answers for illegal phone numbers.
--ron

On Thu, 8 Jan 2009 09:10:01 -0800, TomP wrote:

This is what I did and it worked great! There is so much to learn and I
appreciate your feedback!

Tom

Dim sTemp As String
Dim i As Long
For i = 1 To Len(ScreenTextb)
sTemp = Mid(ScreenTextb, i, 1)
If sTemp Like "#" Then
CPPHONE = CPPHONE & sTemp
End If

Next i
CPPHONE = Format(CPPHONE, Right("(###) ###-####", 14 + 7 *
(Len(CPPHONE) = 7)))

Glad you got it working. Thanks for the feedback.
--ron

I wasn't sure about the time advantage or disadvantage of using 3 built-in
VB functions (the Len function is extremely quick though, so maybe we're
talking about 2) as compared to one call to a worksheet function... I
observed a noticeable delay the first time I executed the worksheet function
call (subsequent calls to it went much faster though) whereas I saw no delay
with the VB only function calls. I guess that is not very scientific
though.<g

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Thu, 8 Jan 2009 10:38:29 -0500, "Rick Rothstein"
wrote:

The OP may want to consider this slightly shorter alternative to the
next-to-the-last statement from your code which uses the native VB
functions
instead...

CPPHONE = Format(CPPHONE, Right("(###) ###-####", _
14 + 7 * (Len(CPPHONE) = 7)))

That'll work also. It requires three VBA functions. My routine does not
check
for "illegal" phone numbers (e.g. not seven or 10 digits) and the two
approaches give different answers for illegal phone numbers.
--ron

On Thu, 8 Jan 2009 14:15:43 -0500, "Rick Rothstein"
wrote:

I wasn't sure about the time advantage or disadvantage of using 3 built-in
VB functions (the Len function is extremely quick though, so maybe we're
talking about 2) as compared to one call to a worksheet function... I
observed a noticeable delay the first time I executed the worksheet function
call (subsequent calls to it went much faster though) whereas I saw no delay
with the VB only function calls. I guess that is not very scientific
though.<g

I've got a timer function set up someplace, but never got a round tuit for
testing <g.
--ron