Converting full-width and half-width characters in Excel (ASC, JIS, Power Query)

“Phone numbers in the address book mix full- and half-width digits”, “the furigana column has both half-width and full-width katakana” — Japanese-data spreadsheets commonly suffer from inconsistent width. This article walks through 3–4 practical fixes in Excel.

Full-width vs half-width recap

• Zenkaku (full-width): characters that visually take “2-byte” width. Hiragana, katakana, kanji, plus full-width Latin like Ａ １ ！
• Hankaku (half-width): characters that visually take “1-byte” width. ASCII letters/digits, half-width katakana like ｱ ｲ ｳ

LEN vs LENB differ because of this:

=LEN("ABC") → 3
=LENB("ABC") → 3 (ASCII, same)
=LEN("ＡＢＣ") → 3
=LENB("ＡＢＣ") → 6 (full-width counts as 2 bytes)

Method 1: ASC / JIS functions

The simplest path, using built-in worksheet functions.

Function	Behavior
=ASC(A1)	Convert full-width to half-width in A1 (also affects katakana)
=JIS(A1)	Convert half-width to full-width in A1

Examples:

A1: ＡＢＣ１２３ ｱｲｳ
=ASC(A1) → ABC123 ｱｲｳ  (full-width Latin → half-width; half-width katakana stays)
=JIS(A1) → ＡＢＣ１２３ アイウ  (half-width Latin → full-width; half-width katakana → full-width)

Trap: ASC converts full-width katakana to half-width katakana too. If you want “half-width Latin but keep katakana full-width”, a single call won’t do it — you need VBA or Power Query (below).

Method 2: Find and Replace (Ctrl + H)

For a single character substitution, use Find and Replace.

Example: convert full-width hyphen − to half-width - in a phone-number column:

1. Ctrl + H to open Replace
2. Find: − (full-width)
3. Replace: - (half-width)
4. Replace All

Trap: Excel’s Find and Replace has a “Match half/full-width” toggle behind the Options button. You may need to enable it for accurate matching.

Method 3: Power Query

For larger datasets or per-column custom rules, Power Query is the better fit.

1. Data tab → “From Table/Range”
2. Power Query Editor opens; select the target column
3. Transform tab → “Format” → “Convert to Half-Width” / “Convert to Full-Width”

Custom logic via M language is also possible:

= Table.TransformColumns(
    Source,
    {"Phone", Text.Lower},
    null
  )

This scales to “digits half-width, katakana full-width” hybrid rules cleanly.

Method 4: VBA macro

VBA’s StrConv is the most flexible:

Sub ConvertHankaku()
    Dim cell As Range
    For Each cell In Selection
        ' Examples:
        cell.Value = StrConv(cell.Value, vbNarrow)  ' Everything half-width
        cell.Value = StrConv(cell.Value, vbWide)    ' Everything full-width
        ' Args: vbNarrow = half-width, vbWide = full-width,
        '       vbKatakana = to katakana, vbHiragana = to hiragana
    Next cell
End Sub

To get “Latin half-width but katakana full-width”, chain conversions:

cell.Value = StrConv(StrConv(cell.Value, vbNarrow), vbKatakana)

Picking a method

Situation	Recommended
Simple half/full-width unification on one column	=ASC() / =JIS()
Replace specific characters	Find and Replace
Many columns, large data, hybrid rules	Power Query
Repeatable automation	VBA macro

Common pitfalls

1. “Conversion runs but nothing visually changes”

ASC / JIS only act on Latin characters, digits, symbols, and katakana. Kanji has no half-width form, so kanji input stays the same.

2. “Phone number becomes 9.01E+09 instead of 0901234567”

Not a width issue — Excel interpreted the cell as a number. Format the cell as Text before pasting, or prefix with a single quote ('09012345678).

3. “Half-width katakana garbles when exporting to CSV”

When the CSV is exported in Shift_JIS, half-width katakana is preserved. UTF-8 encodes them as multi-byte sequences. Choose the encoding that matches the receiving system.

4. “Address-book postal codes mix 123-4567 and １２３−４５６７”

Standard fix: =ASC() to half-width-everything. Search keys like postal codes and phone numbers are best stored half-width — SQL LIKE and join performance improve.

Summary

• Quick: =ASC() / =JIS() worksheet functions
• Targeted: Find and Replace (Ctrl + H)
• Bulk / complex: Power Query
• Reusable: VBA macro

If you ever need to test the same conversion outside Excel (CSV pre-import, etc.), the half-width / full-width tool on this site produces results consistent with Excel’s ASC / JIS, useful for sanity-checking transformations before importing.