Replace hex characters with readable ones

[opislak]

Hi all,

From "Page Source Code" in Sharepoint, I can copy a list of names of users. However, in that list, special characters like accented ones in french, are put in hex code.
I know the formula "substitute", but as the characters may vary, I would like a generic formula to replace them.

e.g.:
O'Toole --> replace by O'Toole
Gérard --> replace by Gérard
François --> replace by François
Jerôme --> replace by Jerôme
etc.

Thanks for helping me out.
Patrick.

 

[Gerald Higgins]

Perhaps like this ?

=IFERROR(IF(FIND("&#",A1)>=0,LEFT(A1,FIND("&#",A1)-1)&CHAR(MID(A1,FIND("&#",A1)+2,FIND(";",A1)-FIND("&",A1)-2))&MID(A1,FIND(";",A1)+1,100)),A1)

There may be better ways of doing this . . .

 

[OaklandJim]

FWIW these are "special" characters in html.

HTML Symbols

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

I suspect that you'd need to do some sort of lookup -- after setting up a table of expected values -- to get the correct character in Windows/Excel?

 

[opislak]

Perhaps like this ?



There may be better ways of doing this . . .

Gerald,
I'm still trying to understand your formula But it works fantastic. Thank you very much!!
Patrick.

 

[Gerald Higgins]

Hi, if you want to understand the formula better, you can use Excel's Formula Evaluation tool to step through the various stages of the formula - that might be interesting for you, to try it with a few different input values.