Breaking up a string into groups of integers

[business_analyst]

Hello All,

So, I have a bunch of phone numbers stored in excel. These numbers are listed in the following format: (555) 555-5555

The userform I am creating will pull this number as is (a string) into a single textbox. This I got to work fine.

However, to make it cleaner, users are requesting that I have 3 seperate textboxes. 1 for the zip, 1 for the first three digits of the number, and 1 for the last four digits. No other characters, just the integers returned to each of the 3 textboxes.

Is there a feasible way for me to break up the string phone number from the worksheet into these 3 groups of integers, using VBA? Any help or opinions would be greatly appreciated.

 

[SamTYler]

Here's a snippet you can modify

Function ColorValue(ByVal RGBString As String) As Long
' Accepts any string containing three seperate numeric values.
' Ex: 1,2,3; 1 2 3; RGB(1, 2, 3); R1, G2, B3; RGB(1 - 2 - 3)
' Returns the Color Value of RGBString as a Long
' irrespective of r,g,b value.


Dim Red As Integer   'These hold the Calculated R, G, and B values.
Dim Green As Integer
Dim Blue As Integer


'********Get Red Value*********
'Strip leading nonnumeric characters
 While Not IsNumeric(Left(RGBString, 1))
   RGBString = Right(RGBString, Len(RGBString) - 1)
 Wend
'Strip Trailing nonnumeric characters
 While Not IsNumeric(Right(RGBString, 1))
   RGBString = Left(RGBString, Len(RGBString) - 1)
 Wend

'Check if RGBString is as long as shortest possible string ("n n n")
' where "n" is any number from 0 to 256
If Len(RGBString) < 5 Then GoTo OOPS

'Set Red = leading numeric characters
 While IsNumeric(Left(RGBString, 1))
   Red = Red & Left(RGBString, 1)
   RGBString = Right(RGBString, Len(RGBString) - 1)
 Wend

'Error Check
If Red > 256 Then GoTo OOPS
If Len(RGBString) < 3 Then GoTo OOPS 'Is not at least ("n n")

'*********Get Blue Value*************
 While Not IsNumeric(Left(RGBString, 1))
   RGBString = Right(RGBString, Len(RGBString) - 1)
 Wend

</PRE>