Non english characters tricky removal

[filemaker 8 user]

I have a field that has some non english characters in some of the records, eg:

V�stervik

Café

StraÌ°åÄe

Månzfa

Sérgio

ZÌ’rich

Fürstenfeld

What I need to do is if the record has a non-english character in it to do a calculation that finds all those records, so I can manually replace the non english "é", with "e".

My original idea was to make a list of all the non english characters and do a pattern match but there are too many non english characters and no real way to know them all.

Does anyone know how to do a pattern match or some other way to find all the non english character in this field? Thanks for your help

[comment]

Try a calculation field (result is Number) =

YourField ≠ Filter ( Lower ( YourField ) ; " abcdefghijklmnopqrstuvwxyz0123456789" ) 

Search for 1 in this field.

---

NOTE: You may need to add some punctuation marks to the list of allowable characters.

Edited June 12, 2008 by Guest

[The Shadow]

No, I don't think there's any easy way to find them using find mode.

What I would suggest is to add a temporary calculated column that removes all acceptable english chars and punctation or digits. You could then enter find mode and find all records with a non-empty "unacceptable chars" column, and either add the characters to the acceptable list or get rid of them.

The calculation would be a large Substitute statement starting out like:

     Substitute( fieldToCheck; ["A";""]; ["a";""]; ["B";""]; etc...

that removed all the characters that are acceptable.

Edit:

Yikes! Comment's calculation makes my scheme look silly. :)

Edited June 12, 2008 by Guest

[comment]

Sorry! :)

[filemaker 8 user]

Thanks a bunch, great answer!!

[Mark Welch]

Here's the code I created today, after reading this thread, to replace foreign characters (extended ASCII) with their logical counterparts in the English character set (standard ASCII).

Substitute ( mtitle;

["¹" ; "1"];

["²" ; "2"];

["³" ; "3"];

["Á" ; "A"];

["á" ; "a"];

["À" ; "A"];

["à" ; "a"];

["Â" ; "A"];

["â" ; "a"];

["Ä" ; "A"];

["ä" ; "a"];

["Ã" ; "A"];

["ã" ; "a"];

["Å" ; "A"];

["å" ; "a"];

["Æ" ; "AE"];

["æ" ; "ae"];

["Ç" ; "C"];

["ç" ; "c"];

["Ð" ; "D"];

["ð" ; "d"];

["É" ; "E"];

["é" ; "e"];

["È" ; "E"];

["è" ; "e"];

["Ê" ; "E"];

["ê" ; "e"];

["Ë" ; "E"];

["ë" ; "e"];

["Í" ; "I"];

["í" ; "i"];

["Ì" ; "I"];

["ì" ; "i"];

["Î" ; "I"];

["î" ; "i"];

["Ï" ; "I"];

["ï" ; "i"];

["Ñ" ; "N"];

["ñ" ; "n"];

["º" ; "o"];

["Ó" ; "O"];

["ó" ; "o"];

["Ò" ; "O"];

["ò" ; "o"];

["Ô" ; "O"];

["ô" ; "o"];

["Ö" ; "O"];

["ö" ; "o"];

["Õ" ; "O"];

["õ" ; "o"];

["Ø" ; "O"];

["ø" ; "o"];

["ß" ; "B"];

["Þ" ; "b"];

["þ" ; "b"];

["Ú" ; "U"];

["ú" ; "u"];

["Ù" ; "U"];

["ù" ; "u"];

["Û" ; "U"];

["û" ; "u"];

["Ü" ; "U"];

["ü" ; "u"];

["Ý" ; "Y"];

["ý" ; "y"];

["ÿ" ; "y"];

["™" ; ""]

)

Note that the last item in this list simply removes any "TM" symbols; others may prefer to substitute "TM" or "" instead. Some of the other substitutions are arbitrary, such as replacing the "Beta" symbol (ß) with a capital "B" and two others (Þ and þ) with a lower-case "b."

Edited January 15, 2009 by Guest
Solved my own problem, posted result