桁数の統一と不要な文字の削除の仕方

質問

桁数の統一と不要な文字の削除の仕方

数字の後に「-（ハイフン）1」や「-（ハイフン）２」が入力されている数字があり、
「-」以下の数字を表示させず、尚且つ数字の桁数（6桁）を統一する方法を探しています。

＜例＞
最初の数字　　　　　　　処理後
11111-1　　　　→　　　011111
11111-2　　　　→　　　011111
222222　　　　 →　　　222222
222222-1　　　 →　　　222222

当初「-1」、「-2」を置換で削除した後、ユーザー定義を「000000」にしていました。
ただ、置換処理が手間な為、関数等で簡単に処理できればと考えています。

宜しくお願い致します。

回答No.1

KURUMITO · ベストアンサー

Ａ1セルから下行にデータがあるとして、Ｂ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(ISERROR(FIND("-",A1)),TEXT(A1,"000000"),TEXT(LEFT(A1,FIND("-",A1)-1),"000000")))

お礼

回答ありがとうございました。
解決しました。

回答No.7

imogasi

表示形式で考えるより、値を変えてしまう関数を使う方が良かろう。
データ例
Ａ列　　　Ｂ列　　　Ｃ列
11111-1 11111 011111
11111-2 11111 011111
222222 222222 222222
222222-1 222222 222222
11223 11223 011223
234-2 234 000234
1122 1122 001122
Ｂ１の式は
=LEFT(A1&"-",FIND("-",A1&"-")-1)
ハイフンが無い場合でも同じ式になるようにするため&"-"をくっつけている。
Ｃ１の式は
=REPLACE("000000",6-LEN(B1)+1,LEN(B1),B1)
文字列000000をＢ列の値で、適当な桁位置から置換することで先頭部に０を補い
６桁に統一している。
最終結果を数値にしたければ、Ｃ列の式の最後に＊１を追加する。
判りやすいように、Ｂ列に一旦中間で出したが、１式でやりたい場合はＢ列の式をＣ列の関数式の中に組み込む。

お礼

回答ありがとうございました。
解決しました。

回答No.6

tom04

No.3です！
たびたびごめんなさい。

前回は大きく勘違いしていました。
無視してください。
何度も失礼しました。m(__)m

お礼

回答ありがとうございました。
解決しました。

回答No.5

keithin

＞数字の桁数（6桁）を統一する方法

数式でも
=TEXT(LEFT(A1,FIND("-",A1&"-")-1),"000000")
ぐらいですから簡単ですが，置換の方が簡単とは思います。

それとアタマゼロを補うのは書式で良くて，６桁じゃなくても数値でいいなら
=LEFT(A1,FIND("-",A1&"-")-1)*1
までにして，書式で000000を付けておけばいいです。

勿論-1や-2をいちいち置換してたらそりゃ大変ですが，列を選んでCtrl+Hして-*を何も入力しないに全て置換で出来ます。ま，でも手を入れるのがとにかくイヤなんだって事ならそれもチョイスですから，イイと思いますよ。

お礼

回答ありがとうございました。
解決しました。

結果は同じでもいろんなやり方があるんですね。
置換のショートカットの「ctrl＋H」は今後活躍しそうです。

回答No.4

MackyNo1

文字列関数だけで表示するなら以下のような数式で表示できます。

=TEXT(IF(LEFT(RIGHT(A1,2),1)="-",LEFT(A1,LEN(A1)-2),A1&""),"000000")

簡単に数式を説明すると、文字列の右２つを切り出した文字列の左１文字が「-」なら文字列全体から２文字を除いた左側の文字列を取得し、そうでない場合は元の文字列（数字が入力されていない場合は空白）を返し、その値をTEXT関数で６ケタ表示する式になっています。

お礼

回答ありがとうございました。
解決しました。

回答No.3

tom04

こんにちは！
一例です。
A1セルからデータがありB列に表示させるとします。
A列の右となりに1列挿入します。

B1セルの数式を
=IF(A1="","",TEXT(SUBSTITUTE(A1,"-",""),"000000")*1)
としてフィルハンドルの（＋）マークでダブルクリック、又はオートフィルで下へコピー

そして、このままA列を削除した場合はエラーになりますので、
B列すべてを範囲指定　→　コピー　→　A列を選択　→　形式を選択して貼り付け
→　「値」にチェックを入れ、OK
最後にB列を削除します。

以上、参考になれば幸いです。m(__)m

お礼

回答ありがとうございました。
解決しました。

回答No.2

KURUMITO

置換で行うこともできますね。
Ａ列を選択してから置換で検索する文字列に -*　と入力し、置換後の文字列は空白のままとします。
Ａ列の表示形式をユーザー定義で000000にすればよいでしょう。

お礼

回答ありがとうございました。
解決しました。

置換で「-＊」という方法があるとは知りませんでした。
勉強になりました。