Thread: [Solved] Using wildcards with SEARCH
View Single Post
 
Old 10-07-2020, 12:48 PM
Purfleet Purfleet is offline Windows 10 Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Right, having checked the formula it was never going to work as it replaces all dashes.

My fault, but does show the value of adding in example data.

So the next attempt, a UDF


Code:
Dim DashNumber As Integer

'Countnumber of dashes for the loop
CountDash = Len(SelectedText) - Len(Replace(SelectedText, Delimit, ""))

'Set output text to start
OutputText = SelectedText

DashNumber = 0

'Loop through all dashes
For I = 1 To CountDash

'Find Dash number in string
    DashNumber = InStr(DashNumber + 1, OutputText, Delimit)
    
    'Check char before dash
    TextBefore = Mid(OutputText, DashNumber - 1, 1)
    'Check char after dash
    TextAfter = Mid(OutputText, DashNumber + 1, 1)
        
        'check if before and after chars are equal to space
        If TextBefore <> " " And TextAfter <> "" Then
            'set outputext as the first chars and then the new text with 1 less dash
            OutputText = Mid(OutputText, 1, DashNumber - 1) & Replace(OutputText, Delimit, "", Start:=DashNumber, Count:=1)

        End If
    Next I
    
RemoveDash = OutputText

End Function

Last edited by Purfleet; 10-07-2020 at 12:48 PM. Reason: typo
Reply With Quote