Introduction

Have you ever thought it would be convenient to automatically identify specific characters (hiragana, katakana, alphabet, numbers, kanji) while using Excel or Google Sheets in your daily work? In this article, I will introduce how to use Unicode in Excel and Google Sheets to easily identify these characters.

Formula (Excel/Google Sheets compatible)

=IF(AND(UNICODE(LEFT(D1,1))>=12352,UNICODE(LEFT(D1,1))<=12447),"ひらがな",
    IF(AND(UNICODE(LEFT(D1,1))>=12448,UNICODE(LEFT(D1,1))<=12543),"カタカナ",
    IF(OR(AND(UNICODE(LEFT(D1,1))>=65,UNICODE(LEFT(D1,1))<=90),AND(UNICODE(LEFT(D1,1))>=97,UNICODE(LEFT(D1,1))<=122),AND(UNICODE(LEFT(D1,1))>=65313,UNICODE(LEFT(D1,1))<=65338),AND(UNICODE(LEFT(D1,1))>=65345,UNICODE(LEFT(D1,1))<=65370)),"Alphabet",
    IF(AND(UNICODE(LEFT(D1,1))>=48,UNICODE(LEFT(D1,1))<=57),"Number",
    IF(AND(UNICODE(LEFT(D1,1))>=19968,UNICODE(LEFT(D1,1))<=40959),"Kanji","Other")))))

By using this formula, you can easily determine whether the first character in a cell is “hiragana”, “katakana”, “alphabet (both full-width and half-width)”, “number”, or “kanji”.

Explanation of the Formula

This formula uses Unicode, an international character encoding standard that assigns unique codes to characters and symbols. In Excel and Google Sheets, the UNICODE function retrieves the Unicode code point of a character, allowing you to determine the type of character based on its value.

1.Hiragana: If the Unicode range is between 12352 and 12447, “hiragana” is displayed.

2.Katakana: If the Unicode range is between 12448 and 12543, “katakana” is displayed.

3.Alphabet: The following ranges are used to identify alphabets:
	•	Half-width uppercase: Unicode range 65–90
	•	Half-width lowercase: Unicode range 97–122
	•	Full-width uppercase: Unicode range 65313–65338
	•	Full-width lowercase: Unicode range 65345–65370

4.Numbers: If the Unicode range is between 48 and 57, “Number” is displayed.

5.Kanji: If the Unicode range is between 19968 and 40959, “Kanji” is displayed.

6.Other characters are displayed as “Other”.

Image of Excel

If You Only Want to Identify Hiragana

=IF(AND(UNICODE(LEFT(D1,1))>=12352,UNICODE(LEFT(D1,1))<=12447), "Hiragana", "Other")

The code for identifying only hiragana would look like the one above.

Conclusion

Using Unicode in Excel or Google Sheets to identify characters is extremely convenient. It is particularly useful when you need to distinguish specific characters within data or automate certain processes. By leveraging this method, you can further improve the efficiency of your daily work!

お知らせ