関数を使っていますが全角数字だけの変換に比べて厄介なこともあり、かなり長い式になります。
- UPPER/LOWER関数やVALUE関数を使って英数字かどうかを判定し、それらにだけASC関数を適用すれば全角英数字だけを半角に変換できます。
- INDIRECT関数に文字を組み込み、参照可能となるかどうかによって英数字を判定する方法もあります。
手順
UPPER/LOWER/VALUE関数を使う方法
画像ではB列にいくつかの文字列が入力されています。
C3セルに式を入力しB3セルの文字列のうち全角英数字だけを半角にしています。他の行も同様です。
C3セル
=LET( x,MID(B3,SEQUENCE(LEN(B3)),1), CONCAT(IF(EXACT(UPPER(x),LOWER(x))*ISERROR(VALUE(x)),x,ASC(x))) )
考え方としては正統派ですが下記の方法より式が長くなります。
SEQUENCE/MID関数を使い文字列を1文字ずつ分割し、LET関数でxという名前を付けています。
そして「EXACT(UPPER(x),LOWER(x))」によりxが英字かどうかを、「ISERROR(VALUE(x))」によりxが数字かどうかを判定しています(ともに全角・半角を問わず英字や数字ならFALSEとなります)。これらを「*」でつなぐことでANDの判定をし、IF関数により英字でもなく数字でもない字はそのまま返し、英字または数字についてはASC関数を適用しています。最後にCONCAT関数で連結して完成です。
「*」を使わず「AND(EXACT(~」としてしまうとうまくいきません。これはAND関数がスピルしない(xの全体に対し1つの戻り値しか返さない)からで、FILTER関数にAND関数で複数条件を付けてもうまくいかないのと同じです。
なお、画像からわかるように数値は文字列に変換され、日付はシリアル値を文字列に変換したものとなります。
AREAS/INDIRECT関数を使う方法
内容的には上記の例と同じで、結果も全く同じです。
使用している関数が若干異なります。
C3セル
=LET( x,MID(B3,SEQUENCE(LEN(B3)),1), CONCAT(IF(ISERR(AREAS(INDIRECT("A"&x&"1"))),x,ASC(x))) )
アプローチが異質というか実験的なのでおすすめはできませんが、若干式が短くなります。
まずSEQUENCE/MID関数を使い文字列を1文字ずつ分割し、LET関数でxという名前を付けています。
そして「ISERR(AREAS(INDIRECT("A"&x&"1")))」により、xが英数字であるかを判定しています。例としてxが英数字でない「あ」の場合は「"A"&x&"1"」の部分が「"Aあ1"」となるので結果的にTRUE(エラー)になり、xが英数字である「a」の場合は「Aa1」となり結果的にFALSE(エラーでない)になります。INDIRECTは「"Aa1"」だろうが「"A01"」だろうが参照してくれる優れものなので、これを利用して英数字を判定しています(全角か半角かは区別してません)。あとは英数字にだけASC関数を適用して最後にCONCAT関数で連結しています。
AREAS関数を使わず「ISERR(INDIRECT(~))」でも済みそうですが、参照先がたまたまエラー値である場合にうまくいきませんので、参照可能であれば1を返すようにしています。
万一の備考として、INDIRECTの引数が「名前の定義」で定義された名前とかぶってしまうとうまくいかないことが想定されるものの、セル参照とみなされる名前は使えないので問題ないはずです。検証はしてませんが問題があれば「"A"&x&"1"」の部分にもう少し手を加える必要があります。
MAP/LAMBDA/ISREF/INDIRECT関数を使う方法
こちらも内容的には上記の例と同じで、結果も全く同じです。
使用している関数が若干異なります。
C3セル
=CONCAT( MAP(MID(B3,SEQUENCE(LEN(B3)),1),LAMBDA(x,IF(ISREF(INDIRECT("A"&x&"1")),ASC(x),x))) )
上記の例において、そもそも有効な参照かどうかを判定するならISREF関数を使った方が簡単なのではないかと思われます。しかし残念なことにISREF関数はスピルしないので1文字ずつの判定ができません。
そこでMAP/LAMBDA関数を使って強制的にスピルさせています。CONCATをLAMBDA内に含めるとCONCATの効果も分割されてしまい機能しないので、MAPの外側に置いています。
全角数字のみ半角にする場合
全角数字だけを半角にする場合は最初の方法から英字の判定を除けばいいだけなので簡単です。次の記事で具体的に試しています。