Extracting specific text

[YasserKhalil]

I want to extract a specific text from a string
for example if cell A1 has the value
No way of escaping fate!Do you know that?

I want to extract from the right the string "Do you know that?"
I mean to extract the string depending on exclamation mark !

 

[njimack]

=right(a1,len(a1)-find("!",a1))

 

[VoG]

Try

=RIGHT(A1,LEN(A1)-FIND("!",A1))

 

[Scott Huish]

=replace(a1,1,find("!",a1&"!"),"")

 

[Rick Rothstein]

I want to extract a specific text from a string
for example if cell A1 has the value
No way of escaping fate!Do you know that?

I want to extract from the right the string "Do you know that?"
I mean to extract the string depending on exclamation mark !

Unfortunately, there are two ways to interpret what you want from the way you used the word "extract".

Does this do what you want...

=MID(A1,FIND("!",A1)+1,LEN(A1))

If it doesn't, then what about this...

=LEFT(A1,FIND("!",A1))

 

[YasserKhalil]

Good but some cells have two exclamation marks and I want just extract after the last one !!!

 

[njimack]

Good but some cells have two exclamation marks and I want just extract after the last one !!!

Then you should have said that in your first post. Try this:

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"!","~",LEN(A1)-LEN(SUBSTITUTE(A1,"!","")))))

 

[VoG]

Try

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"!",""))=1,RIGHT(A1,LEN(A1)-FIND("!",A1)),RIGHT(A1,LEN(A1)-FIND("!",SUBSTITUTE(A1,"!","~",1))))

 

[Rick Rothstein]

Good but some cells have two exclamation marks and I want just extract after the last one !!!

It would have helped if you had mentioned that originally. Does this formula do what you want...

=TRIM(RIGHT(SUBSTITUTE(A1,"!",REPT(" ",99)),99))

 

[Scott Huish]

Function RemoveText(r As String) As String
RemoveText = Mid(r, InStrRev(r, "!") + 1)
End Function

or if you want it blank if there is no exclamation point:

Function RemoveText(r As String) As String
Dim s As Long
s = InStrRev(r, "!")
If s Then RemoveText = Mid(r, s + 1)
End Function