Changing Chinese Font in Excel

[Guest]

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]

I assumed that this result was the due to the fact that I use a template
to create all of my workbooks, and the default font in it is "Times New
Roman". So I might have expected the result to be as shown below,
because the first 3 characters would just follow the default in the
template. Character 4 would be the first location where a change would
be needed.

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

Then you might have a chance at detecting where the new font begins and
ends, so those characters could be converted, or removed.

 

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