Anyway to discern Japanese character fonts in cells and format only those?

[soar2011]

Hi all,

I have a big excell sheet which has many many Japanese / Chinese fonts sprinkled over to many cells. Is there a way or formula to find, select, and increase font size of these characters WHILE leaving the english text fonts in the same cells untouched?

In any cell, a size 10 Arial english text is readable, while Japanese kanji is not or too small. An example: 禦. I want to make them be size 14 which looks like 禦

Manually doing it will require months of chore. Is there a way to do this?

Thank you all.

 

[AD_Taylor]

Hi and welcome to the forum! Sounded like a challenge so I gave it a go

Give this VBA code a try. First of all make a selection containg the cells you want to modify then run the macro. I'd suggest using a sample to test on at first, maybe copy and paste a small amount from your data?
I wasn't too sure how they were entered into your data so I tested with Insert > Symbol and then I inserted some test Greek characters. They all enlarged for me.

NOTE:
This code will enlarge anything that isn't in the standard alphabet. This means any number 0-9, any symbol, e.g. !, ? or @ and of course any Japanese or Chinese symbols I hope.

Sub test()    
    Dim rngChk As Range
    Dim area As Range
    Dim rngC As Range
    
    Dim strCell As String
    Dim char As String
    Dim i As Integer
    
    Set rngChk = Selection
    For Each area In rngChk.Areas
        For Each rngC In area.Cells
            strCell = rngC.Value
            i = 1
            
            Do While strCell <> ""
                char = Left(strCell, 1)
            
                If (char >= "A" And char <= "Z") _
                    Or (char >= "a" And char <= "z") Then
                    
                    'The character is in the standard alphabet
                Else
                    'The character is not standard, e.g. a symbol of some kind
                    rngC.Characters(i, 1).Font.Size = 14
                    
                End If
                
                strCell = Right(strCell, Len(strCell) - 1)
                i = i + 1
            Loop
        Next rngC
    Next area
End Sub

 

[soar2011]

Thank you for replying. I will try what you said though I don't know what it is to run macro or what is VBA. Hopefully i can find tutorial on it.

What I see on my excell sheet is those Asian characters are Arial fonts as the english text co existing in the cells. So arial or times new roman fonts seems to include these foreign characters. Even cyrillic russian characters are the same arial or times new roman fonts. So I wonder Japanese characters (like 専, 導 , 軽 etc) will be considered as different as 0-9, any symbol, e.g. !, ? or @ from english arial fonts.

But this is just a thought since I haven't started anything as you suggested.

Thank you for giving me something to grab.

 

[soar2011]

Hey man, you did it.
Actually I just tested it. It increased font size for all text. Maybe it is because my selected cells included mongolian cyrillic text and Japanese / Chinese characters.

If your code considers mongolian or russian cyrillic characters (а, б, в, г, etc)
as different symbols than english characters (a, b, c, d etc), then it is working.

Then i tried inserting latin/ english character jibberish into the cells and ran the macro again. IT WORKS.

Could you suggest how to leave Russian /Mongolian fonts and numbers intact, and increase the size of Japanese / Chinese fonts? In other words leave everything intact but only increase the size of Japanese / Chinese fonts?

The image shows your great result:

 

[soar2011]

I tried changing this part of your code:

If (char >= "A" And char <= "Z") _ Or (char >= "a" And char <= "z") Then...

...by replacing A and Z by russian А and Я. It seems to work except 2 characters Russians don't have but Mongolians have: Ө, Ү.

These 2 letters got increased in size together with Japanese characters.

So is there any exclusion rule that will increase Japanese kanji characters only and exclude all else? Excluding by unicode number or whatever? Because I don't want to increase the font size of numbers. Your code increased the font size of everything except english / latin alphabet, in my humble mind.

Thank you for your STRONG SKILL.

 

[AD_Taylor]

No problem, I'm glad it's working for you in some parts. I normally post a quick explanation of how to use the code so sorry for leaving that out, but it seems you got it working ok anyway.
I'm sure it is as you say that it is literally picking up anything that is not a standard English character. This would obviously include Russian & Mongolian.

I'll have a look at changing the code for you. You're definitely looking at the right part - it is the "If (char..." bit that needs changing.

Unfortunately I've had a look through the characters my computer supports and I can't find any Japanese/Chinese characters. I do have a suggestion though. If you put all the characters you want it to include into a separate worksheet as a list then I can tell the macro to go and see if the character is in that list instead. It becomes even more flexible then because say for example a new character came along that you didn't include before - just add it to the list and you're good to go again.

