how to remove certain set of number and letters

[Pookiemeister]

The following code removes any special characters from Inputbox answer.

Dim answ as Variant
Do
    answ = InputBox("Please enter the amount")

'cancel pressed
    If StrPtr(answ) = 0 Then Exit Sub
     answ = UCase(answ)
     If answ = "" Or IsNumeric(answ) Then
        MsgBox "Answer must not be left blank and must contain a letter." & vbCrLf & vbCrLf & "Your answer: " & answ, vbCritical, "Invalid Entry"
    Else
        Dim special_Characters As String
        Dim i As Long
      
        special_Characters = """<,.>?/:;{[}]|\+=_-)(*&^%$#@!'"
      
        For i = 1 To Len(special_Characters)
            answ = Replace(answ, Mid(special_Characters, i, 1), "")
            Debug.Print answ
          
            If i = Len(special_Characters) Then Exit Do
        Next
      
    End If
Loop

So how can I remove certain words that might be entered or combination of letters and numbers from the Inputbox string. Here's an example of what might be entered.
SA Allergy Medicine 15mg 10ct Bottle
So using the bold example entry I would only want to keep Allergy Medicine 15mg from that string and eliminate the rest.

Potential problems/issues to consider

• The mg value can change but will always be in this format ###mg or ##mg (No spaces between the number and the letters of the unit of measure(mg) in this example)
• The ct value can change but will always be in this format ###ct or ##ct (No spaces between the number and the letters of the unit of measure(ct) in this example)
• Remove words from text like "SA" or "Bottle" in this example and not just the letters

Is this even possible? If so how?
Thank You for any help offered.

 

[CSmith]

Split String into 'word' array elements and compare with either another array or a combo of regex (for XXXct items) and unwanted words array.

 

[sandy666]

Is this even possible? If so how?
Thank You for any help offered.

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"raw", each Text.BetweenDelimiters(_, " ", " ", 0, 2), type text}})
in
    ETBD

 

[JLGWhiz]

Since there is no control over what a user might enter into the input box, it would be very difficult to construct code which can accomodate all the variations of leading and trailing characters , or for that matter to determine where the significant characters to keep would begin.. For code to detect characters to delete and characters to keep, there must be either a distinguishable and consistent pattern of characters or a set criteria that can be compared against. Unfortunately, VBA does not have the AI capabilities to learn how to distinguish standard descriptive phrases which are embedded in other text simply by the spelling of the text. Those phrases would have to be listed somewhere so that a comparison could be made against text entered into an input box by a user. But if that list could be made, then it would be more efficient to use a ComboBox listing those items and let the user select from that rather than use the input box.