Excelで日本語のみURLエンコードする方法!関数の他VBAも!

Excelで日本語のみURLエンコードさせたいときはないでしょうか。

ExcelではURLエンコードをしてくれる関数が用意されていますが、半角記号など日本語以外の文字もエンコードされてしまい、扱い方が少々難しいです。

そんな中で困ることは、

・Excelで日本語のみURLエンコードするやり方がわからない。
・関数やVBAで効率的に行いたいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する
Excelで日本語のみURLエンコードする方法について
まとめます!


日本語のみURLエンコードするイメージ

日本語のみURLエンコードするイメージについて説明します。

このような形で日本語交じりのURLを用意し、日本語のみURLエンコードを行っていきます。

ただ、ExcelではENCODEURL関数が用意されいますのでこれをそのまま適用すると、

このように半角記号の部分もエンコードされてしまいます。

そこで関数にある条件を加えて、再度実行します。

はい、日本語のみエンコードされましたね。

さらに関数のほかにVBAでも同じ結果となる処理に加え、
改行している場合のURLエンコード処理にも対応していきたいと思います。

それでは早速試してみましょう!

関数で日本語のみURLエンコードする

関数で日本語のみURLエンコードする方法について説明をします。

方法はカンタンで、1文字づつ取り出し、半角記号であればそのまま出力、
半角記号でなければURLエンコードするといった条件を加えるだけです。

エンコードには

ENCODEURL関数

を使用します。

次は設定方法について説明をします。

Excelへ日本語交じりのURLを用意する

ExcelのA2セルへ日本語交じりのURLを入力しましょう。

サンプルでは隣のC2セルにエンコードされた結果を出力していきます。

入力する関数は以下となります。

=TEXTJOIN(“”,TRUE,IF(CODE(MID(A2,SEQUENCE(LEN(A2)),1))>126,ENCODEURL(MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1)))

C2セルへ入れてみましょう。

この時点で結果が表示されたかと思います。

次は各機能について説明をします。

関数で文字列から半角記号かどうか判定する

まず、セルに入力されている文字を一文字づつ取り出していきます。

使う関数はSEQUENCE関数で、連続した配列を作成する関数です。
その値にLEN関数でB2の文字数を取得し、その分の配列を作成します。

A2の値は23文字ですので、23個分の配列ができる形となります。

=SEQUENCE(LEN(A2))

次にMID関数を使い、抽出したい文字を指定します。
MID関数は取得開始位置の中間指定が可能な関数です。

以下のように指定することで抽出したい文字を指定します。

MID(文字列, 開始位置, 文字数)

第二引数の開始位置に配列を指定すると配列の値を取得し、その値を位置番号として文字列の文字を取得できます。

MID関数の第二引数に「SEQUENCE(LEN(A2))」、文字数は1文字を指定します。

=MID(B2,SEQUENCE(LEN(A2)),1)

MID関数で取得した文字列から1文字づつ取り出し、CODE関数で半角記号かどうか判定します。

CODE関数とは指定した文字列の文字コード番号を返す関数です。

タカヒロ
タカヒロ
通常文字コード進数は16進数となりますが、CODE関数のコード番号は10進数となりますのでご注意ください。

半角記号のコードはコード表の33番から47番にありますが、
さらに半角の「:,?」など半角数値に含まれ、「{}~」などの記号はその他記号の126番以下にありますので、
126を基準に半角記号の判定をおこなっていきます。

文字の種類 CODE範囲-開始 CODE範囲-終わり
半角記号 33 47
半角数字 48 64
半角英字(大文字) 65 90
半角英字(小文字) 97 122
半角記号その他 123 126
半角カタカナ 161 223
全角数字 9007 9018
ひらがな 9249 9331
全角カタカナ 9505 9590
全角漢字 12321 29734

次に半角記号である場合はそのままの値、半角記号ない場合はURLエンコードをしていきます。

書式配下の通りです。

ENCODEURL(文字列)

文字列に半角記号でない文字を割り当ててエンコードされた文字を取得していきます。

最後に出てきた結果をTEXTJOIN関数で結合していきます。

TEXTJOIN関数の構文は以下の通りです。

TEXTJOIN (区切り記号, 空の文字列を無視するかどうか, 結合する文字1,・・・,文字列252)

サンプルでは区切り記号に空文字を指定し、空の文字は無視、という条件にしています。

=TEXTJOIN(“”,TRUE,<判定文字>1,<判定文字>2,・・・)

ここまでの処理をまとめますと、

入力する関数は以下となります。

