Extracting text after symbol in excel

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 6, 2013

• #2

Try

=IF(ISNUMBER(FIND("!",A1)),RIGHT(A1,LEN(A1)-FIND("!",A1)),RIGHT(A1,LEN(A1)-FIND("$",A1)))

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 6, 2013

• #3

B2, copied down:

=IFERROR(REPLACE(A2,1,LOOKUP(9.99999999999999E+307,SEARCH({"!","$"},A2)),""),A2)

 

Upvote 0

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

5,835

Office Version

1. 365

Platform

1. MacOS

• Oct 6, 2013

• #4

LauraForiero,

WElcome to the forum.

One way.......

Excel 2007
	A	B
1	Brouillet,Amy Anne!Miss	Miss
2	Dupuis,Jean Paul$Monsieur	Monsieur
3	Ethier,Guy!Mr	Mr
4	Fu,Guo Qing$Prince	Prince
5	Gowan,Louise Marie$Mademoiselle	Mademoiselle
6	McGill,James Patrick!Dr	Dr
7	Robinson,Jacques$Monsieur	Monsieur
8	Vinet,Jean Pierre!Mr	Mr
9	Voigt,Claire Antoinette$Madame	Madame
10	Wong,Jia$Reverend	Reverend
Sheet5

Cell Formulas
Range	Formula
B1	=IFERROR(RIGHT(A1,LEN(A1)-FIND("!",A1,1)),IFERROR(RIGHT(A1,LEN(A1)-FIND("$",A1,1)),""))

Copy formula down as required.

Hope that helps.

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,464

Office Version

1. 365

Platform

1. Windows

• Oct 6, 2013

• #5

Another formula you can try...

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

 

Upvote 0

L

LauraForiero

New Member

Joined

Oct 6, 2013

Messages

18

• Oct 6, 2013

• #6

worked like a charm. thank you so much

 

Upvote 0

L

LauraForiero

New Member

Joined

Oct 6, 2013

Messages

18

• Oct 6, 2013

• #7

Second quesitons

I have cell content of a variable length string and I need to extract all text between "!" or "$" and the ","

A

Brouillet,Amy Anne!Miss

Dupuis,Jean Paul$Monsieur

Ethier,Guy!Mr

Fu,Guo Qing$Prince

Gowan,Louise Marie$Mademoiselle

McGill,James Patrick!Dr

Robinson,Jacques$Monsieur

Vinet,Jean Pierre!Mr

Voigt,Claire Antoinette$Madame

Wong,Jia$Reverend

<tbody>
</tbody>

Again, i need one expression for the entire column

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,464

Office Version

1. 365

Platform

1. Windows

• Oct 6, 2013

• #8

LauraForiero said:

worked like a charm. thank you so much

Click to expand...

Not sure who you were responding to (you had four different formulas offered to you), but here is one more that only uses a total of 3 function calls...

=MID(A1,FIND("$",SUBSTITUTE(A1,"!","$")&"$")+1,99)

And you can copy it down past the end of your current data in anticipation of future data entries later on.

 

Upvote 0

L

LauraForiero

New Member

Joined

Oct 6, 2013

Messages

18

• Oct 6, 2013

• #9

How do i extract anything between "!" or "$" and the ","

Brouillet,Amy Anne!Miss

for example, i would like to extract the "Amy Anne" here

 

Upvote 0

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Oct 6, 2013

• #10

Try this formula:

Code:

=MID(A2,FIND(",",A2,1)+1,(FIND("!",SUBSTITUTE(A2,"$","!"),1)-FIND(",",A2,1))-1)

 

Upvote 0