【Excel】数字を三桁毎に区切ってセルに振分る

Question

【A1】1,146,900,000
↓
【A2】【A3】【A4】【A5】
   　 　1　146　900　000

のように、売上金額を通常入力したものを4つのセルに振り分けたいのですが、
どのような関数を使えばよいでしょうか。

MIDなども使ってみたのですが
桁数が決まっていないので私のレベルでは手に負えませんでした・・。

どなたかご教示いただけないでしょうか。
よろしくお願い致します。

tom04 · Accepted Answer

No.6です。

＞【A2】
【A3】
【A4】
【A5】に分割ではなく
【B1】【C1】【D1】【E1】
に分割でした。

だとすると、前回の数式を利用してB1セルに
=MOD(INT($A$1/(10^(3*(4-COLUMN(A1))))),1000)
という数式を入れ、E1セルまでオートフィルでコピーしてみてください。

次に
＞上記事情でできる範囲でアレンジしてみたのですが、#NUM!エラーになってしまいました。
＞更に、ユーザー定義の中に000を見つけられませんでした

に関してですが、今回はB1セルは何もしないで
C1～E1セルを範囲指定　→　右クリック　→　セルの書式設定　→　ユーザー定義　→
「G/標準」となっている欄に、「G/標準」を消し、ご自身で
000　と入力します。
これでC1～E1セルが必ず3桁で表示されます。

こんなんではどうでしょうか？m（＿ ＿）m

kagakusuki · Answer

回答番号ANo.8です。

>【A2】
>【A3】
>【A4】
>【A5】に分割ではなく

>【B1】【C1】【D1】【E1】
>に分割でした。

それでしたら次の様な関数となります。

=IF(ISNUMBER(1/$A1+1/($A1=INT($A1))),IF(COLUMNS($B:B)>INT(LOG10($A1)/3)+1,"",MOD(INT($A1/10^(FLOOR(LOG10($A1),3)-(COLUMNS($B:B)-1)*3)),1000)),"")

或いは

=IF(ISNUMBER(1/$A1+1/($A1=INT($A1))),IF(COLUMNS($B:B)>ROUNDUP(LEN($A1)/3,0),"",RIGHT(LEFT(TEXT($A1,"#,##0"),FIND("@",SUBSTITUTE(TEXT($A1,"#,##0")&",",",","@",COLUMNS($B:B)))-1),3)+0),"")

上記のどちらかの関数をB1セルに入力してから、B1セルをコピーして、C1セルに貼り付けて下さい。
　次に、C1セルの書式設定の表示形式を、[ユーザー定義]の

000

として下さい。
　その上で、C1セルをコピーして、C1セルよりも右側にあるセル（D1、E1等）に貼り付けて下さい。

gyouda1114 · Answer

No.4、No.9です。
頭に00が付いてしまいますのでA2の表示形式は、標準として下さい。

gyouda1114 · Answer

No.4です。
頭に0がつくと表示されなくなります。
A2～A5すべての表示形式をユーザー定義の種類を000
にして下さい。

kagakusuki · Answer

何故、【B1】【C1】【D1】【E1】ではなく、【A2】【A3】【A4】【A5】なのかは解りませんが、A2セルに次の関数を入力してから、A3以下に貼り付けると良いと思います。

=IF(ISNUMBER(1/A$1+1/(A$1=INT(A$1))),IF(ROWS($2:2)>INT(LOG10(A$1)/3)+1,"",MOD(INT(A$1/10^(FLOOR(LOG10(A$1),3)-(ROWS($2:2)-1)*3)),1000)),"")

或いは

=IF(ISNUMBER(1/A$1+1/(A$1=INT(A$1))),IF(ROWS($2:2)>ROUNDUP(LEN(A$1)/3,0),"",RIGHT(LEFT(TEXT(A$1,"#,##0"),FIND("@",SUBSTITUTE(TEXT(A$1,"#,##0")&",",",","@",ROWS($2:2)))-1),3)+0),"")

mike_g · Answer

添付図参照

Sheet2 において、
A1: =TEXT(Sheet1!A1,"#,##0")
A2: =IF(ROW()<LEN(A$1)-LEN(SUBSTITUTE(A$1,",",""))+2,SUBSTITUTE(A1,Sheet1!A2&",",""),"")
セル A2 をズズーッと下方に（例えばセル A10 まで）ドラッグ＆ペースト
範囲 A1:A10 をズズーッと右方にドラッグ＆ペースト

Sheet1 において、
セル A2 に次式を入力して、此れをズズーッと下方に（例えばセル A10 まで）ドラッグ＆ペースト
=IF(ISERROR(FIND(",",Sheet2!A1)),Sheet2!A1,LEFT(Sheet2!A1,FIND(",",Sheet2!A1)-1))
範囲 A2:A10 をズズーッと右方にドラッグ＆ペースト

tom04 · Answer

こんばんは！
一例です。
A1セル12桁まで対応できます。

A2セルの表示形式はユーザー定義から　000　としておきます。

A2セルに
=MOD(INT($A$1/(10^(3*(4-ROW(A1))))),1000)
という数式を入れA5セルまでオートフィルでコピーではどうでしょうか？m（＿ ＿）m

keithin · Answer

ご相談のヤリタイ事でちょっとやっかいなのは，たとえばA1に
1,023,400,050
のように数字が有った場合，２カタマリ目は「０２３」のような先頭のゼロが必要ですが，
　23,400,050
のような数字では「２３」のように先頭のゼロを無くしたい，

そういう操作を「数値の書式設定では原則として出来ない」点です。
（参考：Excel2007以降では，条件付き書式を使って出来ます）

というワケで，結果の数字を「文字列」で表示させてみます。
A1に元の数字として
B1に
=MID(TEXT($A1,"????????????"),(COLUMN(A1)-1)*3+1,3)
のように記入し，E1までコピーして貼り付ける。

B1:E1はセルの配置を「右寄せ」にする。

gyouda1114 · Answer

A2に　=IF(A1>=1000000000,INT(A1/1000000000),"")
A3に　=IF(A1>=1000000000,INT(MOD(A1,1000000000)/1000000),"")
A4に　=IF(A1>=1000000,INT(MOD(A1,1000000)/1000),"")
A5に　=IF(A1>=1000,INT(MOD(A1,1000)),"")

A5の表示形式を　ユーザー定義　000

数値の社員コードに自動で前ゼロをつける
http://kokodane.com/tec49.htm

m_and_dmp · Answer

やったことはありませんが、
A5は、＝Right（A1,３）
A４は、=Rounddown(Right(A1,6)/1000,0)
A3は、=Ronddown(Right(A1,9)/1000000,0)
A2は、=Rounddown(Right(A1,12)/1000000000,0)

INT関数なら
A4は、=Int(Right(A1,6)/1000)

今エクセルを開けない状況なのでINT関数の方はこれでよかったかどうか自信がありませんので調べてください。

【Excel】数字を三桁毎に区切ってセルに振分る

No.6です。

回答番号ANo.8です。

No.4、No.9です。

No.4です。

何故、【B1】【C1】【D1】【E1】ではなく、【A2】【A3】【A4】【A5】なのかは解りませんが、A2セルに次の関数を入力してから、A3以下に貼り付けると良いと思います。

この回答への補足

添付図参照

この回答への補足

こんばんは！

この回答への補足

ご相談のヤリタイ事でちょっとやっかいなのは，たとえばA1に

この回答への補足

A2に =IF(A1>=1000000000,INT(A1/1000000000),"")

この回答への補足

やったことはありませんが、

このQ&Aを見た人はこんなQ&Aも見ています

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

このQ&Aを見た人がよく見るQ&A

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

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

　回答番号ANo.8です。

　何故、【B1】【C1】【D1】【E1】ではなく、【A2】【A3】【A4】【A5】なのかは解りませんが、A2セルに次の関数を入力してから、A3以下に貼り付けると良いと思います。

A2に　=IF(A1>=1000000000,INT(A1/1000000000),"")