カタカナは全角で英数字は半角に変換【エクセル関数・マクロ】

全角を半角、半角を全角に変換する関数

JIS関数とASC関数はとても便利な関数なのでよく使用していますが、いまだにどっちがどっちなのか覚えられません。

半角を全角にする関数

＝JIS(参照セル)

全角を半角にする関数

＝ASC(参照セル)

カタカナは全角で英数字は半角にする

関数で変換する方法が分からなかったのでマクロを使用します。マクロも詳しくはないのでアナログな使い方かもしれませんが、以下のように「１(全角)を1(半角)に置き換える」という処理を全ての英数字の分だけ作っています。

当初はアルファベットの大文字が小文字に（又は小文字が大文字に）変換されていたので、大文字と小文字を区別するために「MatchCase:=True」を追加しました。

Sub 変換()

Dim 選択セル As Range
For Each 選択セル In Selection
With Selection
.Replace What:="１", Replacement:="1", LookAt:=xlPart, MatchCase:=True

End With
Next 選択セル
End Sub

マクロの内容

選択したセルの「数字・アルファベット(小文字)・アルファベット(大文字)」を全角から半角に変換するマクロのため、カタカナは事前にJISで全角にしておく必要があります。逆に考えると、事前にASCで文字列を半角にして、カタカナ半角をの半角を全角に変換するマクロを使用するのも有効かと思います。

例えば住所などの地名には「全角が正解・半角が正解」という文字があると思うので、目的に応じてプラスアルファの事前・事後処理が必要になる可能性もあります。

Sub 変換()

Dim 選択セル As Range
For Each 選択セル In Selection
With Selection
.Replace What:="１", Replacement:="1", LookAt:=xlPart, MatchCase:=True
.Replace What:="２", Replacement:="2", LookAt:=xlPart, MatchCase:=True
.Replace What:="３", Replacement:="3", LookAt:=xlPart, MatchCase:=True
.Replace What:="４", Replacement:="4", LookAt:=xlPart, MatchCase:=True
.Replace What:="５", Replacement:="5", LookAt:=xlPart, MatchCase:=True
.Replace What:="６", Replacement:="6", LookAt:=xlPart, MatchCase:=True
.Replace What:="７", Replacement:="7", LookAt:=xlPart, MatchCase:=True
.Replace What:="８", Replacement:="8", LookAt:=xlPart, MatchCase:=True
.Replace What:="９", Replacement:="9", LookAt:=xlPart, MatchCase:=True

.Replace What:="ａ", Replacement:="a", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｂ", Replacement:="b", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｃ", Replacement:="c", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｄ", Replacement:="d", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｅ", Replacement:="e", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｆ", Replacement:="f", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｇ", Replacement:="g", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｈ", Replacement:="h", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｉ", Replacement:="i", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｊ", Replacement:="j", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｋ", Replacement:="k", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｌ", Replacement:="l", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｍ", Replacement:="m", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｎ", Replacement:="n", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｏ", Replacement:="o", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｐ", Replacement:="p", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｑ", Replacement:="q", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｒ", Replacement:="r", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｓ", Replacement:="s", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｔ", Replacement:="t", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｕ", Replacement:="u", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｖ", Replacement:="v", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｗ", Replacement:="w", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｘ", Replacement:="x", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｙ", Replacement:="y", LookAt:=xlPart, MatchCase:=True
.Replace What:="ｚ", Replacement:="z", LookAt:=xlPart, MatchCase:=True

.Replace What:="Ａ", Replacement:="A", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｂ", Replacement:="B", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｃ", Replacement:="C", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｄ", Replacement:="D", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｅ", Replacement:="E", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｆ", Replacement:="F", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｇ", Replacement:="G", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｈ", Replacement:="H", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｉ", Replacement:="I", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｊ", Replacement:="J", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｋ", Replacement:="K", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｌ", Replacement:="L", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｍ", Replacement:="M", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｎ", Replacement:="N", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｏ", Replacement:="O", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｐ", Replacement:="P", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｑ", Replacement:="Q", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｒ", Replacement:="R", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｓ", Replacement:="S", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｔ", Replacement:="T", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｕ", Replacement:="U", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｖ", Replacement:="V", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｗ", Replacement:="W", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｘ", Replacement:="X", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｙ", Replacement:="Y", LookAt:=xlPart, MatchCase:=True
.Replace What:="Ｚ", Replacement:="Z", LookAt:=xlPart, MatchCase:=True

End With
Next 選択セル
End Sub