Transliteration from Latin to Cyrillic letters in Excel

K

kecman

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!
 
J

John McGhie

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

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:[email protected]
 
K

kecman

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






--

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:[email protected]

Great, thanks!
 
B

Bob Greenblatt

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.
 
K

kecman

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.

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:=ChrW(&H100D), Replacement:=ChrW(&H447),
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True

whereas this, as an example, does:

Selection.Replace What:="G", Replacement:=ChrW(&H413), LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True

Any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top