How to Replace various texts in an object at once
Hi,
I´ve faced an interest5ing requirement recently. We had an object coming from a query in which some characters comes wrongly mapped due to a different charcode. What I mean by this :
| Should be | Shown as |
| Ã | Ç |
| Õ | å |
| Ç | € |
| Á | µ |
| Ç | ? |
| é | ‚ |
| â | ƒ |
The table above show , in the first column, what is the real character and the second, how is it shows. Note that this table is just an excert of the real one. In the total, 47 substitutions had to be made.
For instance the text :
|
VIABILIZAR A AQUISIۂO E APARELHAMENTO
DE 47 âNIBUS, BEM COMO A MANUTEN€ÇO DE EQUIPE DE PROFISSIONAIS, COM VISTAS · IMPLEMENTA€ÇO DE DIVERSAS A€åES SOCIAIS MEDIANTE ATENDIMENTO MàVEL. |
should be
|
VIABILIZAR A AQUISIÇÃO E APARELHAMENTO
DE 47 ÔNIBUS, BEM COMO A MANUTENÇÃO DE EQUIPE DE PROFISSIONAIS, COM VISTAS · IMPLEMENTAÇÃO DE DIVERSAS AÇÕES SOCIAIS MEDIANTE ATENDIMENTO MÓVEL. |
In order to acomplish it, we use the below formula to, recursively , change all characters :
=Upper(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([Descrição do Projeto];”Ç”;”Ô);”å”;”Õ”);”€”;”Ç”);”µ”;”Á”);”?”;”Ç”);”‚”;”é”);”ƒ”;”â”);”…”;”à”);”‡”;”Ç”);”ˆ”;”Ê”);”Š”;”è”);”Œ”;”î”);”“”;”ô”);”•”;”ò”);”–”;”û”);”—”;”ù”);””;”á”);”¡”;”í”);”¢”;”ó”);”£”;”ú”);”¤”;”ñ”);”¥”;”Ñ”);”¦”;”ª”);”§”;”º”);”µ”;”Á”);”¶”;””);”·”;”À”);”Æ”;”ã”);”Ò”;”Ê”);”Ó”;”Ë”);”Ô”;”È”);”Õ”;”ı”);”à”;”Ó”);”â”;””);”ã”;”Ò”);”ä”;”õ”);”å”;”Õ”);”ë”;”Ù”);”ø”;”°”);”ı”;”Õ”);”Ò”;”Ô);”ÇÇ”;”CÔ);Car(160);”Á”);Car(201);”É”);”õ”;”Õ”);”â”;”ô”);” Ö”;”Í”))
The idea behind it is that the substitutins are made from the inside out, being [Descrição do Projeto] the object containig the “wrong” characters. This Replace is used as an argument for the next replace and so on.
Regards,
Rogerio