Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Client sells parts to companies in China. When the order workbook is returned, there are hundreds of cells that have Chinese characters in them (names, part number info, etc., etc.).

    I need to remove ALL of the Chinese and replace the cell contents with a blank.

    I suspect (or hope) the Chinese letters are non-printing characters in my English version of Excel.
    However, since there are quite a few characters, I can't easily do a Find and Replace...

    Further to this. Some cells are mixed with some English (e.g., a cell might have Chinese for "Part" and that symbol in Chinese is followed by "26")...whenever there is ANY Chinese in a cell, the cell should be blank. Phew.

    I'm attaching a small sample.

    Any thoughts?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Some of the ordinary-looking cells appear to have some Chinese too, for the following macro clears them in addition to the cells with Chinese. I have no idea how to avoid this, except asking your customers not to mix Chinese and English in the same cells.

    Code:
    Sub RemoveChinese()
      Dim oCell As Range
      Dim c As Integer
      For Each oCell In ActiveSheet.UsedRange
    	If Not IsNull(oCell.Font.Name) Then
    	  c = Asc(oCell.Font.Name)
    	  If c < 65 Or c > 90 Then
    		oCell.MergeArea.Clear
    	  End If
    	Else
    	  oCell.MergeArea.Clear
    	End If
      Next oCell
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thanks, Hans.

    It seems to almost work. I'm attaching another example with two sections in it this time. For some reason, the macro removes all of the second section. I can't see any Chinese characters in many of the cells in the second section that is totally removed. What am I missing?

    [quote name='HansV' post='776495' date='22-May-2009 16:55']Some of the ordinary-looking cells appear to have some Chinese too, for the following macro clears them in addition to the cells with Chinese. I have no idea how to avoid this, except asking your customers not to mix Chinese and English in the same cells.

    Code:
    Sub RemoveChinese()
      Dim oCell As Range
      Dim c As Integer
      For Each oCell In ActiveSheet.UsedRange
    	If Not IsNull(oCell.Font.Name) Then
    	  c = Asc(oCell.Font.Name)
    	  If c < 65 Or c > 90 Then
    		oCell.MergeArea.Clear
    	  End If
    	Else
    	  oCell.MergeArea.Clear
    	End If
      Next oCell
    End Sub
    [/quote]
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The problem is that they used a Chinese font for those cells. You should tell your customers not to do that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •