Apostrophe Dilemma

[Saxman]

 =SUBSTITUTE(UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A2&"0123456789("))-1))),"'","")

The above function which Stephanie provided me with a few days ago, strips
all the junk in the following name for example.
Attila's Peintre (8-7) becomes Attilas Peintre

However, the actual name below when copied/pasted special from a web page
Attila´s Peintre (8-7)
becomes:-
Attila´s Peintre (Part stripped).

As you will observe, the apostrophe remains, because there is a difference in
the apostrophe for that particular font.

Is there a way that I can modify the above function to include '´' rather
than '''?

I'm sure it would be a matter of a simple copy/pasting the modified
apostrophe within the function above. It looks like I would have to modify
the end of the function, but I'm not sure how as I cannot ascertain whether
they are quotation marks or apostrophes? There are 4 'marks' on one side of
the comma and 5 on the other.

TIA

 

[bj]

figure out where in the text it is in one example and use (for your example)
=code(mid(A2,7,1)) and replace the "'" with char(the code value)
=SUBSTITUTE(UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A2&"0123456789("))-1))),char(???),"")

 

[Saxman]

Sorry to be so thick, but do you mean typing,
CODE(MID(A2,7,1)) to find out the ASCII code of '´' and replace it with the
the question marks (or "'") in the function below?

 

[bj]

I would probably do it separately in that you may end up having different
characters to replace at diferent times
the Question marks were put in as a place holder to indicate where to put
the results from the code formula

 

[Peo Sjoblom]

You can use this

=SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A3&"0123456789("))-1))),"'",""),"´","")



--
Regards,

Peo Sjoblom

 

[Saxman]

Just found out that it is code 180 by using the function =CODE("´")

It works fine now!

Thanks!!!

 

[Saxman]

I'll give it a whirl Thursday, thanks!


=SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A3&"0123456789("))-1))),"'",
""),"´","")