Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Chinese Font in Excel
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Chinese Font in Excel
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. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Chinese Font in Excel
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 "Bunson" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Chinese Font in Excel
Interesting, Bill. I get the same result on Excel 2003. James
"Bill Renaud" wrote in message . .. 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. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Chinese Font in Excel
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. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Chinese Font in Excel (The Answer!)
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. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
Excel 2003 traditional chinese to simplified chinese | Excel Discussion (Misc queries) | |||
How do I stop Excel from automatically changing font size? | Excel Discussion (Misc queries) | |||
how can i change the traditional chinese font to simplified chine. | Excel Discussion (Misc queries) | |||
changing default font and size in Excel | New Users to Excel |