Excel VB form to break apart a phone number?

[Shaka215]

Hey,

I am trying to figure out how to do the following...

Spreadsheet value is 555-111-2222

I've got 3 text boxes and I need that number broken down into each
text box...

Textbox1.value = 555
Textbox2.value = 111
Textbox3.value = 2222

I got textbox1 to work correctly using this code...

ThisTextbox = TextBox1.Value
charnum = InStr(1, ThisTextbox, "-")
If charnum <> 0 Then
TextBox1.Value = Left(ThisTextbox, charnum - 1)
End If

But I can't figure out how to get it work with the rest of the
numbers... any ideas? I'm thinking LEN & LEFT can do it but I can't
figure out the context. Any help is greatly appreciated!

 

[NickHK]

Something like this ?

Private Sub CommandButton1_Click()
Dim Parts() As String
Dim TelNum As String

Const SAMPLENUMBER As String = "555-111-2222"

On Error GoTo Handler

TelNum = SAMPLENUMBER

Parts = Split(TelNum, "-")
Textbox1.Text = Trim(Parts(0))
Textbox2.Text = Trim(Parts(1))
Textbox3.Text = Trim(Parts(2))

Exit Sub
Handler:

MsgBox "Telephone number <" & TelNum & "> is not in the required format."

End Sub

NickHK

 

[Shaka215]

Something like this ?

Private Sub CommandButton1_Click()
Dim Parts() As String
Dim TelNum As String

Const SAMPLENUMBER As String = "555-111-2222"

On Error GoTo Handler

TelNum = SAMPLENUMBER

Parts = Split(TelNum, "-")
Textbox1.Text = Trim(Parts(0))
Textbox2.Text = Trim(Parts(1))
Textbox3.Text = Trim(Parts(2))

Exit Sub
Handler:

MsgBox "Telephone number <" & TelNum & "> is not in the required format."

End Sub

NickHK













- Show quoted text -

Thanks Nick! It worked perfectly!

-Todd

 

[Chip Pearson]

While Nick's solution works great and does just what you need, I have an
entire page on my web site that describes parsing out elements of a phone
number. It supports parsing out data for 7 different phone number formats.
See http://www.cpearson.com/excel/PhoneNum.htm for example code. You might
find it useful in the future if your encounter differently formatted phone
numbers.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)