本サイトにおける過去のVBAの記事でも、たびたび関数は登場しており、フォルダ内のファイル名一覧を取得したり、ファイルの存在の有無を確認したいときにつかえるDir関数などを取り上げたことがありました。
エクセルVBAには、このほかにも多数の関数がありますが、この記事では文字列操作に利用ができる関数をまとめて紹介していきたいと思います。マクロをつくる上で目の前の課題を解決するために役立つことがあれば幸いです。
プログラミングでどんなことができる？どういった仕事ができるの？
難しそうだけどわたしにできるか不安。
その悩みを現役のプロに無料で相談してみませんか？
利用者の9割以上が初心者、現役プロのメンター制度で挫折しない学習をサポート。
わからないことは気軽にチャットで質問、プロからの回答がスピーディーに得られる安心感。
すべてオンラインで、あなたのライフスタイルに合った学び方で受講ができる。プログラミングでプロを目指す人から、副業で成果をめざす人や、今の仕事でスキルアップしたい人などの幅広いニーズへの対応できる豊富なコースあり。
まずは無料のキャリアカウンセリングであなたにぴったりなキャリアについて相談してみてください。
文字列とは
VBAの「文字列」とは、文字や記号などの連続したデータを表すデータ型のひとつです。これは、過去に変数の記事でも取り扱ったようにエクセルVBAに数あるデータ型のうち、文字列型(String)にあたります。
VBAに限らず、プログラミングやエクセルやシステムをつかった実務でも「文字列」を操作する場面はとても多く見かけます。 例えば、エクセルのワークシートに名前が入力された表Ａがあるとして、「〇〇 〇〇」と入力されている場合と「〇〇 〇〇」とスペースの表記が全角と半角でちがう場合に、半角から全角に、もしくはその逆にどちらかの書きかたを修正することを置換と呼びますが、こういった事象を容易に想像できるほど文字列の操作は多く使われるものだと言えます。
エクセルVBAでは、文字列を操作するためにあらかじめ様々な関数やメソッドが用意されています。この記事はエクセルVBAで文字列の操作に使われる関数やメソッドをサンプルマクロとともに紹介しています。
これらの関数やメソッドをあつかうことは、それほどむずかしいことではありませんが、とても汎用性が高いものと言えます。また、これらを組み合わせてつかうことでより柔軟にもとめた結果が得られるようになることもあります。文字列を自由に操作することができれば確実にスキルアップにつながり、マクロで解決ができる課題の範囲も広がることでしょう。
文字列の長さやバイト数を取得する関数
こちらで紹介するのは、文字列の長さを調べたり、バイト数を取得したりするときにつかえるものです。バイト数を取得してなにかの処理にいかすことは一般的な事務作業で考えることは多くないですが、文字数の制限をしたり、入力された文字数で処理を変えたりする場面では利用できます。
Len関数は文字列の文字数を返す
構文
Len(文字列式または変数名)
文字列式か変数名のいずれかを指定し、指定した文字列の文字数を返します。戻り値は長整数型(Long)となりますので、戻り値を変数に代入する場合は長整数型(Long)で宣言します。
Len関数で指定した、文字列式または変数名にNull（ヌル）が含まれている場合はNull（ヌル）が返します。(※Null（ヌル）はなにもない、データがからっぽという状態です。)
文字列の文字数を返すマクロ
サンプルコード1
Option Explicit
Sub operation_string01()
'Len関数でイミディエイトウィンドウに文字数を出力する
Debug.Print Len("")
'＝実行結果：0
Debug.Print Len("フシギダネ")
'＝実行結果：5
Debug.Print Len("フシギソウ" & Null)
'＝実行結果：5
Debug.Print Len("フシギバナ" + Null)
'＝実行結果：Null
'Len関数でイミディエイトウィンドウに変数に代入された文字列の文字数を出力する
Dim Sample_String1 As String _
: Sample_String1 = ""
Debug.Print Len(Sample_String1)
'＝実行結果：0
Dim Sample_String2 As String _
: Sample_String2 = "観自在菩薩 行深般若波羅蜜多時。照見五蘊 皆空。度一切苦厄。"
Debug.Print Len(Sample_String2)
'＝実行結果：30
Dim Sample_String3 As String _
: Sample_String3 = "舎利子。色不異空。空不異色。色即是空。空即是色。受想行識。亦復如是。" & Null
Debug.Print Len(Sample_String3)
'＝実行結果：34
Dim Sample_String4 As Variant _
: Sample_String4 = Null
Debug.Print Len(Sample_String2 + Sample_String4)
'＝実行結果：Null
End Sub
サンプルコード2
Option Explicit
Sub operation_string02()
'文字列を代入する変数を文字列型(String)で宣言する
Dim Inputyear As String
'インプットボックスメソッドでユーザーから入力された値を変数に代入する
'入力値が4文字でなかった場合はインプットボックス表示をくり返す。
Do
Inputyear = Application.InputBox _
("誕生日を西暦4ケタの数値で入力してください", "誕生した年を入力", Format(Date, "yyyy"), Type:=1)
Loop Until Len(Inputyear) = 4
'Len関数でイミディエイトウィンドウに変数に代入された文字列の文字数を出力する
Debug.Print "文字数は"; Len(Inputyear); "です。"
'＝実行結果：文字数は 4 です。
End Sub
LenB関数は文字列のバイト数を返す
構文
LenB(文字列式または変数名)
文字列式か変数名のいずれかを指定し、指定した文字列のバイト数を返します。LenB関数はこのバイトの数を返すため、戻り値は長整数型(Long)となります、戻り値を変数に代入する場合は長整数型(Long)で宣言します。文字列のバイト数について全角の文字では、2バイトになりますので、例えば「にほん」をLenB関数に指定した場合は6で「日本」では４がかえってきます。
※バイト数とはコンピュータにおける情報量の単位で、1バイトはコンピュータがあつかうデータの最小単位の8ビットです。日本語の文字におけるバイト数については、文字コードによってことなるため、必ず2バイトとならないケースがあります。むずかしい話になるため、ここでは詳しく述べることはできませんが気になる方は調べてみてください。
LenB関数で指定した、文字列式または変数名にNull（ヌル）が含まれている場合はNull（ヌル）が返します。(※Null（ヌル）はなにもない、データがからっぽという状態です。)
文字列のバイト数を返すマクロ
サンプルコード1
Option Explicit
Sub operation_string03()
'LenB関数でイミディエイトウィンドウに文字数を出力する
Debug.Print LenB("")
'＝実行結果：0
Debug.Print LenB("ポッポ")
'＝実行結果：6(Unicodeでは1文字のバイト数は文字によって異なる。全角日本語は3～8バイト)
Debug.Print LenB("ピジョン" & Null)
'＝実行結果：8(Unicodeでは1文字のバイト数は文字によって異なる。全角日本語は3～8バイト)
Debug.Print LenB("ピジョット" + Null)
'＝実行結果：Null
'LenB関数でイミディエイトウィンドウに変数に代入された文字列の文字数を出力する
Dim Sample_String1 As String _
: Sample_String1 = ""
Debug.Print LenB(Sample_String1)
'＝実行結果：0
Dim Sample_String2 As String _
: Sample_String2 = "観自在菩薩 行深般若波羅蜜多時。照見五蘊 皆空。度一切苦厄。"
Debug.Print LenB(Sample_String2)
'＝実行結果：60(Unicodeでは1文字のバイト数は文字によって異なる。全角日本語は3～8バイト)
Dim Sample_String3 As String _
: Sample_String3 = "舎利子。色不異空。空不異色。色即是空。空即是色。受想行識。亦復如是。" & Null
Debug.Print LenB(Sample_String3)
'＝実行結果：68(Unicodeでは1文字のバイト数は文字によって異なる。全角日本語は3～8バイト)
Dim Sample_String4 As Variant _
: Sample_String4 = Null
Debug.Print LenB(Sample_String2 + Sample_String4)
'＝実行結果：Null
End Sub
サンプルコード2
Option Explicit
Sub operation_string04()
'文字列を代入する変数を文字列型(String)で宣言する
Dim Inputyear As String
'インプットボックスメソッドでユーザーから入力された値を変数に代入する
'入力値が4文字でなかった場合はインプットボックス表示をくり返す。
Do
Inputyear = Application.InputBox _
("誕生日を西暦4ケタの数値で入力してください", "誕生した年を入力", Format(Date, "yyyy"), Type:=1)
Loop Until LenB(Inputyear) = 8 'バイト数
'Len関数でイミディエイトウィンドウに変数に代入された文字列のバイト数を出力する
Debug.Print "バイト数は"; LenB(Inputyear); "です。"
'＝実行結果：バイト数は ８ です。
End Sub
文字列の置換や検索をする関数
こちらで紹介する関数は指定した文字列の一部を置き換えてくれるものです。
記事の冒頭でも記載したような名前が入力で「〇〇 〇〇」と「〇〇 〇〇」のように全角と半角スペースの両方が使われている場合、いずれかに統一するなどの使い方ができます。
Replace関数は文字列の一部を別の文字列で置換する
構文
Replace(対象文字列または変数, 検索文字列, 置換文字列, [開始位置, [置換回数, [比較種別]]])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象文字列 (expression) |
必須 | – | 置換をする対象の文字列または変数 |
| 検索文字列 (find) |
必須 | – | 置換前の文字列 |
| 置換文字列 (replace) |
必須 | – | 置換後の文字列 |
| 開始位置 (start) |
省略可能 | 対象文字列の開始位置(1) | 対象の文字列の何文字目から検索を開始するかを数値で指定 |
| 置換回数 (count) |
省略可能 | すべて置換(-1) | 置換する回数を数値で指定 |
| 比較種別 (compare) |
省略可能 | vbBinaryCompare (0) | Option Compareに従う(-1),バイナリ(0),テキスト(1)のなかから比較種別を指定。 設定値は定数もしくは数値(-1,0,1)のいずれでも指定可能 |
比較種別については、vbUseCompareOption(-1)、Option Compare ステートメントの設定に従って比較する、vbBinaryCompare(0)はバイナリ(大文字と小文字、全角と半角、ひらがなとカタカナを区別)比較、vbTextCompare(1)はテキスト比較の種別となります。
文字列の一部を別の文字列で置換するマクロ
サンプルコード
Option Explicit
Sub operation_string05()
'Replace関数で文字列を置きかえて出力する
Debug.Print Replace("ナゾノクサ", "クサ", "ハナ")
'＝実行結果：ナゾノハナ
'Replace関数で文字列を置きかえて出力する(バイナリの場合はひらがなとカタカナを区別)
Debug.Print Replace("ナゾノクサ", "くさ", "ハナ", compare:=vbBinaryCompare)
'＝実行結果：ナゾノクサ
'Replace関数で文字列を置きかえて出力する(テキストの場合はひらがなとカタカナを区別しない)
Debug.Print Replace("ナゾノクサ", "くさ", "ハナ", compare:=vbTextCompare)
'＝実行結果：ナゾノハナ
'Replace関数で文字列を置きかえて出力する(バイナリの場合は大文字と小文字を区別)
Debug.Print Replace("NAZONOKUSA", "kusa", "HANA", compare:=vbBinaryCompare)
'＝実行結果：NAZONOKUSA
'Replace関数で文字列を置きかえて出力する(テキストの場合は大文字と小文字を区別しない)
Debug.Print Replace("NAZONOKUSA", "kusa", "HANA", compare:=vbTextCompare)
'＝実行結果：NAZONOHANA
'Replace関数で文字列を置きかえて出力する(開始位置あり)
Debug.Print Replace("なんだかんだと聞かれたら答えてあげるのが世の情け", "答えて", "こたえて", 13)
'＝実行結果：こたえてあげるのが世の情け
'Replace関数で文字列を置きかえて出力する(開始位置・置換回数・比較種別の指定あり)
Debug.Print Replace("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "世界", "宇宙", 1, 1, 1)
'＝実行結果：宇宙の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役
'Replace関数で文字列を置きかえて出力する(開始位置・置換回数・比較種別の指定あり)
Debug.Print Replace("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "世界", "宇宙", 1, 2, 1)
'＝実行結果：宇宙の破壊を防ぐため宇宙の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役
'Replace関数で文字列を置きかえて出力する(対象文字列が空白)
Debug.Print Replace("", "世界", "宇宙")
'＝実行結果：”” 空白の文字列
'Replace関数で文字列を置きかえて出力する(開始位置が対象文字列の文字数より大きい)
Debug.Print Replace("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "世界", "宇宙", 45)
'＝実行結果：”” 空白の文字列
'Replace関数で文字列を置きかえて出力する(検索文字列が空白)
Debug.Print Replace("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "", "宇宙")
'＝実行結果：世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役
'Replace関数で文字列を置きかえて出力する(置換文字列が空白)
Debug.Print Replace("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "世界の", "")
'＝実行結果：破壊を防ぐため平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役
'Replace関数で文字列を置きかえて出力する(置換回数が0)
Debug.Print Replace("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "世界", "宇宙", 1, 0, 1)
'＝実行結果：世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役
'Replace関数で文字列を置きかえて出力する(対象文字列がNull)
'Debug.Print Replace(Null, "あれ", "これ")
'＝実行結果：エラー
'Replace関数で文字列を置きかえて出力する(対象文字列の変数がNull)
'Dim StringA: StringA = Null
'Debug.Print Replace(StringA, "あれ", "これ")
'＝実行結果：エラー
End Sub
InStr関数は文字列の中から指定した文字列を検索し、最初に見つかった位置を返す
検索文字列が、対象文字列のなかで最初に現れる位置をVariant（Long）型で返します。戻り値を変数に代入する場合はバリアント(Variant)型で宣言します。
構文
InStr([開始位置], 対象文字列, 検索文字列, [比較種別])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 開始位置 (start) |
省略可能 | 対象文字列の開始位置(-1) | 対象の文字列の何文字目から検索を開始するかを数値で指定 |
| 対象文字列 (string1) |
必須 | – | 検索をする対象の文字列または変数 |
| 検索文字列 (string2) |
必須 | – | 検索する文字列 |
| 比較種別 (compare) |
省略可能 | vbBinaryCompare (0) | Option Compareに従う(-1),バイナリ(0),テキスト(1)のなかから比較種別を指定。 設定値は定数もしくは数値(-1,0,1)のいずれでも指定可能 |
比較種別については、vbUseCompareOption(-1)、Option Compare ステートメントの設定に従って比較する、vbBinaryCompare(0)はバイナリ(大文字と小文字、全角と半角、ひらがなとカタカナを区別)比較、vbTextCompare(1)はテキストでの比較種別となります。なお、比較種別を指定した場合は、開始位置も指定しなければエラーが発生します。
引数である開始位置について誤解しやすい点として、InStr関数で返ってくる開始位置は、引数の開始位置から数えられるわけではなく、対象文字列の最初からの位置を返します。
例えば以下の場合の実行結果は「5」が戻ります。
InStr(5,“1234567890”,“5”)
対象文字列の開始位置｢5｣番目から検索文字列｢5｣がみつかった最初の位置を返しますが、引数で指定した開始位置に関わらず、対象文字列の最初からの位置を返します。そのため、上記の検索文字列を「0」に変更すると「10」が返ってきます。
戻り値について
対象文字列か検索文字列のいずれかが、Nullであった場合は戻り値はNullとなります。
対象文字列が「””」の0文字であった場合、0が戻ります。一方、検索文字列が「””」の0文字であった場合は引数の開始位置に指定した数値が戻ります。開始位置の指定値が、検索文字列の開始位置よりも大きい値の場合、0が戻ります。
文字列をまえから検索して最初に見つかった位置を返すマクロ
サンプルコード
Option Explicit
Sub operation_string06()
'Instr関数で検索文字列の開始位置を返す
Debug.Print InStr("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー")
'＝実行結果：32
'Instr関数で検索文字列の開始位置を返す(開始位置を指定)
Debug.Print InStr(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー")
'＝実行結果：32
'Instr関数で検索文字列の開始位置を返す(比較種別を指定した場合は、開始位置も必須になる)
Debug.Print InStr(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー", 0)
'＝実行結果：32
'Instr関数で検索文字列の開始位置を返す(比較種別を指定しているが、開始位置の指定なし)
'Debug.Print InStr("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー", 0)
'＝実行結果：エラー
'Instr関数で検索文字列の開始位置を返す(対象文字列が0文字)
Debug.Print InStr("", "ラブリー・チャーミー")
'＝実行結果：0
'Instr関数で検索文字列の開始位置を返す(検索文字列が0文字は開始位置が返る)
Debug.Print InStr(10, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "")
'＝実行結果：10
'Instr関数で検索文字列の開始位置を返す(対象文字列がNull)
Debug.Print InStr(Null, "ラブリー・チャーミー")
'＝実行結果：Null
'Instr関数で検索文字列の開始位置を返す(対象文字列の変数がNull)
Dim StringA: StringA = Null
Debug.Print InStr(StringA, "ラブリー・チャーミー")
'＝実行結果：Null
'Instr関数で検索文字列の開始位置を返す(検索文字列がNull)
Debug.Print InStr("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", Null)
'＝実行結果：Null
'Instr関数で検索文字列の開始位置を返す(検索文字列の変数がNull)
Dim StringB: StringB = Null
Debug.Print InStr("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", StringB)
'＝実行結果：Null
'Instr関数で検索文字列の開始位置を返す(開始位置が検索文字列以降を指定)
Debug.Print InStr(33, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー")
'＝実行結果：0(ラブリー・チャーミーの開始位置は32文字目)
'Instr関数で検索文字列の開始位置を返す(比較種別はバイナリモードを指定)
Debug.Print InStr(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "らぶりー・ちゃーみー", vbBinaryCompare)
'＝実行結果：0(ひらがなとカタカナは区別される)
'Instr関数で検索文字列の開始位置を返す(比較種別はテキストモードを指定)
Debug.Print InStr(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "らぶりー・ちゃーみー", vbTextCompare)
'＝実行結果：32
End Sub
InStr関数は、対象文字列のなかから検索文字列の開始位置を返す関数です。この性質を応用することで、検索文字列が範囲内のどのセルに含まれているかを検索するマクロをつくることができます。
詳しい手順については、こちらの記事をご覧ください。
＼エクセルVBA｜FindメソッドやInStr関数で文字列を検索する／
InStrB関数は文字列の中から検索文字列の最初に見つかった位置をバイト数で返す
InStr関数が検索文字列の開始位置を返すものであれば、こちらのInStrB関数は開始位置をバイト数で返すものです。構文や使いかたの注意点などは、InStr関数と同じですが、結果として返ってくるものについては違和感をもちやすいので触れておきます。
InStr(“フシギダネ”,“ギ”)で結果を出力すると検索文字列の「ギ」は「3」文字目から開始しているため、「3」が戻ります。これをInStrB(“フシギダネ”,“ギ”)にすると、各文字が2バイト文字であることから6バイトで「6」が返ってくるように考えてしまいそうですが、結果は「5」となります。2バイト文字の「ギ」は5バイト目から開始されていると考えると良いかもしれません。
そもそもInStrB関数を利用する頻度や場面から考えても、そんなに困っている人はいないかもしれませんが、ものすごく稀に不思議に思われる人がいるかもしれませんので書きのこしておきます。
文字列の中から検索文字列の最初に見つかった位置をバイト数で返すマクロ
サンプルコード
Option Explicit
Sub operation_string06()
'InstrB関数で検索文字列の開始位置を返す
Debug.Print InStrB("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー")
'＝実行結果：63(対象文字列は全て1文字が2バイトであるため、検索文字列の開始位置は63バイト)
'InstrB関数で検索文字列の開始位置を返す(開始位置を指定)
Debug.Print InStrB(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー")
'＝実行結果：63(対象文字列は全て1文字が2バイトであるため、検索文字列の開始位置は63バイト)
'InstrB関数で検索文字列の開始位置を返す(比較種別を指定した場合は、開始位置も必須になる)
Debug.Print InStrB(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー", 0)
'＝実行結果：63
'InStrB関数で検索文字列の開始位置を返す(比較種別を指定しているが、開始位置の指定なし)
'Debug.Print InStrB("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー", 0)
'＝実行結果：エラー
'InStrB関数で検索文字列の開始位置を返す(対象文字列が0文字)
Debug.Print InStrB("", "ラブリー・チャーミー")
'＝実行結果：0
'InStrB関数で検索文字列の開始位置を返す(検索文字列が0文字は開始位置が返る)
Debug.Print InStrB(10, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "")
'＝実行結果：10
'InStrB関数で検索文字列の開始位置を返す(対象文字列がNull)
Debug.Print InStrB(Null, "ラブリー・チャーミー")
'＝実行結果：Null
'InStrB関数で検索文字列の開始位置を返す(対象文字列の変数がNull)
Dim StringA: StringA = Null
Debug.Print InStrB(StringA, "ラブリー・チャーミー")
'＝実行結果：Null
'InStrB関数で検索文字列の開始位置を返す(検索文字列がNull)
Debug.Print InStrB("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", Null)
'＝実行結果：Null
'InStrB関数で検索文字列の開始位置を返す(検索文字列の変数がNull)
Dim StringB: StringB = Null
Debug.Print InStrB("世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", StringB)
'＝実行結果：Null
'InStrB関数で検索文字列の開始位置を返す(開始位置が検索文字列以降を指定)
Debug.Print InStrB(64, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "ラブリー・チャーミー")
'＝実行結果：0(ラブリー・チャーミーの開始位置は63バイト)
'InStrB関数で検索文字列の開始位置を返す(比較種別はバイナリモードを指定)
Debug.Print InStrB(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "らぶりー・ちゃーみー", vbBinaryCompare)
'＝実行結果：0(ひらがなとカタカナは区別される)
'InStrB関数で検索文字列の開始位置を返す(比較種別はテキストモードを指定)
Debug.Print InStrB(1, "世界の破壊を防ぐため世界の平和を守るため、愛と真実の悪を貫く、ラブリー・チャーミーな敵役", "らぶりー・ちゃーみー", vbTextCompare)
'＝実行結果：0(63ではなく、見つからない原因は調査中)
End Sub
InStrRev関数は対象文字列のなかで検索文字列を後ろから検索し、最初に見つかった位置を返す
InStr関数が対象文字列のなかの検索文字列を前から検索するのに対して、InStrRev関数は対象文字列の後ろから検索文字列をさがして最初にみつかった位置を返します。
構文
InstrRev(対象文字列, 検索文字列, [開始位置, [ 比較種別]])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象文字列 (stringcheck) |
必須 | – | 検索をする対象の文字列または変数 |
| 検索文字列 (stringmatch) |
必須 | – | 検索する文字列 |
| 開始位置 (start) |
省略可能 | 対象文字列の開始位置(-1) | 対象の文字列の何文字目から検索を開始するかを数値で指定 |
| 比較種別 (compare) |
省略可能 | vbBinaryCompare (0) | Option Compareに従う(-1),バイナリ(0),テキスト(1)のなかから比較種別を指定。 設定値は定数もしくは数値(-1,0,1)のいずれでも指定可能 |
引数を指定する順番がInStr関数の書きかたと異なる点については注意が必要です。また、こちらの関数では開始位置の捉えかたが誤解しやすいと感じるので補足しておきます。
InStrRev(“1234567890”, “5”, 5)
具体例として上の関数についてですが、対象文字列「1234567890」の10文字から、検索文字列の「5」の位置を返したいわけですが、引数の開始位置は「5」で指定した場合の考えかたは以下のとおりです。
InStrRev関数の検索の向きは後ろからですが、戻り値の数値については対象文字列の前から数えたものです。
戻り値について
検索文字列がみつからなかった場合の戻り値は「0」です。
対象文字列や検索文字列がNullであった場合は、エラーになります。対象文字列の長さが0のときの戻り値は「0」ですが、検索文字列の長さが0のときは、戻り値は開始位置となります。開始位置が対象文字列の文字数よりも大きい場合の戻り値は「0」です。
文字列のうしろから検索して最初に見つかった位置を返すマクロ
サンプルコード
Option Explicit
Sub operation_string07()
'InStrRev関数は検索文字列の開始位置を対象文字列の後ろから探して最初の位置を返す
Debug.Print InStrRev("人民の人民による人民のための政治", "人民")
'＝実行結果：9
'InStrRev関数(検索文字列がみつからない)
Debug.Print InStrRev("人民の人民による人民のための政治", "権力者")
'＝実行結果：0
'InStrRev関数(開始位置を8で指定)
Debug.Print InStrRev("人民の人民による人民のための政治", "人民", 8)
'＝実行結果：4
'InStrRev関数(比較種別をバイナリモードに指定)
Debug.Print InStrRev("人民の人民による人民のための政治", "ヨル", compare:=vbBinaryCompare)
'＝実行結果：0
'InStrRev関数(比較種別をテキストモードに指定)
Debug.Print InStrRev("人民の人民による人民のための政治", "ヨル", Compare:=vbTextCompare)
'＝実行結果：7
'InStrRev関数(対象文字列の文字列の長さが0)
Debug.Print InStrRev("", "人民")
'＝実行結果：0
'InStrRev関数(検索文字列の文字列の長さが0)
Debug.Print InStrRev("人民の人民による人民のための政治", "")
'＝実行結果：16(開始位置を指定した場合は開始位置になる)
'InStrRev関数(開始位置が対象文字列の文字数よりも大きい)
Debug.Print InStrRev("人民の人民による人民のための政治", "人民", 17)
'＝実行結果：0
'InStrRev関数(対象文字列がNull)
'Debug.Print InStrRev(Null, "人民")
'＝実行結果：エラー
'InStrRev関数(対象文字列がNull)
'Debug.Print InStrRev("人民の人民による人民のための政治", Null)
'＝実行結果：エラー
End Sub
エクセル・ワード・パワーポイントのオンライン学習
料金負担が安いのになんど見ても追加費用は不要！
安心の環境でしっかりとスキルをつけたいならPCHack
文字列の結合や分割をする関数
ここでは、いくつかの文字列が代入された配列を結合、またその逆の文字列を分割して配列を作成する関数を紹介します。
Join関数は文字列の配列を区切り文字で結合する
配列のなかに代入されている文字列を結合することができます。
Join関数は1次元配列に含まれる文字列を結合します。
1次元配列に文字列型(String)、数値型(Integer)、小数型(Double)、日付型(Date)、真偽型(Boolean)、バリアント型(variant)の値が代入されていたとしても、値が文字列に変換できるものであれば実行は可能です。
例えば、真偽型(Boolean)の「True」や「False」は文字列の「”False”」としてJoin関数の戻り値として出力されます。ただし、文字列に変更できないオブジェクト型が1次元配列に含まれる場合はエラーが発生します。
構文
Join(1次元配列, [ 区切り記号 ])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 1次元配列 (sourcearray) |
必須 | – | 文字列を含む1次元配列 |
| 区切り記号 (delimiter) |
省略可能 | 空白文字” “ | 文字列を結合するときの区切り文字を指定することができる。 区切り記号が長さ0の文字列””の指定であれば、区切り記号なしの結合をする |
文字列の配列を区切り文字で結合するマクロ
サンプルコード
Option Explicit
Sub operation_string08()
'Join関数は配列の文字列を結合した文字列を返す。
'Array関数で作成した1次元配列(区切り文字省略)
Debug.Print Join(Array("炎の呼吸", "壱ノ型", "不知火"))
'＝実行結果：炎の呼吸 壱ノ型 不知火
'1次元静的配列を宣言して値を代入する
Dim Sample_Arr(2)
Sample_Arr(0) = "音の呼吸"
Sample_Arr(1) = "壱ノ型"
Sample_Arr(2) = "轟"
'1次元配列(区切り文字あり)
Debug.Print Join(Sample_Arr, "-")
'＝実行結果：音の呼吸-壱ノ型-轟
'2次元静的配列を宣言して値を代入する
Dim Sample_Arr2(1 To 2, 1 To 3)
Sample_Arr2(1, 1) = "霞の呼吸"
Sample_Arr2(1, 2) = "壱ノ型"
Sample_Arr2(1, 3) = "垂天遠霞"
Sample_Arr2(2, 1) = "恋の呼吸"
Sample_Arr2(2, 2) = "壱ノ型"
Sample_Arr2(2, 3) = "初恋のわななき"
'2次元静的配列をIndex関数を使って1次元配列として抜き出す
Dim Sample_Arr3: Sample_Arr3 = WorksheetFunction.Index(Sample_Arr2, 1)
Dim Sample_Arr4: Sample_Arr4 = WorksheetFunction.Index(Sample_Arr2, 2)
'2次元配列から抜き出した1次元配列
Debug.Print Join(Sample_Arr3, "・")
'＝実行結果：霞の呼吸・壱ノ型・垂天遠霞
'2次元配列から抜き出した1次元配列
Debug.Print Join(Sample_Arr4, "/")
'＝実行結果：恋の呼吸/壱ノ型/初恋のわななき
'多用なデータ型の値をもった配列
Debug.Print Join(Array(12345, 0.1, "$A$1", #2/15/2016#, Empty, False), "|")
'＝実行結果：12345|0.1|$A$1|2016/02/15||False
Dim Sample_Arr5(5)
Sample_Arr5(0) = 12345
Sample_Arr5(1) = 0.1
Sample_Arr5(2) = "$A$1"
Sample_Arr5(3) = #2/15/2016#
Sample_Arr5(4) = Empty
Sample_Arr5(5) = False
'多用なデータ型の値を代入した変数の配列
Debug.Print Join(Sample_Arr5, "と")
'＝実行結果：12345と0.1と$A$1と2016/02/15ととFalse
End Sub
静的配列と動的配列のちがいとは？1次元・2次元配列の宣言や使い方については、他の記事で詳しく紹介しています。ワークシートのセルを配列にするサンプルマクロなども掲載しているため、あわせてご覧ください。
Split関数は区切り文字で区切られた文字列を配列に分割する
Split関数は文字列式から配列を返します。
簡単な例で言えば、「ショートケーキチョコレートケーキパウンドケーキシフォンケーキ」の文字列を「ケーキ」を区切りとして、配列の「ショート」「チョコレート」「パウンド」「シフォン」に分割する処理をします。
構文
Split(対象文字列, [区切り記号, [文字列の数,[比較種別]]])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象文字列 (expression) |
必須 | – | 文字列を含む1次元配列 |
| 区切り記号 (delimiter) |
省略可能 | 空白文字” “と同じあつかいになります。この場合は対象文字列を配列として返します。 | 文字列を結合するときの区切り文字を指定することができる。 区切り記号が長さ0の文字列””の指定であれば、対象文字列全体を含む要素が1つの配列を返します。 |
| 文字列の数 (limit) |
省略可能 | -1になる。すべての部分文字列が返されます。 | 対象文字列を分割する数を指定します。 |
| 比較種別 (compare) |
省略可能 | vbBinaryCompare (0) | Option Compareに従う(-1),バイナリ(0),テキスト(1)のなかから比較種別を指定。 設定値は定数もしくは数値(-1,0,1)のいずれでも指定可能 |
対象文字列が長さ0の文字列“”であった場合はエラーが発生します。一方、区切り記号が長さ0の文字列“”である、または省略されたときは対象文字列をそのまま配列に代入して返します。
文字列の数は対象文字列を分割する数、つまり配列の要素の数が指定できます。
例えば、以下のようにSplit関数を利用したとします。
Split(“係長課長部長本部長”, “長”, limit:=3)
これを説明すると「係長課長部長本部長」の文字列を「長」の区切り記号で3つに分割する処理をします。ただし、引数limitで分割をする数(配列の要素数)は、3を指定しているため、対象文字列の先頭から3つに分割します。
| 配列の要素 (インデックス) |
Split関数で代入された値 |
|---|---|
| 配列(0) | 係 |
| 配列(1) | 課 |
| 配列(2) | 部長本部長 |
配列(0)と配列(1)の文字列は区切り記号である「長」が取り除かれていますが、文字列の数に指定している3つに分ける指定があることから、さいごの配列(2)では区切り記号の「長」も含まれています。
区切り文字で区切られた文字列を配列に分割するマクロ
サンプルコード
Option Explicit
Sub operation_string09()
'Split関数は文字列から1次元配列を返す。
'戻り値の配列を受け取るための変数を宣言する
Dim Var1 As Variant
'Split関数を使用して戻り値を変数に代入(区切り記号に”曜日”を指定)
Var1 = Split("月曜日火曜日水曜日木曜日金曜日土曜日日曜日", "曜日")
Debug.Print Var1(0) '＝実行結果：月
Debug.Print Var1(1) '＝実行結果：火
Debug.Print Var1(2) '＝実行結果：水
Debug.Print Var1(3) '＝実行結果：木
Debug.Print Var1(4) '＝実行結果：金
Debug.Print Var1(5) '＝実行結果：土
Debug.Print Var1(6) '＝実行結果：日
'配列を受け取るための変数を宣言して受け取る
Dim Before_Var2 As String
Dim After_Var2 As Variant
'くり返し処理で使う変数(For Each)
Dim Val As Variant
'Split関数の引数として使う変数に値を代入する
Before_Var2 = "A型B型O型AB型"
'Split関数を使用して戻り値を変数に代入(区切り記号に”型”を指定)
After_Var2 = Split(Before_Var2, "型")
'くり返し処理をつかってAfter_Var2に代入した配列の値を全て出力する
For Each Val In After_Var2
Debug.Print Val '＝実行結果：A B O AB
Next Val
'戻り値の配列を受け取るための変数を宣言する
Dim Var3, Var4, Var5, Var6, Var7, Var8
'Split関数を使用して戻り値を変数に代入(区切り記号に””や省略だと対象文字列を返す)
Var3 = Split("あか色あお色みどり色", "")
Debug.Print Var3(0)
'＝実行結果：あか色あお色みどり色
'Split関数を使用して戻り値を変数に代入(文字列の数を3に指定)
Var4 = Split("係長課長部長本部長", "長", limit:=3)
Debug.Print Var4(0) '＝実行結果：係
Debug.Print Var4(1) '＝実行結果：課
Debug.Print Var4(2) '＝実行結果：部長本部長
'Split関数を使用して戻り値を変数に代入(比較種別をバイナリに指定)
Var5 = Split("すいそたんそちっそさんそふっそえんそ", "ソ", compare:=vbBinaryCompare)
Dim i As Integer
For i = 0 To UBound(Var5)
Debug.Print Var5(i) '=実行結果：すいそたんそちっそさんそふっそえんそ
Next i
'Split関数を使用して戻り値を変数に代入(比較種別をテキストに指定)
Var6 = Split("すいそたんそちっそさんそふっそえんそ", "ソ", compare:=vbTextCompare)
'くり返し処理をつかってVar6の要素を全て出力する
For i = 0 To UBound(Var6)
Debug.Print Var6(i) '=実行結果：すい たん ちっ さん ふっ えん
Next i
'Split関数を使用して戻り値を変数に代入(対象文字列が空文字)
'Var7 = Split("")
'Debug.Print Var7(0)
'=実行結果：エラー
'Split関数を使用して戻り値を変数に代入(区切り記号が空文字/省略も同じ)
Var8 = Split("あいうえお", "")
Debug.Print Var8(0)
'=実行結果：あいうえお
End Sub
なぜ、膨大な事務作業でも定時で退社できるのか。
実務をプロから学べる「ユースフル」の動画は永年見放題。Q&A機能で分からないを放置しないから安心。
詳しくは以下のリンクをクリック
文字列の抽出や挿入をする関数
対象文字列から一部を抽出、挿入する関数を紹介します。
Left関数は文字列の先頭から指定した文字数分の文字列を返す
Left関数は、指定した対象文字列を先頭から指定した文字数分の文字列を返します。
構文
Left(対象文字列, 長さ(数値))
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象文字列 (string) |
必須 | – | 処理のもととなる文字列 |
| 長さ (length) |
必須 | – | 処理で返す文字数を数値で指定する |
対象文字列がNullの場合は、Nullが返ります。長さが0であった場合は、長さ0の文字列“”が返されます。また、対象文字列以上の文字数を指定した場合は、対象文字列が返されます。
文字列の先頭から指定した文字数分の文字列を返すマクロ
サンプルコード
Option Explicit
Sub operation_string10()
'Left関数は対象文字列の先頭から指定した文字数分を抽出する
'Left関数で値を出力する
Debug.Print Left("2016年2月15日", 5)
'＝実行結果：2016年
'Left関数で値を出力する(変数)
Dim Sample_String1 As String: Sample_String1 = "荻野千尋"
Debug.Print "千と千尋のちひろの苗字は "; Left("荻野千尋", 2); " だった。"
'＝実行結果：千と千尋のちひろの苗字は 荻野 だった。
'Left関数で値を出力する(長さが0)
Debug.Print Left("ニギハヤミコハクヌシ", 0)
'＝実行結果：””(空文字)
'Left関数で値を出力する(長さが対象文字列数以上)
Debug.Print Left("ニギハヤミコハクヌシ", 10)
'＝実行結果：ニギハヤミコハクヌシ
'Left関数で値を出力する(対象文字列がNull)
Debug.Print Left(Null, 1)
'＝実行結果：Null
End Sub
Right関数は文字列の末尾から指定した文字数分の文字列を返す
Right関数は、指定した対象文字列を末尾からかぞえて指定の数の文字を含む文字列を返します。先に紹介したLeft関数が文字列の先頭から文字を抜き出すに対して、こちらは末尾から抜き出す処理をする関数です。
構文
Right (対象文字列, 長さ(数値))
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象文字列 (string) |
必須 | – | 処理のもととなる文字列 |
| 長さ (length) |
必須 | – | 処理で返す文字数を数値で指定する |
対象文字列がNullの場合は、Nullが返ります。長さが0であった場合は、長さ0の文字列“”が返されます。また、対象文字列以上の文字数を指定した場合は、対象文字列が返されます。
文字列の末尾から指定した文字数分の文字列を返すマクロ
サンプルコード
Option Explicit
Sub operation_string11()
'Right関数は対象文字列の右から指定した文字数分を抽出する
'Right関数で値を出力する
Debug.Print Right("1996年8月2日", 4)
'＝実行結果：8月2日
'Right関数で値を出力する(変数)
Dim Sample_String1 As String: Sample_String1 = "めいとこねこバス"
Debug.Print "めいは "; Right(Sample_String1, 5); " にキャラメルをあげました。"
'＝実行結果：めいはこねこバス にキャラメルをあげました。
'Right関数で値を出力する(長さが0)
Debug.Print Right("リュシータ・トエル・ウル・ラピュタ", 0)
'＝実行結果：””(空文字)
'Right関数で値を出力する(長さが対象文字列数以上)
Debug.Print Right("リュシータ・トエル・ウル・ラピュタ", 17)
'＝実行結果：リュシータ・トエル・ウル・ラピュタ
'Right関数で値を出力する(対象文字列がNull)
Debug.Print Right(Null, 1)
'＝実行結果：Null
End Sub
Mid関数は文字列の指定した位置から文字数分の文字列を返す
Mid関数は対象の文字列から指定した文字数を抽出することができます。
構文
Mid (対象文字列, 開始位置(数値),長さ(数値))
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象文字列 (string) |
必須 | – | 処理のもととなる文字列 |
| 開始位置 (start) |
必須 | – | 取り出す文字列の先頭の位置を指定 |
| 長さ (length) |
省略可能 | 開始位置からのすべての文字列を返す | 処理で返す文字数を数値で指定する |
対象文字列がNullであった場合はNullが返ります。開始位置が対象文字列の文字数よりも大きい場合、長さ0の文字列(“”)を返します。
文字列の指定した位置から文字数分の文字列を返すマクロ
サンプルコード1
Option Explicit
Sub operation_string12()
'Mid関数は対象文字列から指定した文字数の文字列を抽出する
'Mid関数で値を出力する
Debug.Print Mid("2010年3月10日", 6, 2)
'＝実行結果：3月
'Mid関数で値を出力する(変数)
Dim Sample_String1 As String: Sample_String1 = "パブロ・ディエゴ・ホセ・フランシスコ・デ・パウラ・ホアン・ネポムセーノ・チプリアーノ・デ・ラ・サンティシマ・トリニダード・ルイス・ピカソ"
Debug.Print "長いなまえの有名人のフルネームは パブロ・"; Mid(Sample_String1, 5, 60); "・ピカソ です。"
'＝実行結果：長いなまえの有名人のフルネームは パブロ・ディエゴ・ホセ・フランシスコ・デ・パウラ・ホアン・ネポムセーノ・チプリアーノ・デ・ラ・サンティシマ・トリニダード・ルイス・ピカソ です。
'Mid関数で値を出力する(開始位置が0)
'Debug.Print Mid("ステファニー・ジョアン・アンジェリーナ・ジャーマノッタ", 0, 27)
'＝実行結果：エラー
'Mid関数で値を出力する(長さが0)
Debug.Print Mid("ステファニー・ジョアン・アンジェリーナ・ジャーマノッタ", 1, 0)
'＝実行結果：””(空文字)
'Mid関数で値を出力する(長さが対象文字列数以上)
Debug.Print Mid("トーマス・クルーズ・メイポーザー4世", 1, 19)
'＝実行結果：トーマス・クルーズ・メイポーザー4世
'Mid関数で値を出力する(対象文字列がNull)
Debug.Print Mid(Null, 1, 1)
'＝実行結果：Null
End Sub
Mid関数では、対象文字列から取得したい文字列を抽出することができました。
ただし、対象文字列が長文であれば、Mid関数の引数である開始位置や長さを把握しておくことが必要になります。しかし、長文の文字列から欲しい文字列がどの位置からはじまっているのか、文字数がどのくらいなのかを確認するためにわざわざ目視で数えてしまってはあまり便利とは言えないでしょう。
それならば、他の関数と組み合わせて引数となる値を取得してしまえばいいのです。こちらのコードのほうがより実務的なものなので、利用できる場面ではどんどん利用してみてください。
サンプルコード2
Option Explicit
Sub operation_string13()
'Mid関数の引数である「開始位置」や「文字の長さ」を他の関数との組み合わせで取得する
'対象文字列が長文のため、コードのスリム化のため変数に代入
Dim Word_problems As String: Word_problems = _
"今、わたしたちのまわりでは、ごみを少しでもへらそうと、さまざまな取り組みが行われています。"
'取得したい文字列を変数に代入
Dim Target_Word As String: Target_Word = "ごみを少しでもへらそうと"
'Mid関数の引数である開始位置(start)をInStr関数で取得する
Dim Start_Val As Long: Start_Val = InStr(1, Word_problems, Target_Word)
'＝実行結果：15
'Mid関数の引数である文字数(length)をLen関数で取得する
Dim Length_Val As Long: Length_Val = Len(Target_Word)
'＝実行結果：12
Dim Ans As String: Ans = Mid(Word_problems, Start_Val, Length_Val)
Debug.Print "Q1.なんのためのさまざまな取り組みですか？"; vbCrLf; "A."; Ans
'＝実行結果：Q1.抜き出した文字列はなんですか？A.ごみを少しでもへらそうと
'Mid関数の引数にInStr関数とLen関数を指定する書きかたもできる
Dim Ans2 As String: Ans2 = Mid(Word_problems, InStr(1, Word_problems, Target_Word), Len(Target_Word))
Debug.Print "Q2.なんのためのさまざまな取り組みですか？"; vbCrLf; "A2."; Ans2
'＝実行結果：Q2.抜き出した文字列はなんですか？A2.ごみを少しでもへらそうと
End Sub
文字列を返す関数をつかって文字列を挿入するマクロ
ここで紹介したLeft関数やRight関数、Mid関数は、対象文字列から一部の文字列を抜き出すことができることを説明しました。文字列を抜き出すことができるのであれば、これらの関数の実行で文章に文字列を挿入することもできます。さいごに文字列を挿入するマクロを掲載しますので、あなたの環境で、これらの関数が利用できそうな場面があればサンプルコードを参考にマクロを作ってみてください。
サンプルコード1
Option Explicit
'Left・Right・Mid関数で文字列をつかって挿入する
Sub operation_string14()
Dim String_A As String, String_B As String, String_C As String
String_A = "ベリーキャラメルクリームラテをください。"
String_B = "サイズは、クライン・ミッテ・グロース・エクストラがあります。"
'Left関数とRight関数をつかってString_Aの文字列にString_Bの文字列を挿入する
String_C = Left(String_A, 14) & "の" & Mid(String_B, 15, 4) & Mid(String_B, 1, 3) & Right(String_A, 6)
Debug.Print String_C
'実行結果＝ベリーキャラメルクリームラテのグロースサイズをください。
End Sub
[処理の解説]
・String_Aには「ベリーキャラメルクリームラテをください。」がString_Bには「サイズは、クライン・ミッテ・グロース・エクストラがあります。」が代入されています。
実行結果の「ベリーキャラメルクリームラテのグロースサイズをください。」ができるまでの手順
- Left(String_A, 14)
「ベリーキャラメルクリームラテをください。」の左(先頭)から14文字分である「ベリーキャラメルクリームラテ」を抜き出します。 - Mid(String_B, 15, 4)
「サイズは、クライン・ミッテ・グロース・エクストラがあります。」の15文字目から4文字分である「グロース」を抜き出します。 - Mid(String_B, 1, 3)
「サイズは、クライン・ミッテ・グロース・エクストラがあります。」の1文字目から3文字分である「サイズ」を抜き出します。 - Right(String_A, 6)
「ベリーキャラメルクリームラテをください。」の右(末尾)から6文字分である「をください。」を抜き出します。 - 1で抜き出した文字列と2で抜き出した文字列の間に「の」を挿入します。
文字列の変換や比較をする関数
この章では、文字列の変換や比較ができる関数を紹介します。
CStr関数は指定した値を文字列型に変換する
Cstr関数は指定した値を強制的に文字列型(String)に変換します。ここで紹介するCstrは、データ型を変換する文字列型にするものですが、あるデータ型を別のデータ型に変換することを「キャスト」といい、キャストする関数は、その関数名が「C」からはじまる共通した特徴があります。Cstr関数以外にも、CBool関数やCDate関数、CLong関数のほかにも存在し、いずれも目的のデータ型に変換する機能を持っています。
本記事はあくまで文字列を操作する関数を紹介することを目的にしていますので、Cstr関数のみを取り上げて紹介しています。
構文
CStr(対象となる値)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる値 (expression) |
必須 | – | 文字列型に変換する値を指定します。 |
対象となる値を文字列型に変換しますが、対象となる値がNullであった場合は、実行時エラーが発生します。
戻り値について
| 対象となる値(expression) | 戻り値 |
|---|---|
| 真偽型(Boolean) | True または Falseの文字列 |
| 日付型(Date) | 短い日付形式の日付を含む文字列 |
| Empty | 長さ 0 の文字列 「””」 |
| Error | Error という単語とエラー番号を含む文字列 |
| Null | 実行時エラー |
| その他の数値 | 数値を含む文字列 |
引数のデータ型によって上記のようになります。
指定した値を文字列型に変換するマクロ
サンプルコード1
Option Explicit
'Cstr関数は対象となる値のデータ型を文字列型に変換する
Sub operation_string15()
'結果の確認に使っているTypeName関数はデータ型の種類を返します。
Dim Sample_Val1 As Long: Sample_Val1 = 1
Debug.Print Sample_Val1; " は "; TypeName(Sample_Val1); " から "; _
TypeName(CStr(Sample_Val1)); " に変換されました。"
'＝実行結果： 1 は Long から String に変換されました。
Dim Sample_Val2 As Double: Sample_Val2 = 0.1
Debug.Print Sample_Val2; " は "; TypeName(Sample_Val2); " から "; _
TypeName(CStr(Sample_Val2)); " に変換されました。"
'＝実行結果：0.1 は Double から String に変換されました。
Dim Sample_Val3 As Currency: Sample_Val3 = 1000
Debug.Print Sample_Val3; " は "; TypeName(Sample_Val3); " から "; _
TypeName(CStr(Sample_Val3)); " に変換されました。"
'＝実行結果：Currencyから 1000 でStringに変換されました。
Dim Sample_Val4 As Date: Sample_Val4 = #12/12/2023#
Debug.Print Sample_Val4; " は "; TypeName(Sample_Val4); " から "; _
TypeName(CStr(Sample_Val4)); " に変換されました。"
'＝実行結果：Dateから 2023/12/12 でStringに変換されました。
Dim Sample_Val5 As Boolean: Sample_Val5 = True
Debug.Print Sample_Val5; " は "; TypeName(Sample_Val5); " から "; _
TypeName(CStr(Sample_Val5)); " に変換されました。"
'＝実行結果：Booleanから True でStringに変換されました。
Dim Sample_Val6 As Variant 'Empty値
Debug.Print Sample_Val6; " は "; TypeName(Sample_Val6); " から "; _
TypeName(CStr(Sample_Val6)); " に変換されました。"
'＝実行結果： は Empty から String に変換されました。
'CVErr関数は指定されたエラー番号を含むバリアント型の値を返します。
Dim Sample_Val7 As Variant: Sample_Val7 = CVErr(51)
Debug.Print Sample_Val7; " は "; TypeName(Sample_Val7); " から "; _
TypeName(CStr(Sample_Val7)); " に変換されました。"
'＝実行結果：Errorから エラー 51 でStringに変換されました。
'Dim Sample_Val8 As Variant: Sample_Val8 = Null
'Debug.Print TypeName(Sample_Val8); "から "; CStr(Sample_Val8); " で"; TypeName(CStr(Sample_Val8)); "に変換されました。"
'＝実行結果：エラー(引数にNullが含まれているとエラーが発生する)
'Dim Sample_Val9 As Object: Set Sample_Val9 = ActiveWorkbook
'Debug.Print TypeName(Sample_Val9); "から"; TypeName(CStr(Sample_Val9)); "に変換されました。"
'＝実行結果：エラー(オブジェクト型をキャストしようとするとエラーが発生する)
End Sub
Val関数は文字列を適切な数値型に変換する
文字列に含まれる数値を、適切な型の数値として返します。
構文
Val(対象となる文字列)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列 (string) |
必須 | – | 返したい数値を含む文字列を指定します。 |
引数にNullが指定されているときはエラーが発生します。Val関数の引数に数値の一部として認識されない文字があった場合、文字列の読み取りを中止します。例えば「100円のおかし」を引数に指定した場合、100が返ってきますが、「100円のおかしが5コ」でもうしろの5は読み取られず、100だけが返ってきます。
数値の一部とみなされることが多いドルや、コンマなどは認識されません。
空白やタブ・改行は引数から削除されます。
8進数と16進数を認識して10進数の数値として返します。
※これらを含んだ文字列を引数にしたVal関数は、サンプルマクロで紹介しています。
文字列を適切な数値型に変換するマクロ
サンプルコード1
Option Explicit
'Val関数は対象となる値のデータ型を文字列型に変換する
Sub operation_string16()
Dim Sample_Val
'数値のみ
Sample_Val = Val(17)
Debug.Print Sample_Val
'＝実行結果：17
'文字列のみ
Sample_Val = Val("鈴芽(すずめ)")
Debug.Print Sample_Val
'＝実行結果：0
'数値＋文字列
Sample_Val = Val("17歳の高校生")
Debug.Print Sample_Val
'＝実行結果：17
'文字列＋数値
Sample_Val = Val("岩戸 鈴芽は2006年5月24日生まれ")
Debug.Print Sample_Val
'＝実行結果：0
'空白・タブ・改行文字
Dim Sample_Suzume As String, Sample_Souta As String, Sample_tamaki As String
Sample_Suzume = Val("2006 05 24") '空白
Debug.Print Sample_Suzume '＝実行結果：20060524
Sample_Souta = Val("2002" & vbTab & "02" & vbTab & "24") 'タブ
Debug.Print Sample_Souta '＝実行結果：20020224
Sample_tamaki = Val("1982" & vbCrLf & "07" & vbCrLf & "12") '改行
Debug.Print Sample_tamaki '＝実行結果：19820712
' 数値の一部と見なされることが多い記号や文字 (ドル記号、コンマなど)
Dim Sample_Val1 As String: Sample_Val1 = Val("$")
Dim Sample_Val2 As String: Sample_Val2 = Val("\")
Dim Sample_Val3 As String: Sample_Val3 = Val(",")
Debug.Print Sample_Val1 '=実行結果：0
Debug.Print Sample_Val2 '=実行結果：0
Debug.Print Sample_Val3 '=実行結果：0
'2進数
Sample_Val = Val("&B10001") '2進数の17
Debug.Print Sample_Val
'=実行結果：0
'8進数
Sample_Val = Val("&O21") '8進数の21
Debug.Print Sample_Val
'=実行結果：17
'16進数
Sample_Val = Val("&H11") '16進数の11
Debug.Print Sample_Val
'=実行結果： 17
Sample_Val = Val(True)
Debug.Print Sample_Val
'=実行結果：0
Sample_Val = Val(Empty)
Debug.Print Sample_Val
'=実行結果：0
'Sample_Val = Val(Null)
'Debug.Print Sample_Val
'=実行結果：エラー
End Sub
LCase関数は文字列内のアルファベットを小文字に変換する
引数に指定された文字列のアルファベットの大文字を小文字に変換します。文字列に含まれた大文字のアルファベットを小文字に変換したものを含む文字列を返します。大文字だけが小文字に変換されます。小文字とアルファベット以外の文字は変更されずそのまま返されます。
構文
LCase(対象となる文字列)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列 (string) |
必須 | – | 返したい数値を含む文字列を指定します。 |
引数にNullが指定されているときはNullが返ってきます。
文字列内のアルファベットを小文字に変換するマクロ
サンプルコード1
Option Explicit
'Lcase関数は対象となる文字列のアルファベットの大文字を小文字に変換します。
Sub operation_string17()
Dim Sample_Val As String
'数値
Debug.Print LCase(17)
'=実行結果：17
'文字列
Debug.Print LCase("Tachibana Taki")
'=実行結果：tachibana taki
Debug.Print LCase("miyamizu mitsuha")
'=実行結果：miyamizu mitsuha
'文字列＋数値
Debug.Print LCase("東京都立神宮高校の2年生 の Tachibana Taki")
'=実行結果：東京都立神宮高校の2年生 の tachibana taki
'数値＋文字列
Debug.Print LCase("Miyamizu Mitsuha は 岐阜県立糸守高校の2年3組")
'=実行結果：miyamizu mitsuha は 岐阜県立糸守高校の2年3組
'変数に代入した数値＋文字列
Sample_Val = "AbCdE" & "F1G2H3"
Debug.Print LCase(Sample_Val)
'=実行結果：abcdef1g2h3
'Null
Debug.Print LCase(Null)
'=実行結果：Null
End Sub
UCase関数は文字列内のアルファベットを大文字に変換する
UCase関数は、引数に指定した文字列に含まれるアルファベットの小文字を大文字に変換します。これはさきほどのLCase関数と逆のはたらきをします。小文字だけが大文字に変換されます。大文字とアルファベット以外の文字は変更されずそのまま返します。
構文
UCase(対象となる文字列)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列 (string) |
必須 | – | 返したい数値を含む文字列を指定します。 |
引数にNullが指定されているときはNullが返ってきます。
文字列内のアルファベットを大文字に変換するマクロ
サンプルコード1
Option Explicit
'UCase関数は対象となる文字列のアルファベットの小文字を大文字に変換します。
Sub operation_string18()
Dim Sample_Val As String
'数値
Debug.Print UCase(17)
'=実行結果：17
'文字列
Debug.Print UCase("Tachibana Taki")
'=実行結果：TACHIBANA TAKI
Debug.Print UCase("miyamizu mitsuha")
'=実行結果：MIYAMIZU MITSUHA
'文字列＋数値
Debug.Print UCase("東京都立神宮高校の2年生 の Tachibana Taki")
'=実行結果：東京都立神宮高校の2年生 の TACHIBANA TAKI
'数値＋文字列
Debug.Print UCase("Miyamizu Mitsuha は 岐阜県立糸守高校の2年3組")
'=実行結果：MIYAMIZU MITSUHA は 岐阜県立糸守高校の2年3組
'変数に代入した数値＋文字列
Sample_Val = "AbCdE" & "F1G2H3"
Debug.Print UCase(Sample_Val)
'=実行結果：ABCDEF1G2H3
'Null
Debug.Print UCase(Null)
'=実行結果：Null
End Sub
Format関数は値を指定した書式で文字列型に変換する
Format関数は引数に指定した値を書式指定式に従って文字列を返します。
エクセルの書式設定やワークシート関数のテキスト関数と同じはたらきがあり、書式に指定した値の表示をすることから使用頻度の高い関数と言えます。もっともシンプルで単純な例をあげるとすれば、「2023/1/1」を「令和5年1月1日」に変換する、またその逆の変換をするなどを実行し、戻り値は文字列(String)型になります。
構文
Format(対象となる値, [書式], [週はじめの曜日], [年内はじめの週])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる値 (Expression) |
必須 | – | 任意の値として数値や文字列などを指定します。 |
| 書式 (Format) |
省略可能 | 対象となる値(Expression)をそのまま返しますが、数値は文字列になります。 | 定められた書式指定式、またはユーザー定義の書式指定式を指定します。 |
| 週はじめの曜日 (FirstDayOfWeek) |
省略可能 | vbSunday(1) ※週はじめの曜日(FirstDayOfWeek)引数の設定値の表を参照 |
週の最初の曜日を指定します。 既定値は日曜日(1)です。 |
| 年内はじめの週 (FirstWeekOfYear) |
省略可能 | vbFirstJan1(1) ※年内はじめの週(FirstWeekOfYear)引数の設定値を参照 |
年内で、最初の週を指定します。 既定値は、1月1日含まれる週から開始します。(1) |
週はじめの曜日(FirstDayOfWeek)引数の設定値
| 定数 | 値 | 説明 |
|---|---|---|
| vbUseSystem | 0 | NLS API設定(システムやソフトウェアにおける言語や国・地域の設定)を使用します。 |
| vbSunday | 1 | 日曜日を1週間のはじめの曜日に指定します。(既定) |
| vbMonday | 2 | 月曜日を1週間のはじめの曜日に指定します。 |
| vbTuesday | 3 | 火曜日を1週間のはじめの曜日に指定します。 |
| vbWednesday | 4 | 水曜日を1週間のはじめの曜日に指定します。 |
| vbThursday | 5 | 木曜日を1週間のはじめの曜日に指定します。 |
| vbFriday | 6 | 金曜日を1週間のはじめの曜日に指定します。 |
| vbSaturday | 7 | 土曜日を1週間のはじめの曜日に指定します。 |
年内はじめの週(FirstWeekOfYear)引数の設定値
| 定数 | 値 | 説明 |
|---|---|---|
| vbUseSystem | 0 | NLS API設定(システムやソフトウェアにおける言語や国・地域の設定)を使用します。 |
| vbFirstJan1 | 1 | 1月1日が含まれる週を年の第1週目とします。 (既定)。 |
| vbFirstFourDays | 2 | 4日以上が含まれる最初の週を年の第1週目とします。 |
| vbFirstFullWeek | 3 | 1週間全体が含まれる最初の週を年の第1週目とします。 |
書式(Format)の設定の対象
| 書式設定の対象 | 説明 |
|---|---|
| 数値 | 定義済みの名前付き数値書式を使用するか、ユーザー定義の数値書式を指定します。 ※名前付き数値書式については、名前付き数値書式の表を参照 |
| 日付と時刻 | 定義済みの名前付き日付/時刻書式を使用するか、ユーザー定義の日付/時刻書式を指定します。 ※名前付き日付/時刻書式については、名前付き日付/時刻書式の表を参照 |
| 日付と時刻の連続番号 | 日付および時間書式または数値書式を使用します。 |
| 文字列 | ユーザー定義文字列書式を指定します。 |
対象となる値が数値であるとき、書式(Format)でユーザー定義数式の指定において、セミコロンで区切ることで1から4つのセクションを指定することができます。
Format(対象となる値,“[書式(Format)第1セクション;第2セクション; 第3セクション; 第4セクション]”)
セクションは左から第1・第2・第3・第4となり、「;」文字をつかって区切ることができます。ただし、このあと紹介する「定義済み名前つき書式」が数値書式にある場合は1つのセクションのみが許可されます。
このことから、同じ関数に指定する引数のセクションで、ユーザー定義の書式と定義済み名前つき書式を併用した指定をしないほうが無難です。(エラーは発生しないが意図しない表示になることを回避するため。)
第1セクションのみの場合は、対象となる値のすべてに適用し、第2セクションを指定すると対象となる値が負の場合に適用されます。第3セクションを指定すると0の場合に適用、第4セクションを指定すると対象となる値がNullであった場合に適用されます。
それぞれのセクションの役割と効果については以下の表をご覧ください。
| 書式(Format)のセクション | 説明 |
|---|---|
| セクション1(Ｓ1)を指定 | セクション1(Ｓ1)のみであった場合は、対象となる値のすべてに適用します。 |
| セクション2(Ｓ2)を指定 | セクション2(Ｓ2)は対象となる値が負の場合に適用します。 この場合、S1は正の値と0に適用します。 |
| セクション3(Ｓ3)を指定 | セクション3(Ｓ3)は対象となる値が0の場合に適用します。 この場合、S1は正の値、S2は負の値に適用します。 |
| セクション4(Ｓ4)を指定 | セクション4(Ｓ4)は対象となる値がNullの場合に適用します。 この場合、S1は正の値、S2は負の値、S3は0に適用します。 |
Format関数における書式(Format)セクションの使用例(数値)
Option Explicit
'Format関数は引数に指定した文字列を書式指定式に従って文字列返します。
Sub operation_string_ex0_Val()
'Format関数の引数である数値の書式(Format)は4つのセクションを指定できる。
'セクションは左から第1・第2・第3・第4となり「;」文字をつかって区切ることができる。
'===第1から4セクションまでのすべてを指定した例===
Dim Sample_Val1 As Variant: Sample_Val1 = 1
Debug.Print Format(Sample_Val1, "\A0;\B0;\C0;\D0")
'=実行結果：A1(対象となる値が正の数値のため、第1セクションが適用される)
Sample_Val1 = -1
Debug.Print Format(Sample_Val1, "\A0;\B0;\C0;\D0")
'=実行結果：B1(対象となる値が負の数値のため、第2セクションが適用される)
Sample_Val1 = 0
Debug.Print Format(Sample_Val1, "\A0;\B0;\C0;\D0")
'=実行結果：C0(対象となる値が0のため、第3セクションが適用される)
Sample_Val1 = Null
Debug.Print Format(Sample_Val1, "\A0;\B0;\C0;\D0")
'=実行結果：D0(対象となる値がNullのため、第4セクションが適用される)
End Sub
対象となる値が文字列であるとき、書式(Format)でユーザー定義数式を指定において、セミコロンで区切ることで1から2つのセクションを指定することができます。
Format(対象となる値,“[書式(Format)第1セクション;第2セクション]”)
セクションは左から第1・第2となり、「;」文字をつかって区切ることができます。
第1セクションのみの場合は、対象となる値のすべてに適用し、第2セクションを指定すると対象となる値が空白かNullの場合に適用されます。
それぞれのセクションの役割と効果については以下の表をご覧ください。
| 書式(Format)のセクション | 説明 |
|---|---|
| セクション1(Ｓ1)を指定 | セクション1(Ｓ1)のみであった場合は、対象となる値のすべてに適用します。 |
| セクション2(Ｓ2)を指定 | セクション2(Ｓ2)は対象となる値が空白文字とNull値に適用します。 それ以外の文字列はS1を適用します。 |
Format関数における書式(Format)セクションの使用例(文字列)
Option Explicit
'Format関数は引数に指定した文字列を書式指定式に従って文字列返します。
Sub operation_string_ex0_Str()
'Format関数の引数である文字列の書式(Format)は2つのセクションを指定できる。
'セクションは左から第1・第2となり「;」文字をつかって区切ることができる。
'===第1から2セクションまでのすべてを指定した例===
Dim Sample_Str1 As Variant: Sample_Str1 = "コダマのもりブログは、2022年3月26日にスタートしました。"
Debug.Print Format(Sample_Str1, "@;入力なし")
'=実行結果：コダマのもりブログは、2022年3月26日にスタートしました。
'(対象となる値が空白ではないため、第1セクションが適用される)
Sample_Str1 = ""
Debug.Print Format("", "@;入力なし")
'=実行結果：入力なし
'(対象となる値が""のため、第2セクションが適用される)
Sample_Str1 = Null
Debug.Print Format(Sample_Str1, "@;入力なし")
'=実行結果：入力なし
'(対象となる値がNullのため、第2セクションが適用される)
End Sub
名前付き数値書式
| 書式名 | 説明 |
|---|---|
| General Number | 桁区切り記号を付けずに数を表示 |
| Currency | 桁区切り記号を付けて数を表示 |
| Fixed | 少なくとも整数は1桁、小数は2桁を表示し、小数第三位を四捨五入 |
| Standard | 区切り記号、少なくとも整数1桁、小数2桁を表示 |
| Percent | 数値を100倍して、右側にパーセント記号 (%) を付き表示し、小数第三位を四捨五入 |
| Scientific | 標準の指数表記のE表記 |
| Yes/No | 0はNo、0以外はYes |
| True/False | 0はFalse、0以外はTrue |
| On/Off | 0はOff、0以外はOn |
名前付き数値書式の使用例
Option Explicit
'Format関数は引数に指定した文字列を書式指定式に従って文字列返します。
Sub operation_string_ex1()
'名前付き"数値書式"
'General Number(桁区切り記号を付けずに数を表示)
Debug.Print Format(123456789, "General Number")
'=実行結果：123459789
'Currency(桁区切り記号を付けて数を表示)
Debug.Print Format(123456789, "Currency")
'=実行結果：\123,459,789
'Fixed(少なくとも整数は1桁、小数は2桁を表示し、小数第三位を四捨五入)
Debug.Print Format(12345.6789, "Fixed")
'=実行結果：12345.68
'Standard(区切り記号、少なくとも整数1桁、小数2桁を表示)
Debug.Print Format(12345.6789, "Standard")
'=実行結果：12,345.68
'Percent(数値を100倍して、右側にパーセント記号 (%) を付き表示し、小数第三位を四捨五入。)
Debug.Print Format(0.123456789, "Percent")
'=実行結果：12.35%
'Scientific(標準の指数表記のE表記)
Debug.Print Format(123456789, "Scientific")
'=実行結果：1.23E+08
'Yes/No(0はNo、0以外はYes)
Debug.Print Format(0, "Yes/No"); " "; Format(1, "Yes/No")
'=実行結果：No Yes
'True/False(0はFalse、0以外はTrue)
Debug.Print Format(0, "True/False"); " "; Format(1, "True/False")
'=実行結果：False True
'On/Off(0はOff、0以外はOn)
Debug.Print Format(0, "On/Off"); " "; Format(1, "On/Off")
'=実行結果：Off On
End Sub
名前付き日付/時刻書式
| 書式名 | 説明 |
|---|---|
| General Date | 日付と時刻のどちらか、または両方を表示。日付表示はシステム設定による |
| Long Date | システムの長い日付形式 |
| Medium Date | Microsoft Excelの言語の型に従った中間の長さの日付書式 |
| Short Date | 短い日付書式 |
| Long Time | 時間・分・秒をつかって時間を表示 |
| Medium Time | 12時間表記とAM/PMで表示 |
| Short Time | 24時間形式で表示 |
名前付き日付/時刻書式の使用例
Option Explicit
'Format関数は引数に指定した文字列を書式指定式に従って文字列を返します。
Sub operation_string_ex2()
'名前付き"日付／時刻書式"
'General Date(日付と時刻のどちらか、または両方を表示。日付表示はシステム設定による)
Debug.Print Format("2022/11/11 23:59:59", "General Date")
'=実行結果：2022/11/11 23:59:59
'Long Date(システムの長い日付形式)
Debug.Print Format("2022/11/11", "Long Date")
'=実行結果：2022年11月11日
'Medium Date(Microsoft Excelの言語の型に従った中間の長さの日付書式)
Debug.Print Format("2022/11/11", "Medium Date")
'=実行結果：22-11-11
'Short Date(短い日付書式)
Debug.Print Format("2022/11/11", "Short Date")
'=実行結果：2022/11/11
'Long Time(時間・分・秒をつかって時間を表示)
Debug.Print Format("23:59:59", "Long Time")
'=実行結果：23:59:59
'Medium Time(12時間表記とAM/PMで表示)
Debug.Print Format("23:59:59", "Medium Time")
'=実行結果：11:59 午後
'Short Time(24時間形式で表示)
Debug.Print Format("23:59:59", "Short Time")
'=実行結果：23:59
End Sub
ユーザー定義文字列書式
Format関数の引数の書式(Format)に文字を使用して、文字列の書式指定式が作成できます。
| 文字 | 説明 |
|---|---|
| ＠ | 文字プレースホルダー。文字またはスペースを表示します。対象となる値の表示位置に@があれば、その文字を表示します。 書式文字列に「!」が無い限り文字プレースホルダーは右側から左側に向かって埋められます。 |
| & | 文字プレースホルダー。文字またはスペースを表示します。対象となる値の表示位置に&があれば、その文字を表示します。 書式文字列に「!」が無い限り文字プレースホルダーは右側から左側に向かって埋められます。 |
| ＜ | すべての文字を小文字で表示します。 |
| ＞ | すべての文字を大文字で表示します。 |
| ! | 文字プレースホルダーを左側から右側に向かって埋めます。 |
ユーザー定義文字列書式の使用例
Option Explicit
Sub operation_string_ex3()
'Format関数は引数に指定した文字列を書式指定式に従って文字列を返します。
'ユーザー定義文字列書式"
'@文字プレースホルダーで書式を指定した場合
Debug.Print Format("SUN", "@@@|@@@")
'=実行結果： |SUN(左側に半角スペース×3つ空白あり)
'@文字プレースホルダーで書式を指定した場合(!は文字を左づめにする)
Debug.Print Format("SUN", "!@@@|@@@")
'=実行結果：SUN| (右側に半角スペース×3つ空白あり)
'&文字プレースホルダーで書式を指定した場合
Debug.Print Format("MOON", "&&&&|&&&&")
'=実行結果：|MOON
'&文字プレースホルダーで書式を指定した場合(!は文字を左づめにする)
Debug.Print Format("MOON", "!&&&&|&&&&")
'=実行結果：MOON|
'<(すべての文字を小文字で表示します。)
Debug.Print Format("Diamond and Pearl", "<")
'=実行結果："diamond and pearl"
'>(すべての文字を大文字で表示します。)
Debug.Print Format("Scarlet and Violet", ">")
'=実行結果："SCARLET AND VIOLET"
End Sub
ユーザー定義日付/時刻書式
Format関数の引数の書式(Format)に文字を使用して、日付や時刻の書式指定式が作成できます。
| 文字 | 説明 |
|---|---|
| : | 時刻の区切り記号です。時、分、秒が区切られた表示形式となります。 |
| / | 日付の区切り記号です。日、月、年が区切られた表示形式となります。 |
| c | 日付(ddddd)、時刻(ttttt)の順序で表示します。(表示例：2000/01/01 9:09:09) |
| d | 日を1から31で表示します。日付が1桁の数値であっても0は付与されません。 |
| dd | 日を1から31で表示します。日付が1桁の数値であれば0が付与されます。 |
| ddd | 曜日をSunからSatの省略表示で表示します。 |
| dddd | 曜日をSundayからSaturdayを表示します。 |
| ddddd | 日、月、年を含む形式で日付を表示します。 (表示例：2000/01/01) |
| dddddd | 日、月、年を含む形式で日付を表示します。 (表示例：2000年1月1日) |
| w | 曜日を日曜日が1から土曜日が7の数値で表示します。 (表示例：2000年1月1日は土曜日なので7) |
| ww | 週を1から54の数値で表示します。 (表示例：2000年1月2日なら2) ※日曜日が開始日であり、2000年1月2日(日)から8日(土)までは2が返されます。 |
| m | 月を1から12の数値で表示します。 月が1桁の数値であっても0は付与されません。 |
| mm | 月を01から12の数値で表示します。 月が1桁の数値であれば0が付与されます。 |
| q | 年を四半期に分割した1から4の数値を表示します。 ※1から3月は「1」 , 4から6月は「2」, 7から9月は「3」, 10から12月は「4」 |
| y | 日を1から366の数値で表示します。 (表示例：2000年3月1日なら61、2023年3月1日なら60) ※2000年はうるう年のため。 |
| yy | 年を2桁の00から99の数値で表示します。 |
| yyyy | 年を4桁の100から9999の数値で表示します。 |
| ｈ | 時間を0から23の数値で表示します。 時間が1桁の数値であっても0は付与されません。 |
| Hh | 時間を0から23の数値で表示します。 時間が1桁の数値であれば0は付与されます。 |
| n | 分を0から59の数値で表示します。 分が1桁の数値であっても0は付与されません。 |
| nn | 分を0から59の数値で表示します。 分が1桁の数値であれば0は付与されます。 |
| s | 秒を0から59の数値で表示します。 秒が1桁の数値であっても0は付与されません。 |
| ss | 秒を0から59の数値で表示します。 秒が1桁の数値であれば0は付与されます。 |
| ttttt | 時間、分、秒を含む形式で表示します。表示形式の既定値は「h:㎜:ss」 |
| AM/PM | 12時間形式で表示します。 指定した時間が正午なら「AＭ」を、正午から午後11:59までであれば大文字の「PM」を表示します。 |
| am/pm | 12時間形式で表示します。 指定した時間が正午なら「am」を、正午から午後11:59までであれば小文字の「pm」を表示します。 |
| A/P | 12時間形式で表示します。 指定した時間が正午なら「A」を、正午から午後11:59までであれば大文字の「P」を表示します。 |
| a/p | 12時間形式で表示します。 指定した時間が正午なら「a」を、正午から午後11:59までであれば小文字の「p」を表示します。 |
| AMPM | 12時間形式で表示します。 指定した時間が正午なら「午前」を、正午から午後11:59までであれば小文字の「午後」を表示します。 |
ユーザー定義日付/時刻書式の使用例
Option Explicit
Sub operation_string_ex4()
'Format関数は引数に指定した日付や時刻を書式指定式に従って文字列を返します。
'ユーザー定義日付/時刻書式"
Debug.Print Format("令和5年11月11日 23時59分59秒", "yyyy/mm/dd hh:mm:ss")
'＝実行結果：2023/11/11 23:59:59
Debug.Print Format("2023/11/11", "yyyy/mm/dd hh:mm:ss")
'＝実行結果：2023/11/11 00:00:00(対象となる値に時刻情報なし)
Debug.Print Format("23:59:59", "yyyy/mm/dd hh:mm:ss")
'＝実行結果：1899/12/30 23:59:59(対象となる値に日付情報なし)
Debug.Print Format("2023/11/11 23:59:59", "yyyy/mm/dd")
'＝実行結果：2023/11/11(書式に時刻表示方法の指定なし)
Debug.Print Format("2023/11/11 23:59:59", "hh:mm:ss")
'＝実行結果：23:59:59(書式に日付表示方法の指定なし)
Debug.Print Format("2023/11/11 23:59:59", "c")
'＝実行結果：2023/11/11 23:59:59(日付をdddddで、時刻をtttttの表示形式で返します。)
Debug.Print Format("2023/11/1 23:59:59", "d")
'＝実行結果：1(日を1~31の数値で返す)
Debug.Print Format("2023/11/11 23:59:59", "dd")
'＝実行結果：11(日を01~31の数値で返す)
Debug.Print Format("2023/11/11 23:59:59", "ddd")
'＝実行結果：Sat(曜日をSun-Satで返す)
Debug.Print Format("2023/11/11 23:59:59", "dddd")
'＝実行結果：Saturday(曜日をSunday-Saturdayで返す)
Debug.Print Format("2023/11/11 23:59:59", "ddddd")
'＝実行結果：2023/11/11(日、月、年を含む形式で返す)
Debug.Print Format("2023/11/11 23:59:59", "dddddd")
'＝実行結果：2023年11月11日(日、月、年を含む形式で返す)
Debug.Print Format("2023/11/11 23:59:59", "w")
'＝実行結果：7(日曜日が1で土曜日が7で返す)
Debug.Print Format("2023/11/11 23:59:59", "ww")
'＝実行結果：45(指定した年の何週目かを1～54で返す)
Debug.Print Format("2023/11/11 23:59:59", "m")
'＝実行結果：11(月を1~12の数値として返す)
Debug.Print Format("2023/9/11 23:59:59", "mm")
'＝実行結果：09(月を01~12の数値として返す)
Debug.Print Format("2023/11/11 23:59:59", "q")
'＝実行結果：4(1年の四半期として分割した場合の数値を返す。1-3月:1,4-6月:2,7-9月:3,10-12月:4)
Debug.Print Format("2023/11/11 23:59:59", "y")
'＝実行結果：315(指定した日を1~366の数値として返す)
Debug.Print Format("2023/11/11 23:59:59", "yy")
'＝実行結果：23(指定した年を2桁の00~99の数値として返す)
Debug.Print Format("2023/11/11 23:59:59", "yyyy")
'＝実行結果：2023(指定した年を4桁の100~9999の数値として返す)
Debug.Print Format("2023/11/11 23:59:59", "h")
'＝実行結果：23(時間を0~23の数値として返す)
Debug.Print Format("2023/11/11 9:59:59", "Hh")
'＝実行結果：09(時間を00~23の数値として返す)
Debug.Print Format("2023/11/11 23:59:59", "n")
'＝実行結果：59(分を0~59の数値として返す)
Debug.Print Format("2023/11/11 23:1:59", "nn")
'＝実行結果：01(分を00~59の数値として返す)
Debug.Print Format("2023/11/11 23:59:59", "s")
'＝実行結果：59(秒を0~59の数値として返す)
Debug.Print Format("2023/11/11 23:59:1", "ss")
'＝実行結果：01(秒を00~59の数値として返す)
Debug.Print Format("2023/11/11 9:9:9", "ttttt")
'＝実行結果：9:09:09(時刻を時間、分、秒を含む形式で返す。表示形式の既定値は、h:㎜:ssなので時間は先頭に0なし)
Debug.Print Format("2023/11/11 23:59:59", "AM/PM")
'＝実行結果：PM(対象となる時間に大文字のAM/PMをつけた表示形式で返す)
Debug.Print Format("2023/11/11 23:59:59", "am/pm")
'＝実行結果：pm(対象となる時間に小文字のam/pmをつけた表示形式で返す)
Debug.Print Format("2023/11/11 23:59:59", "a/p")
'＝実行結果：p(対象となる時間に、小文字のaまたはpをつけた表示形式で返す)
Debug.Print Format("2023/11/11 23:59:59", "AMPM")
'＝実行結果：午後(対象となる時間にが含まれる午前または午後を返す)
End Sub
ユーザー定義数値書式
Format関数の引数の書式(Format)に文字を使用して、数値の書式指定式が作成できます。
| 文字 | 説明 |
|---|---|
| なし | 書式の指定なしで数値を表示します。 |
| 0 | 桁のプレースホルダー。1桁の数または0を表示します。 対象となる値に0を指定した位置に数値があれば数値が表示されます。それ以外は0を表示します。 書式で指定した0の桁数よりも対象となる値の桁数が多い場合、0を表示します。 対象となる値が小数点以下で0を指定した書式の桁数よりも多い場合は、四捨五入されます。 |
| # | 桁のプレースホルダー。1桁の数または0を表示します。 0と同様の機能ですが、書式で指定した#の桁数よりも対象となる値の桁数が多い場合であっても0は付与されません。 |
| . | 小数点のプレースホルダー。0または#とあわせて書式で指定します。 対象となる値が1未満であり、書式の整数となる部分に0を指定した場合は、値の先頭に0が付与されます。 (表示例：「0.1」など) 一方、整数となる書式に#を指定した場合は、0が付与されません。(表示例：「 .1」など) |
| % | パーセントのプレースホルダー。 対象となる値を100 倍にします。 書式の指定した位置にパーセント記号( % )が挿入されます。 |
| , | 1,000単位の区切り記号。対象となる値の整数が1000以上である場合は、3桁ごとに区切られます。 書式の整数の末尾がこの文字であったときは、対象となる値を1000で割った数値で表示します。 例えば、対象となる値が10000であるときに書式を「0,」を指定した場合は、10000÷1000となり、10が表示されます。このとき、百の位を四捨五入した数値が1000にならない場合は0が表示されます。 さらに「0,,」や「0,,,」のように書式を指定した場合は百万、十億で割った数値で表示されます。 |
| : | 時刻の区切り文字。時、分、秒で区切られます。 |
| / | 日付の区切り文字。年、月、日で区切られます。 |
| E- E+ e- e+ | 指数形式で表示します。整数と指数の間にEまたはeが表示されます。 E-やe-を書式で指定し、指数が負の値であった場合、符号(-)を付与します。 E+またはe+を書式に指定した場合は、指数の正負によって符号(＋or-)を付与します。 |
| – + $ | リテラル文字を表示します。 その他の文字を表示する場合は、「\」を表示したい文字の前につける。もしくは「“”」で表示したい文字をかこみます。 |
| ￥ | 以下に記載している文字をリテラル文字として表示するには、指定文字のすぐ左に「￥」を記載します。 たとえば、@の文字を表示する書式は「￥@」と指定します。このとき「￥」は表示されません。円記号を表示したい場合は「￥￥」を指定します。 日付や時刻の表示書式指定文字：a、c、d、h、m、n、p、q、s、t、w、y、/、: 数値の表示書式指定文字：#、0、%、E、e、カンマ、ピリオド 文字列の表示書式指定文字：@、&、<、>、! |
| (“ABC”) | ダブルクォーテーション「”」でかこまれた文字列を書式で指定した場合は文字列をそのまま表示します。 ※ダブルクォーテーションの文字コードはChr(34)です。 |
ユーザー定義数値書式の使用例
Option Explicit
Sub operation_string_ex5()
'Format関数は引数に指定した数値を書式指定式に従って文字列を返します。
'ユーザー定義数値書式
'桁のプレースホルダー(0)
Debug.Print Format("12345", "0")
'＝実行結果：12345(1桁のプレースホルダー。0の位置の数字を表示。それ以外は0を表示)
Debug.Print Format("12345", "000000"); '文字列は5桁 < 書式は6桁
'＝実行結果：012345(文字列を右側から埋め、0の位置に数値がなければ0を表示)
'桁のプレースホルダー(#)
Debug.Print Format(12345, "#")
'＝実行結果：12345(1桁のプレースホルダー。0の位置の数字を表示。それ以外は何も表示しない)
Debug.Print Format(12345, "######")
'＝実行結果：12345(文字列を右側から埋め、#の位置に数値がなくても0を表示しない)
'小数点の区切り記号(.)
Debug.Print Format(0.125, "0.00")
'＝実行結果：0.13(小数点の区切り記号。対象となる数値の桁が多い場合は四捨五入)
'小数点の区切り記号(.)
Debug.Print Format(0.124, "#.##")
'＝実行結果：.12(小数点の区切り記号。対象となる数値の桁が多い場合は四捨五入)
'小数点の区切り記号(%)
Debug.Print Format(0.456, "#.0%")
'＝実行結果：45.6%(対象となる数値を100倍してパーセント記号を挿入する)
'1000単位の区切り記号(,)
Debug.Print Format(1234567890, "#,#")
'＝実行結果：1,234,567,890(対象となる数値を1000単位で区切る)
'1000単位の区切り記号(,)
Debug.Print Format(10000, "0, 千") '対象となる数値は1万
'＝実行結果：10 千(整数部の末尾に,を指定した場合は対象の数値を1000で割る)
'1000単位の区切り記号(,)
Debug.Print Format(100000000, "##0,, 百万") '対象となる数値は1億
'＝実行結果：100 百万(整数部の末尾に,,を指定した場合は対象の数値を1,000,000で割る)
'1000単位の区切り記号(,)
Debug.Print Format(1000000000, "##0,,, 十億") '対象となる数値は10億
'＝実行結果：1 十億(整数部の末尾に,,を指定した場合は対象の数値を1,000,000,000で割る)
'時刻の区切り記号(:)
Debug.Print Format(123, "##:##")
'＝実行結果：1:23(時・分で区切られて表示する)
'時刻の区切り記号(:)
Debug.Print Format(123, "0#:##")
'＝実行結果：1:23(時・分で区切られて表示する、時の数値が1桁の場合0が表示される)
'時刻の区切り記号(:)
Debug.Print Format(12345, "##:##:##")
'＝実行結果：1:23:45(時・分・秒で区切られて表示する)
'時刻の区切り記号(:)
Debug.Print Format(12345, "0#:##:##")
'＝実行結果：01:23:45(時・分・秒で区切られて表示する、時の数値が1桁の場合0が表示される)
'日付の区切り記号(/)
Debug.Print Format(20230101, "####/##/##")
'＝実行結果：2023/01/01(年・月・日で区切られて表示する)
'指数形式(E-) 数値と指数部の間に E または e を挿入して指数形式で表示する。
Debug.Print Format(-1234567890, "#E-#")
'＝実行結果：-1E9(対象となる数値がマイナスなら-符号がつく)
'指数形式(E+) 数値と指数部の間に E または e を挿入して指数形式で表示する。
Debug.Print Format(1234567890, "##E+#")
'＝実行結果：12E+8(時・分で区切られて表示する)
'指数形式(e-) 数値と指数部の間に E または e を挿入して指数形式で表示する。
Debug.Print Format(-1234567890, "###e-#")
'＝実行結果：-123e7(対象となる数値がマイナスなら-符号がつく)
'指数形式(e+) 数値と指数部の間に E または e を挿入して指数形式で表示する。
Debug.Print Format(1234567890, "####e+#")
'＝実行結果：1235e+6(時・分で区切られて表示する)
'リテラル文字(-) リテラル文字を表示します。
Debug.Print Format(1234567890, "-##########")
'＝実行結果：-1234567890(-を付与して表示する)
'リテラル文字(+) リテラル文字を表示します。
Debug.Print Format(1234567890, "+##########")
'＝実行結果：+1234567890(+を付与して表示する)
'リテラル文字($) リテラル文字を表示します。
Debug.Print Format(1234567890, "$##########")
'＝実行結果：$1234567890($を付与して表示する)
'円記号(\) 右隣の文字をリテラル文字として表示します。
Debug.Print Format(1234567890, "\\##########")
'＝実行結果：\1234567890(\を付与して表示する)
'円記号(\) 右隣の文字をリテラル文字として表示します。
Debug.Print Format(1234567890, "\@##########")
'＝実行結果：\1234567890(\を付与して表示する)
'円記号(\) 右隣の文字をリテラル文字として表示します。
Debug.Print Format(90, "## °") '角度の単位
'＝実行結果：36.5 °(°を付与して表示する)
'円記号(\) 右隣の文字をリテラル文字として表示します。
Debug.Print Format(36.5, "##.# \℃") '温度の単位
'＝実行結果：36.5 ℃(℃を付与して表示する)
'ダブルクォーテーションで囲まれた文字列(”今日の気温は”)文字列を表示します。
Debug.Print Format(24.5, "今日の気温は ##.# \℃") '温度の単位
'＝実行結果：今日の気温は 24.5 ℃(「今日の気温は」の文字列を付与して表示する)
End Sub
値を指定した書式で文字列型に変換するマクロ
Format関数では書式の使用例でもマクロのコードを掲載しましたが、ここでは一般的な実務でもよく使われそうなサンプルを掲載します。日付の元号表示や、曜日の表示方法、財務諸表・決算書で用いられる「▲」などの表示が必要である場合はこちらが参考になるでしょう。
サンプルコード1
Option Explicit
Sub operation_string_ex6()
'Format関数は引数に指定した文字列を書式指定式に従って文字列返します。
'===日付表示のサンプル===
'文字列日付→年(西暦)・月・日を表示
Debug.Print Format("2023/12/31", "yyyy年m月d日")
'＝実行結果：2023年12月31日
'日付リテラル→年(正式元号)・月・日・曜日を表示
Debug.Print Format("2023/12/31", "ggge年m月d日\(aaa\)")
'＝実行結果：令和5年12月31日(日)
'日付リテラル→年(正式元号)を表示
Debug.Print Format("2023/12/31", "ggge年")
'＝実行結果：令和5年
'日付リテラル→年(省略元号)を表示
Debug.Print Format("2023/12/31", "gge年")
'＝実行結果：令5年
'日付リテラル→年(省略元号)を表示
Debug.Print Format("2023/12/31", "ge")
'＝実行結果：R5
'日付リテラル→標準形式で曜日を表示
Debug.Print Format("2023/12/31", "aaaa")
'＝実行結果：日曜日
'===時刻表示のサンプル===
'xx:xx:xx → 時・分・秒を表示
Debug.Print Format("9:9:9", "h時m分s秒")
'＝実行結果：9時9分9秒
'===数値表示のサンプル===
'整数→%に変換して表示
Debug.Print Format(0.1, "0%")
'＝実行結果：10%(対象となる値を100倍にして%を付与する)
Debug.Print Format(10, "0\%")
'＝実行結果：10%(対象となる値をそのまま受け取って%を付与する)
'負の値に「▲」を付与して表示
Debug.Print Format(-1000, "#,##0;\▲ #,##0")
'＝実行結果：▲ 1,000(桁区切り記号と負の値のときに「▲」を付与する)
'7桁の数値を郵便番号形式にして表示
Debug.Print Format(123456, "\〒000-0000")
'＝実行結果：〒012-3456(-を使って3桁と4桁に分別して「〒」を付与する)
End Sub
ワークシートセルに入力した負の値を赤く表示したい場合の設定はここで紹介したFormat関数ではなく、RangeオブジェクトのNumberFormatプロパティで設定します。
StrConv関数は文字列を大文字や全角、ひらがななどに変換する
StrConv関数は引数に指定した値を変換の種別で指定したとおりの文字列に変換して返します。具体的に言えば、アルファベットを大文字や小文字に変換したり、ひらがなからカタカナにかえたり、カタカナからひらがなに変換などができます。
構文
StrConv(対象となる値, 変換の種類, [システムのローカルID ])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる値 (String) |
必須 | – | 変換の対象となる値を指定します。 |
| 変換の種類 (conversion) |
必須 | – | 変換の種別 |
| システムのローカルID (LCID) |
省略可能 | システムのローカルID | – |
対象となる値が””(空白)の場合は、空白が返ります。またNullの場合はNullが返ってきます。
変換の種類
StrConv関数の引数となる変換の種類(conversion)は、定数または値で指定することができます。
定数や値は以下のとおりです。
| 定数 | 値 | 説明 |
|---|---|---|
| vbUpperCase | 1 | 対象となる文字列を大文字に変換します。 |
| vbLowerCase | 2 | 対象となる文字列を小文字に変換します。 |
| vbProperCase | 3 | 対象となる文字列を先頭の文字を大文字に変換します。 |
| vbWide | 4 | 対象となる文字列内の1バイト文字を2バイト文字に変換します。 |
| vbNarrow | 8 | 対象となる文字列内の2バイト文字を1バイト文字に変換します。 |
| vbKatakana | 16 | 対象となる文字列内のひらがなをカタカナに変換します。 |
| vbHiragana | 32 | 対象となる文字列内のカタカナをひらがなに変換します。 |
| vbUnicode | 64 | 対象となる文字列内の文字列をUnicodeに変換します。 |
| vbFromUnicode | 128 | 対象となる文字列をUnicode からシステムの既定のコード ページに変換します。 |
vbUpperCase(1)や、vbLowerCase(2)は先に紹介したUCase関数とLCase関数と同じはたらきができます。
vbWide(4)とvbNarrow(8)は、システムの言語や地域の設定が日本を含む東アジア圏である場合に有効、vbKatakana(16)、vbHiragana(32)は日本のみ有効です。
また、変換の種類で指定する定数および値は組み合わせることができます。ただし、対象となる値をカタカナにするvbKatakana(16)と、ひらがなにするvbHiragana(32)をくみあわせた場合などはエラーが発生します。いくつかの具体例については、つぎに紹介するサンプルマクロで掲載しています。
システムのローカルID(LCID)
Windowsロケールでシステムの言語や地域を指定している値です。
特殊な状況でなければ使用することはないので考える必要はないです。ちなみに日本は0ｘ0411(16進数)ですが、引数で指定する場合は10進数に変換した「1041」を指定しなければエラーが発生します。
なお、LCIDについてはマイクロソフトのこちらのページで確認できます。
文字列を大文字や全角、ひらがななどに変換するマクロ
サンプルコード1
Option Explicit
'StrConv関数は引数に指定した値を変換の種別で指定したとおりの文字列に変換して返します。
Sub operation_string19()
'定数で指定(大文字に変換)。変換の種別は「1」でも可能
Debug.Print StrConv("ice cream sandwich", vbUpperCase)
'＝実行結果：ICE CREAM SANDWICH
'定数で指定(小文字に変換)。変換の種別は「2」でも可能
Debug.Print StrConv("ICE CREAM SANDWICH", vbLowerCase)
'＝実行結果：ice cream sandwich
'定数で指定(小文字に変換)。変換の種別は「3」でも可能
Debug.Print StrConv("upside down cake", vbProperCase)
'＝実行結果：Upside Down Cake
'定数で指定(2バイト文字に変換)。変換の種別は「4」でも可能
Debug.Print StrConv("ｼﾞﾝｼﾞｬｰﾌﾞﾚｯﾄﾞ", vbWide)
'＝実行結果：ジンジャーブレッド
'定数で指定(1バイト文字に変換)。変換の種別は「8」でも可能
Debug.Print StrConv("アイスクリーム・サンドイッチ", vbNarrow)
'＝実行結果：ｱｲｽｸﾘｰﾑ･ｻﾝﾄﾞｲｯﾁ
'定数で指定(カタカナに変換)。変換の種別は「16」でも可能
Debug.Print StrConv("アップサイドダウンケーキ", vbHiragana)
'＝実行結果：あっぷさいどだうんけーき
'定数で指定(カタカナに変換)。変換の種別は「32」でも可能
Debug.Print StrConv("あっぷさいどだうんけーき", vbKatakana)
'＝実行結果：アップサイドダウンケーキ
'定数で指定(Unicodeに変換)。変換の種別は「64」でも可能
Debug.Print StrConv("あいうえお", vbUnicode)
'＝実行結果：B0D0F0H0J0
'定数で指定(システムの既定のコードに変換)。変換の種別は「128」でも可能
Debug.Print StrConv("B0D0F0H0J0", vbFromUnicode)
'＝実行結果：あいうえお
'変換の種別の組み合わせ(大文字にして2バイト文字)
Debug.Print StrConv("ice cream sandwich", 1 + 4)
'＝実行結果：ＩＣＥ ＣＲＥＡＭ ＳＡＮＤＷＩＣＨ
'変換の種別の組み合わせ(小文字にして1バイト文字)
Debug.Print StrConv("ＩＣＥ ＣＲＥＡＭ ＳＡＮＤＷＩＣＨ", 2 + 8)
'＝実行結果：ice cream sandwich
'変換の種別の組み合わせ(大文字と小文字への変換を併用)
Debug.Print StrConv("ice cream sandwich", 1 + 2) 'vbProperCase(3)とおなじ結果
'＝実行結果：Ice Cream Sandwich
'変換の種別の組み合わせ(ひらがなにして2バイト文字)
Debug.Print StrConv("ｼﾞﾝｼﾞｬｰﾌﾞﾚｯﾄﾞ", 32 + 4)
'＝実行結果：じんじゃーぶれっど
'変換の種別の組み合わせ(カタカナにして1バイト文字)
Debug.Print StrConv("あっぷさいどだうんけーき", 16 + 8)
'＝実行結果：ｱｯﾌﾟｻｲﾄﾞﾀﾞｳﾝｹｰｷ
'変換の種別の組み合わせ(対象となる値が空白で2バイト文字に変換を指定)
Debug.Print StrConv("", 4)
'＝実行結果："" ※空白
'変換の種別の組み合わせ(対象となる値がNullで大文字に変換を指定)
Debug.Print StrConv(Null, 1)
'＝実行結果：Null ※文字列ではないため大文字にならない
'変換の種別の組み合わせ(1バイトと2バイトの変換を併用)
'Debug.Print StrConv("Lollipop", 4 + 8)
'＝実行結果：エラー
'変換の種別の組み合わせ(ひらがなとカタカナの変換を併用)
'Debug.Print StrConv("ロリポップ", 16 + 32)
'＝実行結果：エラー
'変換の種別の組み合わせ(カタカナにして1バイト文字)
'Debug.Print StrConv("あいうえお", 64 + 128)
'＝実行結果：エラー
End Sub
StrConv関数によるUnicodeへの変換についての補足
サンプルマクロにおいて「あいうえお」は「B0D0F0H0J0」に変換されています。これを一文字ずつ分割すると、B0が「あ」、D0が「い」、F0が「う」、H0が「え」、J0が「お」となります。
本来、「あ」のUnicodeのコードポイントは「U+3042」であることから、こちらが結果としてかえってくることが望ましいのですが、調べてみたところ、VBEではUnicodeが読めないことが原因であるとのこと。
では、結果で返ってきている値の「B0」とはどういった値なのか。ですが、これはどうもASCIIコードから変換された文字であるように推察されます。
StrComp関数は文字列を比較する
対象となる文字列を2つ指定し、比較種別に応じた比較を実行します。
構文
StrComp(対象となる文字列1, 対象となる文字列2, [比較種別])
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列1 (string1) |
必須 | – | 比較したい文字列1を指定します。 |
| 対象となる文字列2 (string2) |
必須 | – | 比較したい文字列2を指定します。 |
| 比較種別 (compare) |
省略可能 | vbBinaryCompare (0) |
Option Compareに従う(-1),バイナリ(0),テキスト(1)のなかから比較種別を指定。設定値は定数もしくは数値(-1,0,1)のいずれでも指定可能 |
戻り値について
StrComp関数の実行結果は以下のように返ってきます。
| 実行結果 | 戻り値 |
|---|---|
| 対象となる文字列1(string1)が対象となる文字列2(string2)より小さい | -1 |
| 対象となる文字列1(string1)と対象となる文字列2(string2)が同じ | 0 |
| 対象となる文字列1(string1)が対象となる文字列2(string2)より大きい | 1 |
| 対象となる文字列1(string1)か対象となる文字列2(string2)がNull | Null |
StrCompの引数である対象となる文字列1と対象となる文字列2のどちらかにNullがある場合はNullが戻り値として返します。また数値の比較も実施可能です。
StrCompは指定した文字列を比較して結果に応じた戻り値を数値で返す関数ですが、対象となる文字列の大小は文字コードによって比較しています。文字コードはASC関数をつかって取得できますので、一部のアルファベットで動作を検証してみました。
まずは、Ａ、Ｂ、Ｃ、Ｄの文字コードについてですが、ASC関数で文字コードを取得するとＡが65、Ｂが66、Ｃが67、Ｄは68となっています。
StrComp関数はこの文字コードを比較して大小を判断しているわけなので、以下のようになります。
StrComp(“A”,”B”)は、文字コードに置きなおす「65」と「66」の比較と言えます。対象となる文字列1「65」は、対象となる文字列2「66」よりも小さいため、StrComp(“A”,”B”)の実行結果の戻り値は-1が返ってきます。対象となる文字列を入れかえ、StrComp(“B”,”A”)としたときの実行結果は1となります。
では、比較する対象の文字列を2文字以上にしたときの動きはどうなるのか。についても検証してみました。
結果としては、StrComp 関数では文字コードを1文字目から順番に比較している動作となっています。
その証拠として、StrComp(“AB”,”AC”)の文字コードは「65 66」と「65 67」の比較と、StrComp(“ABC”, “ABD”)での「65 66 67」と「65 66 68」の比較のどちらも-1の結果でした。
また、文字コードを足し算しているわけではないようで、StrComp(“ACB”, “ABC”)は「65 67 66」と「65 66 67」のどちらも合計値は198ですが、実行結果は1が返ってくることから、ACBが大きいと判定されていることがわかります。
StrComp関数のこの特性は、データリストをアルファベット順に並べる、あいうえお順にならべるなどの処理に利用ができます。
文字列を比較するマクロ
サンプルコード1
Option Explicit
'StrComp関数は引数に指定した文字列を比較種別に応じた比較を実行します。
Sub operation_string20()
'バイナリ比較モード(比較種別は「0」でも指定可能。)
Debug.Print StrComp("string1", "string1", vbBinaryCompare)
'＝実行結果：0
'バイナリ比較モード(比較種別は「0」でも指定可能。)
Debug.Print StrComp("String1", "string1", vbBinaryCompare)
'＝実行結果：-1
'バイナリ比較モード(比較種別は「0」でも指定可能。)
Debug.Print StrComp("string1", "String1", vbBinaryCompare)
'＝実行結果：1
'バイナリ比較モード(比較種別は「0」でも指定可能。)
Debug.Print StrComp("もじれつ", "モジレツ", vbBinaryCompare)
'＝実行結果：-1
'バイナリ比較モード(比較種別は「0」でも指定可能。)
Debug.Print StrComp(Null, "モジレツ", vbBinaryCompare)
'＝実行結果：Null
'テキスト比較モード(比較種別は「1」でも指定可能。)
Debug.Print StrComp("String1", "String1", vbTextCompare)
'＝実行結果：0
'テキスト比較モード(比較種別は「1」でも指定可能。)
Debug.Print StrComp("String1", "string1", vbTextCompare)
'＝実行結果：0 (※テキスト比較は大文字と小文字を区別しない)
'テキスト比較モード(比較種別は「1」でも指定可能。)
Debug.Print StrComp("string1", "String1", vbTextCompare)
'＝実行結果：0 (※テキスト比較は大文字と小文字を区別しない)
'テキスト比較モード(比較種別は「1」でも指定可能。)
Debug.Print StrComp("モジレツ", "もじれつ", vbTextCompare)
'＝実行結果：0 (※テキスト比較はひらがなとカタカナを区別しない)
'テキスト比較モード(比較種別は「1」でも指定可能。)
Debug.Print StrComp(Null, "モジレツ", vbTextCompare)
'＝実行結果：Null
End Sub
サンプルコード2
配列の要素を並べかえるマクロ
Option Explicit
'StrComp関数は引数に指定した文字列を比較種別に応じた比較を実行します。
Sub operation_string21()
'StrCompを使ってあいうえお順に並べかえをするサンプルマクロ
Dim Arr As Variant '配列用の変数を宣言(バリアント)
Dim T_String As String '文字列退避用
'このArray関数で1次元配列を作成
Arr = Array("さくらぎ", "るかわ", "みやぎ", "みつい", "あかぎ")
Dim i As Long, j As Long 'くり返し用の変数を宣言
'並べかえ処理
For i = 0 To UBound(Arr) - 1 '配列の0から3までの要素をくり返す
For j = i + 1 To UBound(Arr) '配列の1から4までの要素をくり返す
If StrComp(Arr(i), Arr(j)) = 1 Then 'StrComp関数で比較で戻り値が1の場合
T_String = Arr(i) '対象となる文字列1を変数に代入する
Arr(i) = Arr(j) 'Arr(i)の文字列をArr(j)の文字列に上書きする
Arr(j) = T_String '変数に退避した文字列をArr(j)に上書きする
End If
Next j
Next i
End Sub
配列Arrの要素である文字列をStrComp関数で並べかえられることができました。
このサンプルマクロでは、Array関数をつかって配列の要素を代入しましたが、実用的な使いかたでは、ワークシートのセルの範囲から配列を作成し、並べかえ処理をする場面が多いと考えますので、セルの範囲を配列に代入する方法について知りたい人は、以下の記事を参考にしてください。
StrComp関数以外をつかってデータの並べかえを処理する方法として、Sort(ソート)メソッドやオブジェクトをつかう方法もあります。こちらについては以前の記事で紹介していますので、そちらの方法を知りたい人は以下のリンクよりご覧ください。
文字列の空白やスペースを削除する関数
指定した文字列の空白やスペースを削除するための関数として、LTrim・RTrim・Trimがあります。
この章では、これらの関数についての解説やサンプルマクロを紹介します。いずれの関数も空白やスペースを削除するためのものですが、対象となる文字列のなかの文字間や文字列間の空白やスペースを削除することはできません。
文字・文字列間の空白やスペースを削除したいのであれば、先に紹介したReplace関数で置換する手段を使うとよいでしょう。
LTrim関数は文字列の先頭(左側)の空白を削除する
LTrim関数は指定した文字列の先頭の空白(スペース)を取り除きます。
構文
LTrim(対象の文字列)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列 (String) |
必須 | – | 空白やスペースを削除する文字列を指定します。 |
※引数は文字列です。Nullが含まれる場合はNullが返されます。
文字列の左側にある空白を削除するマクロ
サンプルコード
Option Explicit
'LTrim関数は対象となる文字列の先頭のスペースを削除します。
Sub operation_string22()
Debug.Print LTrim(" ABC ")
'＝実行結果：ABC (末尾のスペースは削除されない)
Debug.Print LTrim(" 123")
'＝実行結果：123
Debug.Print LTrim("あいう ")
'＝実行結果：あいう (末尾のスペースは削除されない)
Debug.Print LTrim("ABC DEF GHI")
'＝実行結果：A B C D E(文字列間のスペースは取り除かれない)
Debug.Print LTrim(Null)
'＝実行結果：Null
End Sub
RTrim関数は文字列の末尾(右側)の空白を削除する
RTrim関数は指定した文字列の末尾の空白(スペース)を取り除きます。
構文
RTrim(対象の文字列)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列 (String) |
必須 | – | 空白やスペースを削除する文字列を指定します。 |
※引数は文字列です。Nullが含まれる場合はNullが返されます。
RTrim関数は文字列の右側にある空白を削除するマクロ
サンプルコード
Option Explicit
'RTrim関数は対象となる文字列の先頭のスペースを削除します。
Sub operation_string23()
Debug.Print RTrim(" ABC ")
'＝実行結果： ABC(先頭のスペースは削除されない)
Debug.Print RTrim(" 123")
'＝実行結果： 123(先頭のスペースは削除されない)
Debug.Print RTrim("あいう ")
'＝実行結果：あいう
Debug.Print RTrim("ABC DEF GHI")
'＝実行結果：A B C D E(文字列間のスペースは取り除かれない)
Debug.Print RTrim(Null)
'＝実行結果：Null
End Sub
Trim関数は文字列の先頭と末尾(両側)の空白を削除する
Trim関数は指定した文字列の先頭と末尾の両方の空白(スペース)を取り除きます。
構文
Trim(対象の文字列)
引数について
| 引数の種類(パーツ名) | 必須or省略可能 | 省略時の値 | 説明 |
|---|---|---|---|
| 対象となる文字列 (String) |
必須 | – | 空白やスペースを削除する文字列を指定します。 |
※引数は文字列です。Nullが含まれる場合はNullが返されます。
文字列の両側にある空白を削除するマクロ
サンプルコード
Option Explicit
'Trim関数は対象となる文字列の先頭と末尾のスペースを削除します。
Sub operation_string24()
Debug.Print Trim(" ABC ")
'＝実行結果：ABC
Debug.Print Trim(" 123")
'＝実行結果：123
Debug.Print Trim("あいう ")
'＝実行結果：あいう
Debug.Print Trim("ABC DEF GHI")
'＝実行結果：A B C D E(文字列間のスペースは取り除かれない)
Debug.Print Trim(Null)
'＝実行結果：Null
End Sub
オンラインスクールで現役エンジニアのサポートがあるテックアカデミーがおすすめ。
スキマ時間に学べて仕事も保証。必ず副業、始められます。まずは無料でプログラミング体験
エクセルVBAの文字列操作関数まとめ
この記事では、エクセルVBAで文字列操作をする関数を紹介しました。さいごに記事のなかで紹介した関数についてまとめておきます。
いくつか使い方にクセのある関数もありますが、これらを単体で利用することや、組み合わせることで記事を読んだ人の目的の文字列操作につかえる関数が見つかれば幸いです。
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント