Extracting text after symbol in excel

[LauraForiero]

HI
Probably simple I just cant figure it out.

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

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

<colgroup><col style="width: 159pt; mso-width-source: userset; mso-width-alt: 7753;" width="212"> <tbody>
</tbody>

I know i must use the Right and Find function together but don't know how to. I need 1 single expression for the entire column.
thanks in advance

 

[Rick Rothstein]

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

For this question, the following formula is necessarily longer than the one I posted in my last message (#8) above...

=MID(A1,FIND(",",A1&",")+1,FIND("$",SUBSTITUTE(A1&"$","!","$"))-FIND(",",A1&",")-(A1<>""))

but, like my last formula, this one can be copied down past the end of your existing data in anticipation or future data entries.

 

[LauraForiero]

is there a way using the right,left,len, find or mid functions?

 

[bschwartz]

As far as I know, you can't use LEFT or RIGHT. You need MID.

Here I am using MID, FIND, and SUBSTITUTE:

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

 

[Rick Rothstein]

is there a way using the right,left,len, find or mid functions?

When you have multiple differeing answers give to a question you post, you should click "Reply With Quote" (and delete unnecessary text for the response you give) instead of just clicking "Reply"... that way, we can see who you are replying to. Right now, I have no idea who the above question was directed to (probably not me because my last message and your last message probably overlapped each other).

 

[Aladin Akyurek]

is there a way using the right,left,len, find or mid functions?

Like the first one, this is also short:

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

You might need to wrap this into an IFERROR call...

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

 

[LauraForiero]

Sorry for the confusion, I am new completely new to this site.

I have a very last question. I now want to extract anything before the ","

Brouillet,Amy Anne!Miss

I would like to extract"Brouillet" from this. Again, how would I do so?
Thank you again! Your answers have been very helpful

When you have multiple differeing answers give to a question you post, you should click "Reply With Quote" (and delete unnecessary text for the response you give) instead of just clicking "Reply"... that way, we can see who you are replying to. Right now, I have no idea who the above question was directed to (probably not me because my last message and your last message probably overlapped each other).

 

[Aladin Akyurek]

Sorry for the confusion, I am new completely new to this site.

I have a very last question. I now want to extract anything before the ","

Brouillet,Amy Anne!Miss

I would like to extract"Brouillet" from this. Again, how would I do so?
Thank you again! Your answers have been very helpful

=IFERROR(LEFT(A2,SEARCH(",",A2)-1),A2)

would do...

 

[bschwartz]

=left(a2,find(",",a2,1)-1)

 

[bschwartz]

Looking at all of these different solutions (each of which produces the desired result) kind of makes me wonder what the difference is between FIND and SEARCH.

Can anyone shed some light on this?

 

[Aladin Akyurek]

Looking at all of these different solutions (each of which produces the desired result) kind of makes me wonder what the difference is between FIND and SEARCH.

Can anyone shed some light on this?

FIND does a case-sensitive search, SEARCH doesn't. I believe it's better to invoke the former when case-sensitivity is required, but not when case-indifference for items like 1, comma, etc. upholds. This is conditioned on SEARCH being faster, if that's the case.