☰

スペースなしで文字の前の数字を削除する

tag-icon • tag-icon microsoft-excel-2010
スペースなしで文字の前の数字を削除する

各行からコロンを含む数字を削除したいと思います。

サンプルデータ:

22:18:01(165):access-list OUTSIDE_IN extended permit deny IP

式を使ってみました

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

しかし、うまくいきませんでした。22:18:01(165):access-list最後のコロンの後にスペースがないので、すべてが削除されました。

答え1

すべての行が例の行のように見えると仮定すると (具体的には、保持したいテキストの部分にコロンがない)、:区切り文字として指定して、Text to Columns を使用できます。

データのある列を選択し、タブText to ColumnsをクリックしますData。

最初の画面で選択しDelimited、2番目の画面ではチェックのみしOtherてコロンを入力します:

「完了」をクリックすると、データは各コロンに基づいて4つの列に分割されます。最初の3つの列を削除すると、次のようになります。access-list OUTSIDE_IN extended permit deny IP

あるいは、前の文字数がaccess-list各行で同じであると仮定すると、次のように使用できます。=RIGHT(A2,LEN(A2)-14)

答え2

任意の長さの数値に対応する最も単純な数式ソリューションは、データの先頭にコロンで区切られた数値の数が固定されている場合に適用されます。たとえば、数値が 3 つの場合です。

ワークシートのスクリーンショット

次の数式を入力しB2、表の残りの列に Ctrl キーを押しながら Enter キーを押すか、コピーして貼り付けるか、下方向にフィルするか、自動でフィルします。

=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),3)))

説明：

オプションの4番目の引数は、特定SUBSTITUTE()の文字:（この場合は3番目）を、テキストの他の場所には現れない特別な文字に置き換えることを可能にします。CHAR(1)一度もない任意の文字列に出現します。

これにより、保持したいデータが右側にある 2 番目の単独の区切り文字が作成されます。これRIGHT()で、数式が正しく機能するようになります。

この解決策は、保持したいテキストにコロンが含まれている場合でも機能することに注意してください。

より一般的な公式の解法では、変数データの先頭の区切り文字の数字の数:（および任意の長さの数字）は次のとおりです。

=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))

説明：

2 番目はSUBSTITUTE()すべてのコロンを削除します。元のテキストの長さとコロンが削除されたテキストの長さの差がコロンの数になります。

サンプルデータの場合:

  • LEN(A2)-LEN(SUBSTITUTE(A2,":",""))
    → 3.
  • したがって=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))
    →=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),3)))つまり最初の式と同じです（ただし、最初の式では は33番目のコロンですが、この式では最後結腸）

明らかにこの式はない保持したいテキストにコロンが含まれている場合でも正しく機能します。

関連情報