2011. március 28., hétfő

How to replace accented characters in Excel?

If you ever tried to generate user names and / or passwords from names with non-ASCII characters (accents, diacritical marks etc.) you had to find out that there is no function for that. I have only found solutions using macros.
I refrain from writing macros: I think anything that cannot be solved in an Excel sheet should not be, rather it should be programmed in a proper programming / scripting language.
Here is my 1 minute solution for that:
  1. The first two rows of your sheet should Start with From: and To: (see the screenshot) You should put the characters to replace in the first row and the accent less versions right below these.
  2. The first column below this should contain the original text to convert.
  3. Put this function right below the From and To rows, right of the first line of text to convert (third row, second column):
    =SUBSTITUTE(A3;B$1:B$2)
  4. Copy this function to the right (under each character to replace) and to all lines (which have text to fix).
Your last column is the all fixed text. :)

  • replace spaces, eg. From: " " To: "." like in the example.
  • You can also use this to delete: replace anything with empty
  • You can also use multiple letters (replace/ delete words, eg. change ß to ss and so on)
Have fun, let me know, if you need help! :)

Nincsenek megjegyzések:

Megjegyzés küldése

Rendszeres olvasók