Formula to use in conjunction with UPPER to convert accented letters to non-accented

[Nola111]

I have sheet full of names that look like this:

Please Login or Register  to view this content.

I would like to see if there is a formula that can convert these names to this:

Please Login or Register  to view this content.

I know I can use UPPER for getting them to uppercase, but what about converting the accented and other foreign characters to standard characters.

[XLent]

Given the number of potential substitutes I don't think it's really feasible via formula -- there are User Defined Functions (UDF) published online that would do this, to extent character set supported, e.g.:

https://www.extendoffice.com/documen...haracters.html

[Glenn Kennedy]

That one didn't work (for me) with all of the characters in the sample provided...

[Glenn Kennedy]

Try this "brute force and ignorance" approach. I woudn't have attempted to set this up myself, if it hadn't been partly available online

http://howtouseexcel.net/excel-formu...ted-characters

I just added on a few more to cover those not already included.

[Nola111]

Try this "brute force and ignorance" approach. I woudn't have attempted to set this up myself, if it hadn't been partly available online

http://howtouseexcel.net/excel-formu...ted-characters

I just added on a few more to cover those not already included.

Hi Glenn. Thanks for that file!! I do have one issue though. When I try to copy your formula from your file to mine, it replaces some of the special characters in your formula with a question mark. Any ideas what could be going on here? I'm on the latest version of Excel 2016. Perhaps a difference in settings between our Excels?

[Nola111]

Glenn - I am realizing that there still a TON of characters missing (my list is about 5k names and I only presented a sample set here). I think I'm going to have to approach this with VBA as opposed to a formula. Thanks for your help.

[Glenn Kennedy]

No problem... except that you might still have a problem. My first approach was to use the VBA approach in Post 2. I couldn't get some of your special characters into the VBA.... even though they pasted perfectly into Excel itself. It's something to do with the installed character sets... I know a bit about Excel but nothing about PCs.

If it doesn't work.... TIO TIO. Turn it off. Turn it on.

IF you do get a VBA solution, it's be nice if you posted it here, for my education and the benefit of others....

[Nola111]

Hi Glenn, thanks again. So I ended up having one of my devs do this in .NET using some of the framework's off-the-shelf normalization functions, and working it in a database table instead. This seemed to cover everything except a couple Nordic characters. I understand there is a .Net package out there called UniDecode that can handle those as well. We skipped over those for now as we got the majority of them.

[AliGW]

As this is a public forum, it would be helpful to others if you would share the .NET solution you came up with. Thanks!

[Nola111]

As this is a public forum, it would be helpful to others if you would share the .NET solution you came up with. Thanks!

Ali, hi, absolutely - I didn't think it would apply since this is an Excel-centric forum, and we decided to go a non-Excel route, but sure thing:

So what we did was take our list of names and load them into a SQL Server database table.

Then he built a small console app using this code:

Please Login or Register  to view this content.

After that, we added the information about the Server, database, table name, and column names to the app.config and ran the program. It iterated over a list of 200k+ names in less than 2 seconds and normalized them.

Then we simply exported the normalized names back into Excel.

Hope this can help someone trying to accomplish the same.

[AliGW]

Thank you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.