Excel VB form to break apart a phone number?
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! |
Excel VB form to break apart a phone number?
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 wrote in message oups.com... 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! |
Excel VB form to break apart a phone number?
On Feb 8, 2:44 am, "NickHK" wrote:
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 wrote in message oups.com... 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!- Hide quoted text - - Show quoted text - Thanks Nick! It worked perfectly! -Todd |
Excel VB form to break apart a phone number?
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) wrote in message oups.com... On Feb 8, 2:44 am, "NickHK" wrote: 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 wrote in message oups.com... 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!- Hide quoted text - - Show quoted text - Thanks Nick! It worked perfectly! -Todd |
All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com