How to calculate byte length of one UTF8 character using Excel VBA?

user_16655615 0
Viewed 574

I need to cater a case that handles the byte length of some UTF8 characters (some are Chinese and some are Japanese). So far as I know, one UTF8 character can occupy spaces range from 1 byte to 4 bytes.

I need to count the bytes used by one specific UTF8 character as well as to count the total bytes occupied by a string of UTF8 characters (a mix of English and [Chinese or Japanese]).

From the article UTF 8 byte length of a string in microsoft excel , seems each UTF8 character have a fixed Unicode value and the unicode value can in turn help to determine the byte length.

I have tried to use ascw() function to get the value (not so sure the value returned is unicode value or not), but some UTF8 character returned with a negative value and cause error in determining the byte length used.

If I take the following UTF8 string as an example, when I use ascw() function,

台灣台中市西區民龍里21鄰模範街

asc("西") returned with -30337. In fact this word use 3 bytes.

asc("龍") returned with -24691. In fact this word use 3 bytes.

asc("里") returned with -28212. In fact this word use 3 bytes.

asc("鄰") returned with -28368. In fact this word use 3 bytes.

asc("街") returned with -30633. In fact this word use 3 bytes.

As a result, I cannot use the logic provided by the above link to determine the byte length of the above 5 UTF8 character correctly.

vba excel byte
1 Answers

Working from: https://www.vbforums.com/showthread.php?797751-RESOLVED-AscW-returns-negative-value

Dim s, i, ch, c As Range

Set c = Range("A1") 'pasted your text here
s = c.Value

For i = 1 To Len(s)
    ch = Mid(s, i, 1)
    c.Offset(i, 0).Value = ch
    c.Offset(i, 1).Value = Asc(ch)
    c.Offset(i, 2).Value = AscW(ch)
    c.Offset(i, 3).Value = (AscW(ch) And &HFFFF&)
Next i

Output:

台   63  21488   21488
灣   63  28771   28771
台   63  21488   21488
中   63  20013   20013
市   63  24066   24066
西   63  -30337  35199
區   63  21312   21312
民   63  27665   27665
龍   63  -24691  40845
里   63  -28212  37324
2    50  50      50
1    49  49      49
鄰   63  -28368  37168
模   63  27169   27169
範   63  31684   31684
街   63  -30633  34903
user_4788840
Related