Remove specific text within a string

[YasserKhalil]

I have the range A1:A100
I want to remove specific text within the cell value from the exclamation mark to the end
Note that the cell value may contain more than exclamation mark .. I mean the last one
Example:
No way of escaping fate!It's your destiny!Is it right?
The string "!Is it right?" have to be removed
I want to do that by code

 

[Aladin Akyurek]

I have the range A1:A100
I want to remove specific text within the cell value from the exclamation mark to the end
Note that the cell value may contain more than exclamation mark .. I mean the last one
Example:
No way of escaping fate!It's your destiny!Is it right?
The string "!Is it right?" have to be removed
I want to do that by code

Something like...

B1, copy down:

=IF(ISNUMBER(FIND("!",A1)),
   LEFT(A1,FIND("#",SUBSTITUTE(A1,"!","#",
    LEN(A1)-LEN(SUBSTITUTE(A1,"!",""))))-1),A1)

 

[YasserKhalil]

Can it be done by code??

 

[Aladin Akyurek]

Can it be done by code??

Yes. Someone else surely will take it up...

 

[Jaafar Tribak]

Hi Yasser السلام عليكم


something like this maybe :

Function EndToInstrRev( _
ByVal StringCheck As String, _
ByVal StringMatch As String _
) As String

    If InStrRev(StringCheck, StringMatch) > 0 Then
        EndToInstrRev = Right(StringCheck, Len(StringCheck) - _
        InStrRev(StringCheck, StringMatch))
    End If

End Function

Sub Test()

    Dim s As String
    
    s = "No way of escaping fate!It's your destiny!Is it right?"
    
    MsgBox EndToInstrRev(s, "!")

End Sub

 

[Jaafar Tribak]

Sorry Yasser. I misunderstood your question- I think you mean this :

Function StartToInstrRev( _
ByVal StringCheck As String, _
ByVal StringMatch As String _
) As String

    If InStrRev(StringCheck, StringMatch) > 0 Then
        StartToInstrRev = Left(StringCheck, InStrRev(StringCheck, StringMatch) - 1)
    End If

End Function

Sub Test()

    Dim s As String
    
    s = "No way of escaping fate!It's your destiny!Is it right?"
    
    MsgBox StartToInstrRev(s, "!")

End Sub

 

[Scott Huish]

Duplicate post. See http://www.mrexcel.com/forum/showthread.php?t=573063

Please continue your post in the original thread.