【関数】適切な文字数の数字を取り出したい

Question

【やりたい事】

A1セルに以下の数字があります。

やりたい事は、B1セルに関数を入れ

鬼滅の刃ケース１であれば、1,999,999を
ケース２であれば、299,999を
取り出したいです　左から２番目の数字です

ご存じの方、教えて下さい

ケース１　A1セル
契約の金額  1,000,000   1,999,999　12345　0　0


ケース2　A1セル
契約の残高  200,000   299,999　123456　0


条件：取り出し数字は、左から数えて、２番目にある数字です
スペースが必ずあります。スペースの文字数に規則性なしです

【考えた関数】
=MID(A1,20,10)

ケース１の場合はうまく取り出せますが、ケース２だと
うまう、299,999を取り出す事が出来ません

goomania · Accepted Answer

No.2,3,7です。

>,,　は　,　２つではなく、１つでないでしょうか？
>ASC(A1)　　" "　　TRUE　　の３つを区切るだけかと
>思うのですが。

とのご質問ですが、そもそも関数の引数の概念を勘違いしておられます。
関数の引数の間の「,」は単に引数を区分するだけのものではありません。
ですから引数の区切りが判ればよいというものではなく、引数は原則として定められた位置に、定められた意味を持たせて配置しなければ正しい結果が返りません。そうでない場合、結果が正しく見えていても、それは偶然です。

今回のご質問では、
契約の金額 1,000,000 1,999,999　12345　0　0･･････(a)
という文字列なので、間のスペースが１文字分しかありません。しかし、
>スペースが必ずあります。スペースの文字数に規則性なしです
との説明があるので、1,999,999の前にスペースが2つあり
契約の金額 1,000,000 　1,999,999　12345　0　0･･････(b)
となることもあるかも知れないという前提になります。

添付画像①をご覧ください。
A1セル(a)のデータがあったとき、B1にご質問者の仰っている「,」を1つにした数式
=INDEX(TEXTSPLIT(ASC(A1)," ",TRUE),3)･･････(c)
を入れると
「1,999,999」が返るのでこの数式は一見間違っていないように見えます。

しかし画像②のように、A1セル(b)のデータがあったとき、B1に(c)の数式を入れると、B1は空白になってしまいます。
このように(c)の数式はご質問の意図と合致していないことになります。
この理由は前回回答で説明した、TEXTSPLITの引数の解説をよく読んでいただければ判ります。

TEXTSPLIT関数の第一引数は横方向の区切文字、第二引数は縦方向の区切文字ですから(c)の数式の意味は、【ASC(A1)という文字列、つまり元のA1セルの文字列を半角に変換できるものは半角に変換し、「半角スペース」で横方向に分割し、「TRUE」という文字で縦方向に分割する。】という意味になってしまいます(TRUEが空白を無視するという意味ではなくなってしまいます)。

従って画像③のように「空白」は無視されず、「契約の金額」「1,000,000」「スペース(空白)」「1,999,999」「12345」「0」「0」の7つの要素に分割されることになり、左から３つ目つまり「空白」が返ってしまうのです。

実際のデータでは起こらないとは思いますが、もしA1の文字列に「TRUE」が混入していたらどうなるでしょうか?
画像④をご覧ください。A1のデータにTRUEが混入しているとTRUEで改行(縦方向に分割)されることになるので、TRUEが2つあると３行目が全体が返るこになり、左から3つ目だった筈が上から3つ目になっています。まったく想定外の結果ではありませんか?
ですから、関数の引数は定められた位置に、定められた意味を持たせて配置するのが原則です。

しかし、厳密でないものもあります。
前回回答の数式
=INDEX(TEXTSPLIT(ASC(A1)," ",,TRUE),3)･･････(d)
のINDEX関数は=INDEX(配列,行番号,列番号)のように記述するルールです。横方向に分割する場合、列番号は第二引数ですから、本来なら=INDEX(配列,,列番号)のように「,」を2つつけるべきだということになります。しかし、(d)の数式は=INDEX(配列,列番号)という表記なのに、正しい結果を返します。ちなみに
=INDEX(TEXTSPLIT(ASC(A1)," ",,TRUE),,3)
のように「,」を2つにしても、数式の結果は変わりません。

tatsumaru77 · Answer

自作関数になります。
１．以下のマクロを標準モジュールに登録します。
Public Function MySplit(ByVal str As String, ByVal num As Long) As String
    Dim elms As Variant
    Dim elm As Variant
    Dim i As Long: i = 0
    elms = Split(str, " ")
    For Each elm In elms
        If elm <> "" Then
            i = i + 1
            MySplit = elm
            If i = num Then Exit Function
        End If
    Next
    MySplit = ""
End Function

２．次に、B1セルに以下の式を入力します。
=MySplit(A1,3)

これで、完了です。
A1セルの左から3番目の文字のかたまりを表示します。

左から１番目（"契約の金額"）を表示したいなら、
=MySplit(A1,3)　と入力します。

左から2番目（"1,000,000"）を表示したいなら、
=MySplit(A1,2)　と入力します。

goomania · Answer

No.2,3です。
>本件は、特に、分割せずに、２番目の数値をきれいに取り出ししています
>関数の最後の３が何を意味しているのかも知りたいです
とのご希望ですので、簡単に解説します。

