How to Fix Font Errors in Excel: Convert TCVN3 and VNI to Unicode Using VBA

Have you ever opened an Excel spreadsheet only to be greeted by a chaotic mess of unreadable characters? This specific type of font error is a common frustration for users working with older Vietnamese data files. The issue typically arises when a file was created using legacy font standards like TCVN3 (ABC) or VNI, which are not natively supported by modern Unicode standards (used by fonts like Arial or Times New Roman).

While installing the missing fonts is a temporary fix, the sustainable solution is to convert the data itself into Unicode. In this guide, “Thủ Thuật” will demonstrate how to automate this process using VBA (Visual Basic for Applications) macros, allowing you to instantly convert TCVN3 and VNI text to readable Unicode directly within Excel.

Why Do These Font Errors Occur?

Before diving into the solution, it is helpful to identify the source of the problem.

• TCVN3 (ABC) Standards: These fonts usually begin with a period, such as .VnTime, .VnTimeH, or .VnArial.
• VNI Standards: These fonts typically start with the prefix “VNI”, such as VNI-Times or VNI-Helve.

If your computer lacks these specific legacy fonts, Excel cannot render the text correctly. The VBA solutions below will translate these encodings into standard Unicode.

Setting Up the VBA Environment

To perform these conversions, we utilize User Defined Functions (UDFs) in Excel. First, you need to access the VBA editor.

1. Open your Excel file.
2. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
3. Go to Insert > Module to create a space for the code.

Animation showing how to insert a new Module in the VBA Excel editor

Once your module is open, you can paste the specific conversion scripts described in the sections below.

Method 1: Converting TCVN3 to Unicode

The TCVN3 standard (often referred to as the ABC font standard) is prevalent in older government and administrative documents in Vietnam. To convert text from fonts like .VnTime to a Unicode font like Times New Roman, we use a custom function named TCVN3toUNICODE.

How to use the function:
After pasting the TCVN3 conversion code into your VBA Module (source code available via Giaiphapexcel), return to your spreadsheet. You can now use the function just like a standard Excel formula.

Syntax:
=TCVN3toUNICODE(Cell_Address)

Example:
If cell B2 contains the corrupted text in .VnTime font, type =TCVN3toUNICODE(B2) into cell C2.

Screenshot demonstrating the TCVN3 to Unicode conversion result in Excel

As shown in the image above, the unreadable text in column B is successfully converted to readable Vietnamese Unicode text in column C.

Method 2: Converting VNI to Unicode

Similar to TCVN3, the VNI standard was widely used in the southern regions of Vietnam before Unicode became the global standard. Fonts like VNI-Times require a different mapping algorithm. For this, we use the VniToUni function (source: caulacbovb).

How to use the function:
Paste the VNI-specific script into your VBA module. Then, use the formula in your worksheet.

Syntax:
=VniToUni(Cell_Address)

Example:
If cell B2 contains text formatted with VNI-Korin or VNI-Times that displays errors, input =VniToUni(B2) in the adjacent cell.

Excel spreadsheet showing the conversion of VNI font to Unicode text

The function instantly re-encodes the string, allowing you to change the font to Arial or Times New Roman without losing the content.

Advanced Solution: Auto-Detect and Combined Conversion

In some scenarios, you might deal with a dataset that mixes both TCVN3 and VNI errors, or you might not be sure which encoding was used. Advanced VBA scripts can be written to combine these functions.

A combined function can check the input string characters and automatically determine whether to apply the TCVN3 or VNI conversion logic. This eliminates the need to manually identify the source font, streamlining the workflow for large, mixed datasets.

Conclusion

Handling legacy font errors in Excel does not require manual re-typing. By utilizing VBA macros, you can efficiently convert outdated TCVN3 and VNI encodings to the universal Unicode standard. This ensures your documents are readable, professional, and compatible with all modern devices.

For those who want to practice or need the raw code, you can download the sample file here: http://bit.ly/2v61bE7.

Stay tuned to Thủ Thuật for more in-depth guides on Excel processing and office productivity tips. If you found this solution helpful, please share it with your colleagues!