#1
|
|||
|
|||
Extracting Numbers from String
I have a User Form where the user enters information that populates a document using Private Sub cmdOK_Click(). From one of the Text Fields that the user inputs data I need to extract the numbers that are entered in the format of ##-#### stripping any other text that is entered and I then I want to be able to do a
ActiveDocument.Bookmarks("CaseNum1").Select Selection.TypeText to place the number minus any other characters into the document. All documentation I can find to do something like this is for Excel and does not work in Private Sub cmdOK_Click(). |
#2
|
||||
|
||||
The following will do that. It assumes TextBox1 is the name of the textbox in question and CommandBuitton1 is the name of the button that exits the userform.
Code:
Option Explicit Private Sub CommandButton1_Click() Dim sNum As String sNum = Get_Num(TextBox1.Text) If Len(TextBox1.Text) < 7 Or sNum = "" Then MsgBox "The data in the text box is incorrect" TextBox1.SetFocus GoTo lbl_Exit End If FillBM "CaseNum1", sNum Unload Me lbl_Exit: Exit Sub End Sub Private Function Get_Num(s As String) As String Dim i As Long For i = 1 To Len(s) - 6 If Mid(s, i, 7) Like "##-####" Then Get_Num = Mid(s, i, 7) Exit For End If Next i End Function Private Sub FillBM(strbmName As String, strValue As String) 'Graham Mayor - http://www.gmayor.com Dim oRng As Range With ActiveDocument On Error GoTo lbl_Exit Set oRng = .Bookmarks(strbmName).Range oRng.Text = strValue oRng.Bookmarks.Add strbmName End With lbl_Exit: Set oRng = Nothing Exit Sub End Sub Code:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim bTest As Boolean bTest = IsAllowed(CStr(KeyAscii)) If bTest = False Then Beep KeyAscii = 0 End If lbl_Exit: Exit Sub End Sub Private Function IsAllowed(ByVal i As String) As Boolean Select Case Val(i) 'Checking to see if inside valid Ascii range for integers Case 45, 48 To 57 IsAllowed = True Case Else IsAllowed = False End Select lbl_Exit: Exit Function End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help to extracting text from text string | Haha88 | Excel | 3 | 02-13-2019 05:47 AM |
Help with Formula to extracting text from text string | Haha88 | Excel | 7 | 01-13-2019 01:33 AM |
Help with Formula to extracting text from text string | Haha88 | Excel | 9 | 02-05-2018 01:04 AM |
How to extract only numbers from a STRING? | Learner7 | Excel | 3 | 07-02-2013 06:25 AM |
Extracting a phone number from a string that contains text and numbers. | hommi16 | Excel | 2 | 06-05-2013 09:19 PM |