実際のデータには、1 つのセルに複数の事実が詰め込まれていることがよくあります。INV-2041-DE のような注文コードがその例です。末尾からテキストを取り出し、文字を見つけ、テキストを数値に変換する関数によって、それを使える部品に分けられます。
端と途中からテキストを取り出す
LEFT は先頭から、RIGHT は末尾から、MID は指定した任意の位置から文字を取り出します。コード INV-2041-DE が A2 にある場合、最後の 2 文字が国を表します:
=RIGHT(A2,2)
DE を返します。固定の位置は、すべてのコードの形が同じ場合にしか動きません。長さが変わる場合は、代わりに区切り文字を探してください。
SEARCH で位置を見つける
SEARCH(検索文字列, 対象の文字列, 開始位置) は、あるテキストの中に別のテキストがある位置を返します。INV-2041-DE では最初のダッシュは位置 4 にあるので、=SEARCH("-",A2) は 4 を返します。その直後から始めるには 1 を足します: =MID(A2,SEARCH("-",A2)+1,4) は、位置 5 からの 4 文字である "2041" を返します。
省略可能な 3 番目の引数は、検索を開始する位置を指定します。=SEARCH("-",A2,5) は最初のダッシュを飛ばし、位置 9 にある 2 番目のダッシュを見つけます。3 つの部分からなるコードの 2 番目の部分に到達するには、このようにします。
最初のダッシュの後にあるすべてを取り出すには、LEN に数えさせます: =RIGHT(A2,LEN(A2)-SEARCH("-",A2)) は "2041-DE" を返します。
SEARCH は大文字と小文字を無視し、ワイルドカードの ? と * を受け付けます。その兄弟の FIND は大文字と小文字を区別し、ワイルドカードは使えません。テキストが見つからないと、どちらも #VALUE! を返すため、区切り文字がない行があるかもしれない場合は IFERROR で囲んでください。
テキストを数値に変える
LEFT、RIGHT、MID が返すものはすべて、数値のように見えてもテキストです。"2041" は合計されず、誤った位置に並んでしまいます。VALUE で変換してください: =VALUE(MID(A2,5,4)) は数値 2041 を返します。二重のマイナスでも、より短く同じ仕事ができます。=--MID(A2,5,4) です。ただし VALUE のほうが意図を明確に示します。
テキストの数値は、左揃えと、隅の小さな緑の三角形で見分けられます。VALUE はお使いの地域の設定でテキストを読み取るので、ファイルを国をまたいで共有すると、小数点のコンマかピリオドが問題になることがあります。テキストが数値でまったくない場合は、#VALUE! になります。
大文字・小文字をそろえる
コードが大文字と小文字の混ざった形で届くと、UPPER と LOWER が役立ちます。完全一致で比べる検索が壊れるからです。1 つの数式で抽出と組み合わせてください:
=LOWER(RIGHT(A2,2))
de を返します。列を一度だけ分割したいだけなら、「区切り位置」やフラッシュ フィルのほうが速いかもしれません。データが変わり続けるなら、数式が勝ります。数式は自動的に更新されるからです。
役立つ習慣があります。数式は 1 段階ずつ、別々のセルで組み立ててください。まず位置、次に部分、次に変換です。動いたら、その段階を 1 つの数式にまとめます。