☰

先頭にダッシュが付いたテキスト文字列を負の数に変換する

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
先頭にダッシュが付いたテキスト文字列を負の数に変換する

私が利用しているオンライン ブローカーの Web サイトは、世界で最も機能不全に陥っているに違いありません。このブローカーは「エクスポート」機能を提供しておらず、その代わりに、ブラウザーでレンダリングされたテーブルからデータをコピーして Excel に貼り付けるだけでよいとユーザーに伝えています。(本当です!)

驚いたことに、Excel (2016、Mac 版) は、「正のテキスト数値」を数値関数 (SUM通常) で操作できる実際の数値に変換するように誘導できます。ただし、「負のテキスト数値」は、負の数値に変換するためのすべての努力を無視していることがわかりました。

「正のテキスト番号」は 123.45 ドルです。
「負のテキスト番号」は -$543.21 です (先頭のテキスト文字に注意してくださいhyphen)

編集：
「ネガティブテキスト番号」は： - $299.55（主人公がないa hyphen; それはem dash
混乱させてしまい申し訳ありません...数字だらけのウェブページem dashで の代わりに を使うプログラマーはどんな人なのか疑問に思っていますが、それは私のminus sign/hyphenオンラインブローカー。

セル A1 の「正のテキスト数値」を次のように変換できます。=VALUE(RIGHT(A1,6))

しかし、これは「負のテキスト数値」では機能しません。数値は正しくなりますが、負の符号が失われます。値を変更してみましたcharsが、役に立ちませんでした。

以下のことも試しましたが、成功しませんでした:
1. 1 を掛ける
2. テキストを列に分割する
3. セルをフォーマットする...

手動で再入力する以外に、これを行う方法はありますか?

答え1

簡単な解決策を見つけたと思います。先頭の em ダッシュがあるセルの 1 つで、em ダッシュだけを強調表示してコピーします。Excel の「検索と置換」機能を使用して、em ダッシュを「検索する文字列」ブロックに貼り付けてから、「置換後の文字列」ブロックにマイナス記号を入力します。変換するすべてのセルに置換を適用します。

答え2

セルA1に文章「$123.45」または「-$543.21」の形式の場合、次の数式を使用して数値を取得できます。

=IF(LEFT(A1,1)="-",0-VALUE(RIGHT(A1,LEN(A1)-1)),VALUE(A1))

テキストが「-」で始まる場合、「-」以降のすべての値が取得され、負の値になります。

答え3

私のおすすめ:

  1. 符号の後の値を抽出し$、固定位置は使用しない
  2. 符号の直前のシンボルをチェックして値の符号を確認します$。

つまり、式は次のようになる。

=IF(IF(ISERROR(FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>")),3,FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>"))<3,-MID(A1,FIND("$",A1)+1,LEN(A1)),0+MID(A1,FIND("$",A1)+1,LEN(A1)))

どこ

"<dash><hyphen>"- 負の符号が付く可能性のあるリテラル（プレースホルダーを任意の順序で適切な文字に置き換えて、示されているケースでは 2 文字のリテラルを取得します）

3- 前のリテラルの文字数より大きい数

Z1これらの値は、すべての可能な「マイナス」記号 (それぞれおよびなど1+LENGTH(Z1))を含むセルへの参照に置き換えることができます。

更新。数式をデバッグ中。

空のワークシートにテーブルを作成します。

A 列に 4 つの異なる値を挿入します (先頭のスペースなし/あり、正/負)。

以下の数式を挿入します。

Cell          Formula                      Meaning
-------------------------------------------------------------
$B$1          =IF(I1,-G1,0+G1)             Result
$C$1          =LEN(A1)                     Data length in source
$D$1          =FIND("$",A1)                $ position in source
$E$1          =MID(A1,D1-1,1)              symbol before $
$F$1          =FIND(E1,"<dash><hyphen>")   symbol before $ position in hyphens list
$G$1          =MID(A1,D1+1,C1)             number from source
$H$1          =ISERROR(F1)                 correction when no symbol before $ in list
$I$1          =IF(H1,3,F1)<3               define is a source positive or negative

この数式パックを 2 ～ 4 行目にコピーします。次のような出力が得られることを確認します。

     A        B      C   D   E      F       G    H       I
1    $123     123    4   1   #VAL!  #VAL!   123  TRUE    FALSE   
2    -$234    -234   5   2   -      1       234  FALSE   TRUE    
3      $345   345    6   3          #VAL!   345  TRUE    FALSE   
4      -$456  -456   7   4   -      1       456  FALSE   TRUE

列 CI には単一の計算手順が含まれ、列 B にはすべての置換を含む最終的な数式が含まれます。

計算失敗はどこですか？

関連情報