Excel VBA(エクセル マクロ) 小技集@ 文字列から特定の文字を抽出(表示形式統一)

外部ファイルを取り込むと表示形式が入り乱れる。
検索効率を上げるためにも、表示形式をすっきり統一。

大まかな作業は終わり、これからはExcel VBAのスキルアップということで、細かいところの修正を行っていく。

文字列操作を行う必要が出てきた。

VLOOKUP等で検索する場合、検索元と検索値を同じにしなければならないが、外部ファイルの寄せ集めのため、さまざまな表示形式がある。

121-134567
PJ123455
#123456

いずれも後ろ後半の数字(桁数は変わる)が必要で表記方法を統一しなければならない。


ということで、InStr、Mid、Right、Left、Lenという文字列検索関数、抽出関数、文字列の長さ算出関数を使用して実現することとした。

Sub 文字列書式統一()
Dim a As String, b As String
Sheets("データ").Select
a = Cells(1, 1).Value

If InStr(a, "-") > 0 Then
b = Mid(a, InStr(a, "-") + 1, Len(a) - InStr(a, "-"))
End If
If InStr(a, "PJ") > 0 Then
b = Mid(a, InStr(a, "PJ") + 2, Len(a) - InStr(a, "PJ")-1)
End If
If InStr(a, "#") > 0 Then
b = Mid(a, InStr(a, "#") + 1, Len(a) - InStr(a, "#"))
End If
Cells(1, 2).Value = b
End Sub


Cells(1,1)に元データを入れて、Cells(1,2)に抽出結果を出すこととする。

@「121-134567」の後半134567を取り出したい。

If InStr(a, "-") > 0 Then
b = Mid(a, InStr(a, "-") + 1, Len(a) - InStr(a, "-"))
End If

まず、InStr関数で「-」があるかどうかを確認する。

InStr(a, "-")関数は文字列aに対し、「-」がある場合は何番目にあるか返し、無い場合は0を返すため、0より値が大きい場合は「-」がある場合は後半部分を取り出すようにIF分を作成する。

b = Mid(a, InStr(a, "-") + 1, Len(a) - InStr(a, "-"))

Mid関数は文字列aに対し、何番目の文字から何文字取り出したものを値として返すか決める。

上のプログラムだと、InStr(a, "-") は4と言う値が入り、「-」の次の文字(5番目)から取り出したいので、「InStr(a, "-") +1」番目から取り出したい。
どれだけ取り出したいかは、文字列aの長さLen(a)から「-」の位置InStr(a, "-")を引いた物なので、Len(a) - InStr(a, "-")となる。
実際の値は、文字列の長さは10、「-」の位置は4のため、6となる。

121-134567の5番目から6文字取り出すことなり、134567が見事に抽出される。

APJ123455、#123456
 基本的に@と同じ。

If InStr(a, "PJ") > 0 Then
b = Mid(a, InStr(a, "PJ") + 2, Len(a) - InStr(a, "PJ")-1)
End If

 InStr(a, "PJ")でPJが存在するか確認し、Mid関数で文字を抽出する。

 ただ、PJの場合はInStr(a, "PJ")の値が1となり、3番目から抽出したいため、InStr(a, "PJ") + 2として、余分に1を足さないといけない。

置換関数replaceを使い、PJを空白に置き換えればPJは消えるが、シート全体の「PJ」が消えてしまうのでNG。
replaceに範囲指定できることを知っていたら教えてください。


私の場合は、Do until構文を使用して、ある列について空白行になるまで処理を行う方式を使用した。

ine1 = 2
Do Until Cells(line1, 4).Value = ""



line1 = line1 + 1
Loop

以上で、文字列操作が可能となった。

忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP