Changing Chinese Font in Excel

[Bunson]

I got many files containing Chinese and English charater in same cells. I
need to convert those Chinese text into a special font. But Excel does not
provide any function to distinguish Chinese and English character in same
cell. It's very time consuming to change those font type manually. Does
anyone know if there is any Macro program code can speed up thise process?

 

[Bill Renaud]

In Excel 2000, if I put 9 characters in a cell (i.e. "abcdefghi") that
are formatted as "Times New Roman", then format the middle 3 as
"WingDings" (i.e. "def" formatted), then run the following test code:

Public Sub Test()
Dim rngCell As Range
Dim lngIndex As Long

Set rngCell = ActiveCell

For lngIndex = 1 To Len(rngCell.Value)
Debug.Print lngIndex, rngCell.Characters(lngIndex).Font.Name
Next lngIndex
End Sub

I get the following results in the Immediate window:

1 Null
2 Null
3 Null
4 Null
5 Null
6 Null
7 Times New Roman
8 Times New Roman
9 Times New Roman

This is only partially correct, as characters 1-3 should also be "Times
New Roman". Maybe newer versions of Excel have improved the Characters
object. If it would work on your system, then you might be able to use
it to step through each character to find out whether the font had been
changed to some other font.

 

[Zone]

Bunson, Standard English characters normally end at ASCII code 126. If the
font you are using obeys this rule, then this may work for you. Copy this
code and paste in a standard module. Select some cells you want to change
and run the macro, changing "Webdings" to the name of the special font you
want to use. Might work. Let me know! James

Sub Test()
Dim k As Integer, cell As Range
For Each cell In Selection
For k = 1 To Len(cell)
With cell.Characters(Start:=k, Length:=1)
If AscW(.Text) > 126 Then .Font.Name = "Webdings"
End With
Next k
Next cell
End Sub

 

[Zone]

Interesting, Bill. I get the same result on Excel 2003. James

 

[Bill Renaud]

Sorry, I made a mistake when I tried the routine previously. I forgot to
specify a single character (1) in the argument list to the Characters
property! (I had a cell with 9 characters in it, with the 3 middle ones
formatted as "Wingdings", instead of the default "Times New Roman".)

Public Sub ShowCharacters()
Dim rngCell As Range
Dim lngIndex As Long

Set rngCell = ActiveCell

For lngIndex = 1 To rngCell.Characters.Count
Debug.Print lngIndex, rngCell.Characters(lngIndex, 1).Font.Name
Next lngIndex
End Sub

Produces the following output in the Immediate window:

1 Times New Roman
2 Times New Roman
3 Times New Roman
4 Wingdings
5 Wingdings
6 Wingdings
7 Times New Roman
8 Times New Roman
9 Times New Roman

This should give you a start in figuring out where the Chinese
characters are located and then removing them or converting them to
something else.