Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 09-25-2019, 08:51 PM
gmayor's Avatar
gmayor gmayor is offline Extracting Numbers from String Windows 10 Extracting Numbers from String Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,144
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
You can ensure that only numbers or a hyphen are entered in the field in the first place by adding the following code

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
Reply With Quote
 



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
Extracting Numbers from String 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 Numbers from String Extracting a phone number from a string that contains text and numbers. hommi16 Excel 2 06-05-2013 09:19 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft