Extracting specific text

N

njimack

Well-known Member

Joined

Jun 17, 2005

Messages

7,772

• Aug 19, 2011

• #2

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

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 19, 2011

• #3

Try

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

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,637

Office Version

1. 365

Platform

1. Windows

• Aug 19, 2011

• #4

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

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,527

Office Version

1. 365

Platform

1. Windows

• Aug 19, 2011

• #5

YasserKhalil said:

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 !

Click to expand...

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))

 

Upvote 0

Y

YasserKhalil

Well-known Member

Joined

Jun 24, 2010

Messages

852

• Aug 19, 2011

• #6

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

 

Upvote 0

N

njimack

Well-known Member

Joined

Jun 17, 2005

Messages

7,772

• Aug 19, 2011

• #7

YasserKhalil said:

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

Click to expand...

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

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

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 19, 2011

• #8

Try

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

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,527

Office Version

1. 365

Platform

1. Windows

• Aug 19, 2011

• #9

YasserKhalil said:

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

Click to expand...

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

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

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,637

Office Version

1. 365

Platform

1. Windows

• Aug 19, 2011

• #10

Code:

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:

Code:

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

 

Last edited: Aug 19, 2011

Upvote 0