Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-25-2019, 12:25 PM
KBeMe KBeMe is offline Extracting Numbers from String Windows 10 Extracting Numbers from String Office 2016
Novice
Extracting Numbers from String
 
Join Date: Sep 2019
Posts: 1
KBeMe is on a distinguished road
Default 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().
Reply With Quote
  #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,101
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 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
Reply

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
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 05:36 AM.


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