| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Phone Number Excel 2003 | Excel Discussion (Misc queries) | |||
| How do I delete dashes from a phone number xxx-xxx-xxxx in Excel? | Excel Worksheet Functions | |||
| How to format a phone number in Excel | Excel Discussion (Misc queries) | |||
| Keep phone number fomatting from excel to print on WORD labels | Excel Discussion (Misc queries) | |||
| Alpha Phrase To Phone Number Calculator in excel | Excel Discussion (Misc queries) | |||
Linear Mode