I think this is the way forward. I'll get working on the code and post up a step by step guide for you.

 

[ZVI]

Hi,
Try this code. It's not sensitive to symbol’s code just to names of fonts.
It sets the required sizes for the listed fonts only for the text values of selection.
For the fonts to be fixed define the names and sizes in FontNames and FontSizes arrays accordingly.

Sub ResizingFonts()
' ZVI:2013-03-20 http://www.mrexcel.com/forum/excel-questions/692196-anyway-discern-japanese-character-fonts-cells-format-only-those.html
' Replacing in selection the sizes of fonts defined in Settings (see below)
 
  Dim Area As Range, FontNames, FontSizes, n As String, Rng As Range, Cell As Range, i As Long
 
  '--> Settings. Set font names and required font sizes (change to suit)
  FontNames = Array("Arial", "Times New Roman")
  FontSizes = Array(10, 12)
  '<-- End of settings
 
  ' Choose range with only string values
  Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
  If Rng Is Nothing Then Exit Sub
  On Error Resume Next
  Set Rng = Rng.SpecialCells(xlCellTypeConstants, xlTextValues)
  If Err Then Err.Clear: Exit Sub Else On Error GoTo exit_
 
  ' Fix sizes of fonts
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 0 To UBound(FontNames)
      n = FontNames(i)
      .Item(n) = FontSizes(i)
    Next
    For Each Area In Rng.Areas
      For Each Cell In Area
          For i = 1 To Cell.Characters.Count
            n = Cell.Characters(i, 1).Font.Name
            If .Exists(n) Then
              Cell.Characters(i, 1).Font.Size = .Item(n)
            End If
          Next
      Next
    Next
  End With
 
exit_:
  If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number
 
End Sub

Regards

 

[AD_Taylor]

As an alternative here's my solution.

1. Create a new worksheet and name it VBA_CharResize. The name is important as it will be used in the code.
2. Type the characters that you want to resize into cell A1. For example to resize the first few letters of the alphabet, enter abc into A1. (NOTE: This is case sensitive so if you wanted to resize all letters for the start of the alphabet, you would need to put abcABC.
3. Feel free to hide this worksheet now.
4. Run the code below. It simply checks if any of the characters also exist in this new worksheet we've created. This way you can resize whatever characters you want to including the standard English alphabet if wanted.

Option Explicit


Sub CharResize()
    Const wsName As String = "VBA_CharResize"
    Dim strChars As String
    
    Dim rngChk As Range
    Dim area As Range
    Dim rngC As Range
    
    Dim strCell As String
    Dim char As String
    Dim i As Integer
    
    strChars = ThisWorkbook.Worksheets(wsName).Range("A1").Value
    
    Set rngChk = Selection
    For Each area In rngChk.Areas
        For Each rngC In area.Cells
            strCell = rngC.Value
            i = 1
            
            Do While strCell <> ""
                char = Left(strCell, 1)
            
                If InStr(strChars, char) Then
                    'The character is specified so resize it
                    rngC.Characters(i, 1).Font.Size = 14
                End If
                
                strCell = Right(strCell, Len(strCell) - 1)
                i = i + 1
            Loop
        Next rngC
    Next area
End Sub

Hope this helps!

PS - I think this is much more flexible as it allows you to specify what characters you want to use not just accept that all of a certain set may be resized.

 

[soar2011]

Hi Vladimir,

Probably my wording misled you. What I found is the characters/ letters in my excel sheet are all arial in one column or two. So whether it's russian, mongolian or japanese, every letter/ character in the same column appears to be all arial or times new roman depending on how i set it. I see that the following characters from different languages can be all the same font in the one very the same cell: 専, 導 бабушка, үүрдийн

Perhaps that's why I did not see result with your code.

Anyway thank you very much.

 

[soar2011]

As an alternative here's my solution.

1. Type the characters that you want to resize into cell A1. For example to resize the first few letters of the alphabet, enter abc into A1. (NOTE: This is case sensitive so if you....

The characters I want to resize are several thousand, unlike english 26. So I can't use it. But you pointed me to good direction. Instead selecting from a to z, - following your advice - I need to select from 1st Japanese/Chinese character to the last.

Funny thing is VBA command don't let Asian characters entered. So got something from here: select foreign characters (excel 2003) | Windows Secrets Lounge

and changed the unicode thing. I'm not even sure if I entered the right unicode. But it seems to do the job.

But most of all unless you showed the way, I would not have searched for replacing english character range in your code, thus anyone's code.

Thanks heaps!