=TEXTJOIN(“”,TRUE,IF(CODE(MID(A2,SEQUENCE(LEN(A2)),1))>126,ENCODEURL(MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1)))

C2セルへ入れてみましょう。

はい、日本語のみURLエンコードされていますね。

URLエンコードを関数で処理した場合の問題点

関数を利用したURLエンコード処理について紹介しましたが、問題点があります。
それは改行コードを含む文字列であった場合、すべてをURLエンコード処理してしまうことです。

このような形になります。

改行コードを判定して処理を分けるようにすればよいかと思いますが、
セルの関数で実現するにはコードが複雑になるので限界があります。

そこで、VBAを利用し、関数だけでは難しかった
細かいところに手が届く処理を加えて改行とURLを判定しながらURLエンコード処理をしていきましょう。



VBAで日本語のみURLエンコードする

早速、VBAで日本語のみURLエンコードしてみます。

処理結果は関数のものと同様になります。

さらに、関数の時に問題があった改行付き文字列の場合、すべてエンコードしてしまうことを回避するようにしていますのでこちらを確認してみましょう。

データはこちらになります。
URL以外にも改行していろいろ入れてみてください。

以下サンプルコードです。

Sub 日本語のみURLエンコードする()


'URLエンコードの条件を定義します。
'「http」を指定。「http」や「https」に該当。
Const myURLKye = "http"

Dim objApp As New Excel.Application
Dim arrUrlLists As Variant
Dim strURL As Variant
 

'URLエンコード対象の文字列を取得し、改行コードで分割、配列に格納します。
arrUrlLists = Split(Range("A2"), vbLf)


For Each strURL In arrUrlLists

    'URLを含む文字列かどうかを判定します。
    If InStr(strURL, myURLKye) Then
    
        '一文字づつ取り出し、半角記号以外をURLエンコードします。
        For k = 1 To Len(strURL)
            strMoji = Mid(strURL, k, 1)
            If strMoji Like "[ -/:-@\[-~]" Then
               strMojiTemp = strMojiTemp & strMoji
            Else
               strMojiTemp = strMojiTemp & objApp.WorksheetFunction.EncodeURL(strMoji)
            End If
        Next

        'URLエンコード後の文字列を結合していきます。
        If strEncodeMoji = "" Then
            strEncodeMoji = strMojiTemp
        Else
            strEncodeMoji = strEncodeMoji & vbLf & strMojiTemp
        End If
    Else
    
       'URLエンコード対象でない場合はそのまま結合していきます。
        If strEncodeMoji = "" Then
            strEncodeMoji = strURL
        Else
            strEncodeMoji = strEncodeMoji & vbLf & strURL
        End If
    
    End If
Next

'URLエンコード処理をおこなった文字列を出力します。
Range("C2") = strEncodeMoji


End Sub

VBAの設定箇所

VBAの設定箇所は以下の通りです。

URLエンコードの条件を定義します。
「http」を指定すると「http」や「https」に該当します。

Const myURLKye = “http”

URLエンコード対象の文字列があるセルを指定します。

arrUrlLists = Split(Range(“A2”), vbLf)

URLエンコード処理後の出力先セルを指定します。

Range(“C2”) = strEncodeMoji

VBAを実行する

実装して実行してみましょう。

はい!
改行つき文字列であってもURLの日本語のみURLエンコードできていますね。

VBAの説明

VBAの処理内容について説明します。

URLエンコード対象の文字列を取得し、改行コードで分割、配列に格納します。

arrUrlLists = Split(Range(“A2”), vbLf)

配列に入っている文字列を1個づつ抜き出します。

For Each strURL In arrUrlLists

URLを含む文字列かどうかを判定します。

If InStr(strURL, myURLKye) Then

一文字づつ取り出し、半角記号以外をURLエンコードします。

For k = 1 To Len(strURL)
strMoji = Mid(strURL, k, 1)
If strMoji Like “[ -/:-@\[-~]” Then

URLエンコード後の文字列を結合していきます。

If strEncodeMoji = “” Then
strEncodeMoji = strMojiTemp
Else
strEncodeMoji = strEncodeMoji & vbLf & strMojiTemp
End If

URLエンコード対象でない場合はそのまま結合していきます。

If strEncodeMoji = “” Then
strEncodeMoji = strURL
Else
strEncodeMoji = strEncodeMoji & vbLf & strURL
End If

URLエンコード処理をおこなった文字列を出力します。

Range(“C2”) = strEncodeMoji



VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。


さいごに

いかがでしょうか。

今回は、
Excelで日本語のみURLエンコードする方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。



コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です