TEXTSPLIT関数は

=TEXTSPLIT(分割対象文字列,横方向に分割する区切り文字,縦方向に分割する区切り文,TRUE=空白を無視する/FALSEまたは省略=空白を無視しない,0または省略=大文字と小文字を区別する/1=大文字と小文字を区別しない,各行の横方向のデータ不足時に表示する値。省略時は「#N/A」)

という引数を指定して使用します。
今回の例では、TEXTSPLIT(ASC(A1)," ",,TRUE)という数式ですから、

ASC(A1)という文字列、つまり元のA1セルの文字列を半角に変換できるものは半角に変換し、「半角スペース」で横方向に分割し、空白は無視する。

という数式だということになります。A1セルの文字列が
契約の金額 1,000,000 1,999,999　12345　0　0
だとすると、間のスペースが幾つあっても分割後は空白になるので、無視されて、
「契約の金額」「1,000,000」「1,999,999」「1234」「0」「0」
という6つの要素に分解されることになります。

INDEX関数はこれらの要素を配列として扱うためのものです。
INDEX(一次元配列,要素番号)すると配列の中から要素番号で指定された要素を返します。従って、

=INDEX(TEXTSPLIT(ASC(A1)," ",,TRUE),3)

は上記の6つの要素の左から3番目の要素である「1,999,999」が返ることになります。

tatsumaru77 · Answer

提示されたサンプルで
契約の金額 1,000,000 1,999,999　12345　0　0
は、全角のスペースと半角のスペースが混在しています。

以下のようになっています。
契約の金額 1,000,000 1,999,999□12345□0□0
（□は全角のスペースとします）
本当に全角のスペースと半角のスペースが混在していますか？
半角のスペースだけなら簡単ですが、全角のスペースと半角のスペースが混在していると多少厄介です。

mimazoku_3 · Answer

①今回の元データ
②データの長さを計算：LEN関数
③抽出するデータの長さを計算：上記の数値より［－９］する。
④MID関数で元の文字列から必要部分を抽出。
⑤ASC関数で文字列にする。
⑥NUMBERVALUE関数で数値化。
⑦LEN関数で文字列の長さを計算。
⑧上記の長さを２で割る。
⑨LEFT関数で最初の数値に該当する部分を抽出。
⑩RIGHT関数で最初の数値に該当する部分を抽出。
⑪⑨での文字列を数値に変換。：NUMBERVALUE関数
⑫⑩での文字列を数値に変換。：NUMBERVALUE関数

⑪、⑫でようやく数値で扱いが可能になる。

【使った関数の実際】
②、=LEN(A2)
③、=B5-9
④、=MID(A2,1,C5)
⑤、=ASC(E5)
⑥、=NUMBERVALUE(F5)
⑦、=LEN(G5)
⑧、=H5/2
⑨、=LEFT(G5,I5)
⑩、=RIGHT(G5,I5)
⑪、=NUMBERVALUE(J5)
⑫、=NUMBERVALUE(K5)

●エクセルは、２０１９です。

途中に変な変換をしているようになっていますが、２０桁を超えると数値変換に誤差が出ていたので、こういう結果になりました。
追加情報は、データの右側のバイト数が固定なのか？と思い、お尋ねしました。

オヤジの頭じゃ、これが限界でした。

■黄色いセルは、失敗部分です。

fujillin · Answer

こんばんは

＞バージョン365です
とのことですので、No2様が言及しておられるTEXTSPLITを利用して
　=INDEX(TEXTSPLIT(TRIM(SUBSTITUTE(A1,"　"," "))," "),3)
ではいかがでしょうか？

goomania · Answer

No.2です。
>バージョン365です
とのことなので、添付画像をご覧ください。
TEXTSPLIT関数が使えるので、B1セルに

=INDEX(TEXTSPLIT(ASC(A1)," ",,TRUE),3)

を入れて下方向へコピーしています。
これで、ご覧のような結果になります。

ちなみに、365でもFILTERXML関数は使えるはずですが、WEB版だと「#VALUE」エラーになってしまいます。

goomania · Answer

>スペースが必ずあります。スペースの文字数に規則性なしですとのことなので、A1セルの文字列をスペースで分割して、先頭部分の数字でないものも含めて左から3番目の数字を抽出することにします。文字列を分割するのに適した関数はTEXTSPLIT関数ですが、バージョン365でしか使えません。ご質問者のEXCELのバージョンが不明なので、2013以降であれば使えるFILTERXML関数を使うことにして、B1セルに =INDEX(FILTERXML(""&SUBSTITUTE(TRIM(ASC($A1))," ","")&"","//y"),3) を入れれば、ご希望の結果になると思います。

mimazoku_3 · Answer

お尋ねしたい事があります。

1,000,000 1,999,999　12345　0　0　と
200,000 299,999　123456　0　について、他の数値はありますか？
あれば、５～６個教えてください。

【関数】適切な文字数の数字を取り出したい

No.2,3,7です。

自作関数になります。

No.2,3です。

提示されたサンプルで

①今回の元データ

こんばんは

No.2です。

>スペースが必ずあります。

お尋ねしたい事があります。

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング