【Excel】エクセルで左から任意の文字数を削除する関数の使い方
Excelで商品コードや管理番号を扱っていると、先頭に付いた不要な文字列だけを削除したい場面があります。
たとえば「TOKYO-00125」から拠点名を除き、「00125」だけを取り出したいケースです。
手作業で編集すると件数が増えたときにミスが起こりやすいため、関数で処理できるようにしておくと便利です。
左側から決まった文字数を削除するなら、REPLACE関数またはRIGHT関数を使う方法が基本です。
削除する文字数が行ごとに異なる場合は、FIND関数と組み合わせると柔軟に対応できます。
この記事では、左から任意の文字数を削除する数式、文字列の長さが異なるときの考え方、エラーを防ぐ方法まで解説します。
サンプルデータは1行目を見出し行とし、実際の表にそのまま入力できる形で紹介します。
REPLACE関数で先頭文字を削除する方法【固定文字数の削除】
| A列 元データ | B列 削除後 |
|---|---|
| ABC-00125 | 00125 |
| ABC-00890 | 00890 |
それではまず、先頭から決まった数の文字を削除するREPLACE関数について解説していきます。
先頭の「ABC-」のように、削除する部分が毎回同じ4文字である場合に向く方法です。
REPLACE関数の入力構造
REPLACE関数は、指定した位置から指定した文字数を別の文字列へ置き換える関数です。
置換後の文字列に空の文字列を指定すれば、対象部分を実質的に削除できます。
=REPLACE(A2,1,4,””)
この数式ではA2セルの1文字目から4文字分を空白へ置き換えます。
そのため、A2が「ABC-00125」であれば、結果は「00125」です。
開始位置を1にすると、文字列のいちばん左端から処理できます。
2番目の引数は削除を始める位置、3番目の引数は削除する文字数、4番目の引数は置き換える文字列です。
文字を消す目的では、4番目の引数を半角のダブルクォーテーション2個で表す空文字列にします。
数式のコピーとオートフィル
B2セルへ数式を入力したら、セル右下の小さな四角形を下方向へドラッグしてコピーします。
データが連続している表では、フィルハンドルをダブルクリックして最終行まで自動入力する方法も便利です。
数式はA2を相対参照しているため、B3では自動的にA3、B4ではA4を参照します。
元データを修正しても計算結果が更新されるため、コピーと貼り付けによる編集より管理しやすい仕組みです。
先頭の文字数がすべての行で同じなら、1つの数式を下までコピーするだけで完了します。
【操作のポイント】数式を入力するのは削除前の列ではなく、隣の空いた列にします。
削除後の値だけを残す手順
REPLACE関数で作成したB列は数式であり、A列の内容に連動しています。
外部システムへ渡すデータなどで数式を残したくない場合は、B列をコピーして値として貼り付けましょう。
コピー後に貼り付け先を右クリックし、「値」の貼り付けを選択すると、表示結果だけを固定できます。
数式を値へ変換したあとにA列を削除すれば、不要な接頭辞を除いたデータだけを残せます。
ただし値貼り付けをすると元データを変えても結果は更新されないため、作業完了後に実施するのが安心です。
【操作のポイント】元データ列は、値貼り付けが終わるまで削除せずに残しておきます。
RIGHT関数で残りの文字列を取り出す方法【文字数からの計算】
| A列 元データ | B列 取得結果 |
|---|---|
| ABC-00125 | 00125 |
| ABC-00890 | 00890 |
続いては、RIGHT関数とLEN関数を使って左側の不要な文字を除く方法を確認していきます。
この方法は、削除するのではなく、右側に残したい文字数を計算して抜き出す考え方です。
RIGHT関数とLEN関数の組み合わせ
RIGHT関数は文字列の右端から指定した文字数を取り出します。
元の文字数から先頭で削除したい文字数を引けば、右側に残すべき文字数を求められます。
=RIGHT(A2,LEN(A2)-4)
LEN(A2)はA2セル全体の文字数を数える部分です。
そこから4を引くことで、先頭4文字を除いた残りの文字数になります。
RIGHT関数は、元データの全体文字数が行ごとに違っても使える点が特徴です。
「ABC-00125」と「ABC-123456789」のように後半の桁数が異なるデータでも、先頭4文字を除いた残りを取得できます。
REPLACE関数との使い分け
先頭から一定数を消すだけなら、REPLACE関数のほうが数式を読み取りやすいことがあります。
一方で、右側の文字列を取り出す処理だと明確に伝えたい場合はRIGHT関数が適しています。
たとえば管理番号の後半だけを別の列へ抽出する帳票では、RIGHT関数を選ぶと目的を理解しやすくなります。
文字列の途中を空文字列に置き換えるREPLACE関数に対し、RIGHT関数は必要な部分を新しく抜き出す関数です。
どちらの式も結果は同じでも、後から修正する人が理解しやすい数式を選ぶことが大切です。
【操作のポイント】先頭の削除文字数を変更する場合は、数式内の4を必要な数へ変更します。
文字数が不足した場合の注意点
RIGHT関数の文字数指定には、基本的に0以上の数値を指定します。
元データが短すぎる状態で「LEN(A2)-4」が負の数になると、数式はエラーになります。
入力漏れや形式違いが混在する表では、IF関数を組み合わせると安全です。
=IF(LEN(A2)>4,RIGHT(A2,LEN(A2)-4),””)
この式では、A2の文字数が4より大きいときだけRIGHT関数を実行します。
4文字以下のデータでは空白を返すため、エラー表示を避けられます。
データの品質を確認したい場合は空白の代わりに「確認」と表示する設定も有効です。
【操作のポイント】短いデータが例外として存在する可能性を考え、事前に列全体を確認します。
区切り記号の位置から削除する方法【FIND関数の活用】
| A列 元データ | B列 抽出結果 |
|---|---|
| 東京-00125 | 00125 |
| 大阪営業所-00890 | 00890 |
続いては、削除する文字数が一定ではなく、ハイフンなどの区切り記号より左を削除する方法を確認していきます。
拠点名や部署名の文字数が行ごとに異なるデータでは、FIND関数を使うと対応できます。
FIND関数で区切り位置を調べる仕組み
FIND関数は、文字列の中にある特定の文字が何文字目にあるかを返します。
「東京-00125」であれば、ハイフンは3文字目にあります。
=FIND(“-“,A2)
この数式の結果は3です。
「大阪営業所-00890」の場合は、ハイフンが5文字目にあるため結果は5になります。
FIND関数を使うと、固定の文字数ではなく区切り記号の位置を基準にできます。
全角ハイフン、半角ハイフン、スラッシュなど、データに実際に入っている記号を正確に指定することが重要です。
FIND関数とRIGHT関数の数式
区切り記号の右側だけを取り出すには、RIGHT関数の文字数にFIND関数を組み込みます。
=RIGHT(A2,LEN(A2)-FIND(“-“,A2))
FIND(“-“,A2)でハイフンの位置を求め、文字列全体の長さから引きます。
その結果、ハイフンの右側にある文字数だけをRIGHT関数で取り出せます。
数式をB2へ入力したら、フィルハンドルを下へ引いて各行へコピーします。
先頭セルで正しい結果を確認してからオートフィルを実行すると、形式の違いに早く気付けます。
【操作のポイント】ハイフンそのものは結果に含まれないため、FINDの結果に1を足す必要はありません。
MID関数で右側を直接抽出する方法
MID関数は、指定位置から指定文字数分を取り出す関数です。
区切り記号の直後から最後までを取得する式としても利用できます。
=MID(A2,FIND(“-“,A2)+1,LEN(A2))
FIND関数で見つけたハイフンの位置に1を足し、その次の文字から抽出を開始します。
RIGHT関数の式と比べると、抽出の開始位置を明示できる点がMID関数の特徴です。
記号の右側を取り出す式では、RIGHTとMIDのどちらを使っても実務上は対応できます。
【操作のポイント】区切り記号の直後から取り出すMID関数では、FINDの結果に必ず1を加えます。
文字列の形式が混在する場合の対処法【IFERROR関数】
| A列 元データ | B列 結果 |
|---|---|
| 東京-00125 | 00125 |
| 番号未設定 | 要確認 |
続いては、区切り記号がないデータや空白セルが混在する場合の対処法を確認していきます。
実務の一覧表では、すべての行が同じルールで入力されているとは限りません。
FIND関数で起こるエラー
FIND関数は、検索する記号が対象セルに存在しない場合にエラーを返します。
たとえば「番号未設定」にハイフンが入っていないと、FIND(“-“,A2)は正常に計算できません。
そのエラーがRIGHT関数まで伝わるため、B列にはエラー表示が出ます。
エラーは数式の故障ではなく、想定と異なるデータ形式を知らせる合図でもあります。
まずは元データに区切り記号があるか、全角と半角が混ざっていないかを確認しましょう。
IFERROR関数で表示を整える方法
エラーの代わりに空白や確認メッセージを表示したい場合はIFERROR関数を使います。
=IFERROR(RIGHT(A2,LEN(A2)-FIND(“-“,A2)),”要確認”)
IFERROR関数は、最初の数式が正常ならその結果を返し、エラーなら2番目の内容を返します。
この例ではハイフンがあるデータは番号を表示し、ないデータは「要確認」と表示します。
一覧表を確認する担当者がいる場合は、空白よりも要確認のような表示のほうが見落としを防げます。
【操作のポイント】エラーを隠すだけでなく、例外データを確認するための表示を決めておきます。
全角記号と半角記号の統一
見た目が似ていても、全角の「－」と半角の「-」はExcelでは別の文字です。
半角ハイフンを検索するFIND関数では、全角ハイフンを含むセルを見つけられません。
入力元が複数ある場合は、置換機能で記号を統一してから数式を入れる方法が有効です。
また、TRIM関数で不要な半角スペースを削除する処理も、データ整形では役立ちます。
関数の前に元データの表記ルールを整えることが、安定した集計への近道です。
【操作のポイント】検索する記号は、セルを編集状態にして実際の文字を確認してから数式へ入力します。
左から文字を削除するときの実務上の注意点【データ管理】
| A列 元コード | B列 加工後コード |
|---|---|
| 00125 | 00125 |
| 00008 | 00008 |
続いては、左から文字を削除する際に知っておきたいデータ管理上の注意点を確認していきます。
計算結果が正しく見えても、コードの先頭ゼロや元データを失うと後工程で困る場合があります。
先頭のゼロを消さないための設定
「00125」のような管理番号は、数値ではなく文字列として扱う必要があります。
セルの表示形式が標準や数値のままだと、貼り付けや再入力の際に先頭のゼロが消えることがあります。
対象列を選択し、セルの書式設定で表示形式を文字列にしてから入力すると安心です。
コードや会員番号は、計算対象でなければ文字列として管理するのが基本です。
関数の結果も文字列として返るため、REPLACE関数やRIGHT関数で得た「00125」は通常そのまま保持されます。
元データ列を保管する考え方
関数で加工した列を作るときは、元データを直接書き換えずに別列へ出力する方法が安全です。
誤って削除文字数を間違えた場合でも、A列が残っていれば数式を修正して再計算できます。
元データ、加工用数式、確定値の列を分けると、作業の流れが明確になります。
一度しかない原本データは、加工前の状態を残すことが重要です。
【操作のポイント】大きなデータを値貼り付けする前に、ファイルを別名保存してバックアップを作成します。
関数結果の確認方法
数式を下までコピーしたら、先頭行だけでなく途中行と最終行も確認します。
特に文字数が長いデータ、空白を含むデータ、記号が複数あるデータを重点的に見ましょう。
ハイフンが2つある文字列では、FIND関数は最初に見つかったハイフンを基準にします。
期待する位置と異なる場合は、検索開始位置を指定するFIND関数や、TEXTAFTER関数の利用を検討できます。
【操作のポイント】数式の一括コピー後は、形式が異なる代表的な行を選んで結果を照合します。
まとめ エクセルで左から任意の文字数を削除する関数の使い方
Excelで左から任意の文字数を削除するには、削除部分が固定か、区切り記号によって変わるかで関数を選びます。
先頭から決まった数の文字を消すなら、=REPLACE(A2,1,削除文字数,””)が分かりやすい数式です。
右側に残る文字列を取得するなら、=RIGHT(A2,LEN(A2)-削除文字数)を使えます。
拠点名などの長さが行ごとに変わる場合は、FIND関数でハイフンなどの位置を求め、RIGHT関数またはMID関数と組み合わせます。
形式が混在する表ではIFERROR関数を加え、エラーではなく「要確認」と表示すると確認作業が進めやすくなります。
最初は隣の列で数式を試し、結果を確認してから値貼り付けする流れが安全です。
先頭ゼロを含むコードは文字列として扱い、元データを残しながら処理していきましょう。
