LoginSignup
6
7

More than 1 year has passed since last update.

Excelでちょっとした正規表現を使いたい

Last updated at Posted at 2021-09-29

(2022/04/29 更新)
関連記事として「Script LabでExcelのユーザー定義関数を書く」を投稿しました。

はじめに

以前,Excelで名簿整理業務をした際,元になる複数の名簿間で電話番号の表記ゆれがあった。
よくある事案だが,まさかの全角表記,さらには「ー(長音)」まで混在...
(もしかすると,ハイフンが半角長音の可能性も...)

(0XXX)YY-ZZZZ, 0XXX(YY)ZZZZ, 0XXX-YY-ZZZZ, (0XXX)YY-ZZZZ など

当時,上記の表記ゆれを 0XXX-YY-ZZZZ に統一するため,次の手順で対応した。

(1)ASC関数で半角変換し,値としてコピペし直す。
(2)データのセル範囲に対して4ステップの置換作業
   (0  →  0
   (  →  -
   )  →  -
   (半角長音)  →  -

別に大した作業ではないが,「Excelで正規表現を利用できたら」とモヤモヤ...

そうだ! PowerShellだ!

「Excel 正規表現」で検索すると,VBA + VBScript.RegExpオブジェクトの使用例が多い。
たかが電話番号の処理には大げさすぎる。

「正規表現ならPowerShellがあるじゃないか!」 早速試す。

(1)元データのセル範囲をコピー!(Ctrl + C)
   (まさかの全角表記があれば,事前にASC関数で半角変換。)
Excel1.png

(2)PowerShellで次の1行を実行

PowerShell
(gcb) -replace '\(?(\d+)\D(\d+)\D(\d+)', '$1-$2-$3' | scb

(3)貼り付け先のセルでペースト!(Ctrl + V)
Excel2.png

Excelや正規表現に限らず,クリップボードを介したPowerShellの活用は便利だ...

ご注意

旧PowerShell(少なくとも5.1)では,Excelのクリップボードと相性が悪いようで,当方の環境ではペーストできませんでした。

Excelでセル範囲をコピーすると「CutCopyMode」(コピーされたセルの周囲で破線が回る状態)に入ります。
通常,他のアプリケーションでコピーが行われると,「CutCopyMode」は解除され,クリップボードは置き換わります。PowerShell 7.1 でも,Set-Clipboard(scb)で同様の動作をします。

旧PowerShellでもSet-Clipboardでクリップボードが置き換わり,Excel以外のアプリケーションへのペーストは,正常に機能します。
ところが,肝心のExcelで「CutCopyMode」が解除されず,「Excel上のクリップボード」は処理前の元データのまま置き換わっていません。
かといって,Excelでペーストする前にESCキーで「CutCopyMode」を手動解除すると,クリップボードがクリアされ,結局Excelにはペーストできません。

手数を要しますが,旧PowerShellで回避する一例です。
(1)元データのセル範囲をコピー

(2)PowerShellでクリップボードの一時保存

PowerShell
$temp = gcb

(3)Excelに戻り「CutCopyMode」を手動解除

(4)PowerShellでスクリプトを実行

PowerShell
$temp -replace '\(?(\d+)\D(\d+)\D(\d+)', '\$1-\$2-\$3' | scb

(5)再度Excelに戻り,貼り付け先のセルでペースト

Excelの動作に注意すれば,旧PowerShellでもそれなりのことはできます。

作業環境

OS : Windows 10 Pro(Ver. 21H1)
Excel : Excel for Microsoft 365 MSO 32bit
PowerShell : Ver. 7.1.4 および Ver. 5.1

6
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
7