Transliteration from Latin to Cyrillic letters in Excel

I am trying to convert text from Serbian latin to Serbian cyrillic.
The text is in column A of a worksheet in Excel. I therefore recorded
a macro using the search and replace function. The manual search and
replace works perfectly (but is a big hassle), but the macro does not
record properly.

For example, I want the letter "d" to be replaced by "=C4". In the macro
editor, the following is recorded:

    Selection.Replace What:=3D"d", Replacement:=3D"_", LookAt:=3DxlPart, _
        SearchOrder:=3DxlByRows, MatchCase:=3DTrue

The problem is that I cannot input cyrillic letters in the macro. I.e.
the problem is in:

Replacement:=3D"_"

which should read

Replacement:=3D"=C4"

Is there a way to work around this problem? Thanks!
0
kecman (3)
10/20/2008 9:39:12 AM
mac.office 7017 articles. 0 followers. Follow

4 Replies
2461 Views

Similar Articles

[PageSpeed] 34

Within limits there is a way around it.

Depending on which version of Excel you have, VBA can pass only ANSI
characters as strings.  You need Unicode.

Which means you need the ChrW function:
http://msdn.microsoft.com/en-us/library/613dxh46.aspx

Dim MyWanted Char as Long

MyWantedChar = ChrW(&H01FD)

     Selection.Replace What:="d", Replacement:=MyWantedChar, _
LookAt:=xlPart,  SearchOrder:=xlByRows, MatchCase:=True


ChrW expects a four-digit Hex argument, but you can specify Decimal as an
integer of you prefer.

Cheers

On 20/10/08 7:09 PM, in article
3660d0cd-6848-4eab-8b3c-083d3a294ac5@m74g2000hsh.googlegroups.com,
"kecman@gmail.com" <kecman@gmail.com> wrote:

> I am trying to convert text from Serbian latin to Serbian cyrillic.
> The text is in column A of a worksheet in Excel. I therefore recorded
> a macro using the search and replace function. The manual search and
> replace works perfectly (but is a big hassle), but the macro does not
> record properly.
> 
> For example, I want the letter "d" to be replaced by "�". In the macro
> editor, the following is recorded:
> 
>     Selection.Replace What:="d", Replacement:="_", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=True
> 
> The problem is that I cannot input cyrillic letters in the macro. I.e.
> the problem is in:
> 
> Replacement:="_"
> 
> which should read
> 
> Replacement:="�"
> 
> Is there a way to work around this problem? Thanks!

-- 

Don't wait for your answer, click here: http://www.word.mvps.org/

Please reply in the group.  Please do NOT email me unless I ask you to.

John McGhie, Microsoft MVP, Word and Word:Mac
Nhulunbuy, NT, Australia.  mailto:john@mcghie.name

0
john7143 (1072)
10/21/2008 9:05:04 AM
On Oct 21, 11:05=A0am, John McGhie <j...@mcghie.name> wrote:
> Within limits there is a way around it.
>
> Depending on which version of Excel you have, VBA can pass only ANSI
> characters as strings. =A0You need Unicode.
>
> Which means you need the ChrW function:http://msdn.microsoft.com/en-us/li=
brary/613dxh46.aspx
>
> Dim MyWanted Char as Long
>
> MyWantedChar =3D ChrW(&H01FD)
>
> =A0 =A0 =A0Selection.Replace What:=3D"d", Replacement:=3DMyWantedChar, _
> LookAt:=3DxlPart, =A0SearchOrder:=3DxlByRows, MatchCase:=3DTrue
>
> ChrW expects a four-digit Hex argument, but you can specify Decimal as an
> integer of you prefer.
>
> Cheers
>
> On 20/10/08 7:09 PM, in article
> 3660d0cd-6848-4eab-8b3c-083d3a294...@m74g2000hsh.googlegroups.com,
>
>
>
> "kec...@gmail.com" <kec...@gmail.com> wrote:
> > I am trying to convert text from Serbian latin to Serbian cyrillic.
> > The text is in column A of a worksheet in Excel. I therefore recorded
> > a macro using the search and replace function. The manual search and
> > replace works perfectly (but is a big hassle), but the macro does not
> > record properly.
>
> > For example, I want the letter "d" to be replaced by "=E4". In the macr=
o
> > editor, the following is recorded:
>
> > =A0 =A0 Selection.Replace What:=3D"d", Replacement:=3D"_", LookAt:=3Dxl=
Part, _
> > =A0 =A0 =A0 =A0 SearchOrder:=3DxlByRows, MatchCase:=3DTrue
>
> > The problem is that I cannot input cyrillic letters in the macro. I.e.
> > the problem is in:
>
> > Replacement:=3D"_"
>
> > which should read
>
> > Replacement:=3D"=E4"
>
> > Is there a way to work around this problem? Thanks!
>
> --
>
> Don't wait for your answer, click here:http://www.word.mvps.org/
>
> Please reply in the group. =A0Please do NOT email me unless I ask you to.
>
> John McGhie, Microsoft MVP, Word and Word:Mac
> Nhulunbuy, NT, Australia. =A0mailto:j...@mcghie.name

Great, thanks!
0
kecman (3)
10/21/2008 5:54:36 PM
On 10/21/08 1:54 PM, in article
83ef114f-3623-4e30-88cf-b7e2286b5dc1@t65g2000hsf.googlegroups.com,
"kecman@gmail.com" <kecman@gmail.com> wrote:

> On Oct 21, 11:05�am, John McGhie <j...@mcghie.name> wrote:
>> Within limits there is a way around it.
>> 
>> Depending on which version of Excel you have, VBA can pass only ANSI
>> characters as strings. �You need Unicode.
>> 
>> Which means you need the ChrW
>> function:http://msdn.microsoft.com/en-us/library/613dxh46.aspx
>> 
>> Dim MyWanted Char as Long
>> 
>> MyWantedChar = ChrW(&H01FD)
>> 
>> � � �Selection.Replace What:="d", Replacement:=MyWantedChar, _
>> LookAt:=xlPart, �SearchOrder:=xlByRows, MatchCase:=True
>> 
>> ChrW expects a four-digit Hex argument, but you can specify Decimal as an
>> integer of you prefer.
>> 
>> Cheers
>> 
>> On 20/10/08 7:09 PM, in article
>> 3660d0cd-6848-4eab-8b3c-083d3a294...@m74g2000hsh.googlegroups.com,
>> 
>> 
>> 
>> "kec...@gmail.com" <kec...@gmail.com> wrote:
>>> I am trying to convert text from Serbian latin to Serbian cyrillic.
>>> The text is in column A of a worksheet in Excel. I therefore recorded
>>> a macro using the search and replace function. The manual search and
>>> replace works perfectly (but is a big hassle), but the macro does not
>>> record properly.
>> 
>>> For example, I want the letter "d" to be replaced by "�". In the macro
>>> editor, the following is recorded:
>> 
>>> � � Selection.Replace What:="d", Replacement:="_", LookAt:=xlPart, _
>>> � � � � SearchOrder:=xlByRows, MatchCase:=True
>> 
>>> The problem is that I cannot input cyrillic letters in the macro. I.e.
>>> the problem is in:
>> 
>>> Replacement:="_"
>> 
>>> which should read
>> 
>>> Replacement:="�"
>> 
>>> Is there a way to work around this problem? Thanks!
>> 
>> --
>> 
>> Don't wait for your answer, click here:http://www.word.mvps.org/
>> 
>> Please reply in the group. �Please do NOT email me unless I ask you to.
>> 
>> John McGhie, Microsoft MVP, Word and Word:Mac
>> Nhulunbuy, NT, Australia. �mailto:j...@mcghie.name
> 
> Great, thanks!
Great solution John, but there is just one teeny problem: ChrW is a VB6
function and not available in Macintosh VBA. Unicode is just not supported.

-- 
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

0
bob6686 (387)
10/21/2008 8:02:39 PM
On Oct 21, 10:02=A0pm, Bob Greenblatt <b...@nospam.com> wrote:
> On 10/21/08 1:54 PM, in article
> 83ef114f-3623-4e30-88cf-b7e2286b5...@t65g2000hsf.googlegroups.com,
>
>
>
> "kec...@gmail.com" <kec...@gmail.com> wrote:
> > On Oct 21, 11:05=A0am, John McGhie <j...@mcghie.name> wrote:
> >> Within limits there is a way around it.
>
> >> Depending on which version of Excel you have, VBA can pass only ANSI
> >> characters as strings. =A0You need Unicode.
>
> >> Which means you need the ChrW
> >> function:http://msdn.microsoft.com/en-us/library/613dxh46.aspx
>
> >> Dim MyWanted Char as Long
>
> >> MyWantedChar =3D ChrW(&H01FD)
>
> >> =A0 =A0 =A0Selection.Replace What:=3D"d", Replacement:=3DMyWantedChar,=
 _
> >> LookAt:=3DxlPart, =A0SearchOrder:=3DxlByRows, MatchCase:=3DTrue
>
> >> ChrW expects a four-digit Hex argument, but you can specify Decimal as=
 an
> >> integer of you prefer.
>
> >> Cheers
>
> >> On 20/10/08 7:09 PM, in article
> >> 3660d0cd-6848-4eab-8b3c-083d3a294...@m74g2000hsh.googlegroups.com,
>
> >> "kec...@gmail.com" <kec...@gmail.com> wrote:
> >>> I am trying to convert text from Serbian latin to Serbian cyrillic.
> >>> The text is in column A of a worksheet in Excel. I therefore recorded
> >>> a macro using the search and replace function. The manual search and
> >>> replace works perfectly (but is a big hassle), but the macro does not
> >>> record properly.
>
> >>> For example, I want the letter "d" to be replaced by "=E4". In the ma=
cro
> >>> editor, the following is recorded:
>
> >>> =A0 =A0 Selection.Replace What:=3D"d", Replacement:=3D"_", LookAt:=3D=
xlPart, _
> >>> =A0 =A0 =A0 =A0 SearchOrder:=3DxlByRows, MatchCase:=3DTrue
>
> >>> The problem is that I cannot input cyrillic letters in the macro. I.e=
..
> >>> the problem is in:
>
> >>> Replacement:=3D"_"
>
> >>> which should read
>
> >>> Replacement:=3D"=E4"
>
> >>> Is there a way to work around this problem? Thanks!
>
> >> --
>
> >> Don't wait for your answer, click here:http://www.word.mvps.org/
>
> >> Please reply in the group. =A0Please do NOT email me unless I ask you =
to.
>
> >> John McGhie, Microsoft MVP, Word and Word:Mac
> >> Nhulunbuy, NT, Australia. =A0mailto:j...@mcghie.name
>
> > Great, thanks!
>
> Great solution John, but there is just one teeny problem: ChrW is a VB6
> function and not available in Macintosh VBA. Unicode is just not supporte=
d.
>
> --
> Bob Greenblatt [MVP], Macintosh
> bobgreenblattATmsnDOTcom

I have tried John's solution and it seems to work, even in Mac. The
only snag I have come across is if the letter I want to change in the
text is not a standard English alphabet letter. For example a "c" with
a ^ on top. So, this does not work:

Selection.Replace What:=3DChrW(&H100D), Replacement:=3DChrW(&H447),
LookAt:=3DxlPart, _
        SearchOrder:=3DxlByRows, MatchCase:=3DTrue

whereas this, as an example, does:

Selection.Replace What:=3D"G", Replacement:=3DChrW(&H413), LookAt:=3DxlPart=
,
_
        SearchOrder:=3DxlByRows, MatchCase:=3DTrue

Any ideas?
0
kecman (3)
10/22/2008 12:52:53 PM
Reply:

Similar Artilces: