エクセルのデーターを複数の規則にしたがって－で区切りたい。

質問

エクセルのデーターを複数の規則にしたがって－で区切りたい。

社員名簿で社員番号がＡ列に有ります。
コンピューターから吐き出したデータなので以下の５種類があります。

１．○○○○○○○○○○○○○○
２．○○○○○－○○○○○
３．○○○○○○○○－○○○○○
４．７○○○○○○○○○○○○○
５．７Ａ○○○○○○○○○○○○

会社のいろいろな帳票類や書類に記載するには電話番号のように－で区切って記入
をしなければならず名簿もそれに合わせたいです。
法則は以下のようになります。

１．○○○○○○○○○○○○○○　→　○○○－○○○○○－○○－○○－○○
２．○○○○○－○○○○○　→　○○○○○－○○○○○
３．○○○○○○○○－○○○○○　→　○○○－○○○○○－○○○○○
４．７○○○○○○○○○○○○○　→　７○○○○－○○○○○－○○－○○
５．７Ａ○○○○○○○○○○○○　→　７Ａ○－○○○○○○○○○○○

です。○の部分は英字だったり数字でそれは規則性は無くランダムに入っています。
（どこが英字でどこが数字か不明）

Ａ列に５，０００件のデータが入っていて上記５種類がランダムに羅列されています。
Ｂ列に上記の変換法則にしたがって変換表示をしたいのでＢ列にその仕組みを入れたいです。

Ａ列でソートして５種類で並びかえをして５分割し、REPLACE文を５種類作成したらできました。
ですが１種類の計算式でこの５種類を変換させるのが希望です。
データー更新のたびにソートして作った５種類の式から選んで登録だと
間違える可能性もありますし手間です。
Ａ列はソートしないでＢ列の先頭に式を入れてオートフィルで最後列までコピーしたいです。
ＩＦ文などを使ってもうまくできません。
教えていただけるとありがたいです。お願いします。

回答No.7

KURUMITO · ベストアンサー

文字が半角でも全角でもかまいません。Ｂ１セルに次の式を入力して下方にオートフィルドラッグしてみてください。

=IF(A1="","",IF(ISERROR(FIND("-",ASC(A1))),IF(LEFT(ASC(A1),1)<>"7",MID(A1,1,3)&"-"&MID(A1,4,5)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2),IF(LEFT(ASC(A1),2)="7A",MID(A1,1,3)&"-"&MID(A1,4,11),IF(LEFT(ASC(A1),1)="7",MID(A1,1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2),""))),IF(FIND("-",ASC(A1))=6,A1,MID(A1,1,3)&"-"&MID(A1,4,11))))

お礼

Ｂ１以外でも、Ｆ１でもＧ１でも完璧に変換できました。

なおＡ１がＡ２とかＡ３から開始の場合、
この式を全部選択してメモ帳に貼り付けて、
置き換えで「A1」→「A2」とか「A3」にすべて置き換え後に全文コピーして
対象セルに貼り付けるとＡ列の開始行がどこでも完璧変換対応できました。

　※他に回答していただいた式でも変換はできましたが上記のような開始行がずれた場合で
　　式内の文字を置き換えをした場合、おかしくなりました。
　　（Ａ１の文字以外も変更が必要なのかな？と思いました。私の操作ミスかも。）

それにＢ列以外でも使えてフレキシブルでした。
ありがとうございました。

補足

私の説明が不足でした。
パターン(2)と(3)で
パターン(2)は５－５とだけ説明でしたが頭が７と7Aがきた場合パターン（４）（５）と
条件が一部一致してしまいました。
パターン(3)は８－５で説明しましたが同じく頭に７と7Aが来た場合パターン（４）（５）の条件と
一部が一致してしまいました。申し訳ありません。
ですが教えていただいた式で全部完璧に変換できました。
全部で１０，０００行あり、パターン（１）～パタン（５）
「（２）は3種、（３）も３種」が混在していますが全部完璧に変換できました。
ありがとうございました。

(1) 　　12S1234R123456　　　　→　12S-1234R-12-34-56　→　○
(2) 　　G1234-56789 →　G1234-56789 →　○
(2)－２ 　　7W123-45678 →　7W123-45678 →　○
(2)－３ 　　7A123-45678　 →　7A123-45678 　　　　→　○
(3)－２ 　　71234567-12345 →　712-34567-12345 →　○
(3)－３ 　　7A123456-12345 →　7A1-23456-12345 →　○
(3) 　　12345678-12345 →　123-45678-12345 →　○
(4) 　　7123456Q123456 →　71234-56Q12-34-56 →　○
(5) 　　7A123456ABC123 →　7A1-23456ABC123 →　○

回答No.6

MackyNo1

文字列が半角で入力されているなら、あなたが使用された数式を以下のようにIF関数で条件分岐すればご希望の表示に変更できます。

=IF(LEN(A1)=12,A1,IF(LEFT(A1,2)="7A",REPLACE(A1,4,0,"-"),IF(LEFT(A1,1)="7",REPLACE(REPLACE(REPLACE(A1,6,0,"-"),12,0,"-"),15,0,"-"),IF(ISNUMBER(FIND("-",A1)),REPLACE(A1,4,0,"-"),REPLACE(REPLACE(REPLACE(REPLACE(A1,4,0,"-"),10,0,"-"),13,0,"-"),16,0,"-")))))

お礼

すいません。たぶん私が悪いのですが
２．○○○○○－○○○○○　→　○○○○○－○○○○○
だけおかしく変換されてしまいます。

ありがとうございました。

補足

実際にやって見ました。私の説明不測です。
すいません。

(1) 12S1234R123456　→ 12S-1234R-12-34-56　→ ○
(2) G1234-56789　　→ G12-34-56789 　　→　×
(2) 7W123-45678 　→ 7W123--4567-8 - 　→　×
(2) 7A123-45678 →　　7A1-23-45678 　→　×
(3) 71234567-12345 →　　71234-567-1-23-45 　→　×
(3) 7A123456-12345 →　　7A1-23456-12345 　→　○
(3) 12345678-12345 →　　123-45678-12345 　→　○
(4) 7123456Q123456 →　　71234-56Q12-34-56 　→　○
(5) 7A123456ABC123 →　　7A1-23456ABC123 　→　○

回答No.5

merlionXX

ANo2 merlionXXです。

> 申し訳ありません。すべて半角の英数字です。

回答に書いたとおり、全角でも半角でもOKにしたと思いますが、試してみましたか？
もし、働かないなら他の理由があるはずなのでお聞かせいただきたかったのですが。
回答の式を半角のみ対応にするならASC関数を除くだけです。

お礼

すいません。
全角か半角か不明とのメッセージでしたので
補足をしました。

最初に教えていただいた式で完璧に変換できました。

ありがとうございました。

補足

私の説明が不足でした。
パターン(2)と(3)で正しく変換できない時がありました。
パターン(2)は５－５とだけ説明でしたが頭が７と7Aがきた場合パターン（４）（５）と
条件が一部一致してしまいましたので変換が正しく行われません。
パターン(3)は８－５で説明しましたが同じく頭に７が来た場合パターン（４）の条件と
一部が一致してしまい正しく変換ができませんでした。
申し訳ありません。

(1) 12S1234R123456　→　　　12S-1234R-12-34-56　→　○
(2) G1234-56789 　 →　　　G1234-56789 →　○
(2) 7W123-45678 　 →　　　7W123--4567-8 - 　　→　×
(2) 7A123-45678　　　→　　　7A1-7A123-45678 　　→　×
(3) 71234567-12345　→　　　71234-567-1-23-45 　　→　×
(3) 7A123456-12345　→　　　7A1-23456-12345 　　→　○
(3) 12345678-12345　→　　　123-45678-12345 　　→　○
(4) 7123456Q123456　→　　　71234-56Q12-34-56 　　→　○
(5) 7A123456ABC123　→　　　7A1-23456ABC123 　　→　○

回答No.4

imogasi

関数式では５つの場合をIF関数などで判別し、また文字の切り出しの関数式を２から５個並べなければならず、書く気がしない。
VBAで場合分けしたいが、質問者は経験無いのかな。
＞ですが１種類の計算式でこの５種類を変換させるのが希望です。
＞IF 文などを使ってもうまくできません
IF関数のネストをすればできるはず。既に回答が出ている。ただ質問者の希望が、不可能でないが、常識的には、５０００行のデータに対して式が長すぎて無理ではないかと言うこと。
自分が独力で出来ないときは、ソートして５種類で並びかえをして５分割してでも、そのそれぞれにでも式を分けて入れる、のが世の当たり前で、なんでも思ったことが出来るはずだというのはおかしいし、人にいつも回答を頼れないはず。（本件は１回限りの作業ではないのか。）
ユーザー関数を作れば少しは式が簡単になりそう。
ユーザー関数の例
Function hiph(a)
p = InStr(a, "－")
s2 = Mid(a, 1, 2)
s1 = Mid(a, 1, 1)
Select Case s2
Case "７Ａ"
hiph = "７Ａ" & Mid(a, 3, 1) & "－" & Mid(a, 4, 3)
Case Else
Select Case s1
Case "７"
hiph = "７" & Mid(a, 2, 4) & "－" & Mid(a, 6, 5) & "－" & Mid(a, 9, 2) & "－" & Mid(a, 11, 2)

Case Else

Select Case p
Case 0
hiph = Mid(a, 1, 3) & "－" & Mid(a, 4, 5) & "－" & Mid(a, 9, 2) & "－" & Mid(a, 11, 2) & "－" & Mid(a, 13, 1)
Case 6
hiph = a
Case 9
hiph = Left(a, 3) & "－" & Mid(a, 4, Len(a) - 3)
End Select
End Select
End Select
End Function
使い方は
A1に質問の例があるとして
G1とかに=hiph(A1)　と入れて下方向に式を複写。
結果
○○○－○○○○○－○○－○○－○
○○○○○－○○○○○
○○○－○○○○○－○○○○○
７○○○○－○○○○○－○○－○○
７Ａ○－○○○
ただし文字は全角半角か、－の全角半角か、７Ａ○－○○○...の...は何かなど質問では、あいまいで、上記コードを修正が必要だろう。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

>Ａ列でソートして５種類で並びかえをして５分割し、REPLACE文を５種類作成したらできました。
提示していただければ、半角全角の判断が付き、正確な回答をしやすくなります

=IF(LEN(A1)=11,A1,
IF(OR(LEFT(A1,2)="7A",MID(A1,9,1)="-"),REPLACE(A1,4,,"-"),
IF(LEFT(A1,1)="7",REPLACE(LEFT(A1,10),6,,"-"),
REPLACE(REPLACE(LEFT(A1,10),4,,"-"),10,,"-"))
&"-"&REPLACE(RIGHT(A1,4),3,,"-")))

1. 文字数による判断
2. 「7A～」と9文字目の「-」の場合
3. 「7～」の場合(2.の場合を含んでいない) 左10文字の処理
4. 「7～」ではない場合の左10文字の処理
5. 3.4.における右4文字の処理

お礼

すいません。たぶん私が悪いのですが
２．○○○○○－○○○○○　→　○○○○○－○○○○○
だけおかしく変換されてしまいます。

ありがとうございました。

補足

申し訳有りません。半角です。

私の行った方法です。

セルＡ２に１．○○○○○○○○○○○○○○
セルＡ３に２．○○○○○－○○○○○
セルＡ４に３．○○○○○○○○－○○○○○
セルＡ５に４．７○○○○○○○○○○○○○
セルＡ６に５．７Ａ○○○○○○○○○○○○

があるとして作成したREPLACEは以下のとうりです。

セルＢ２に=REPLACE(REPLACE(REPLACE(REPLACE(A2,4,0,"-"),10,0,"-"),13,0,"-"),16,0,"-")
セルＢ３に=A3
セルＢ４に=REPLACE(A4,4,0,"-")
セルＢ５に=REPLACE(REPLACE(REPLACE(A5,6,0,"-"),12,0,"-"),15,0,"-")
セルＢ６に=REPLACE(A6,4,0,"-")

という感じです。

回答No.2

merlionXX

ご提示の例が全角なのか半角なのか判然としないので、どちらでもよいように作りました。
半角と決まっているならもっと簡単にできるのですが。

B1セルに
=IF(LEFT(ASC(A1),2)="7A",ASC(LEFT(A1,3)&"-"&RIGHT(A1,11)),IF(LEFT(ASC(A1),1)="7",ASC(LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)),IF(MID(ASC(A1),9,1)="-",ASC(LEFT(A1,3)&"-"&MID(A1,4,5)&RIGHT(A1,6)),IF(MID(ASC(A1),6,1)="-",ASC(A1),IF(LEN(A1)=14,ASC(LEFT(A1,3)&"-"&MID(A1,4,5)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)&"-"&RIGHT(A1,2)),"")))))

これでオートフィルで最後までコピーしてください。

お礼

ありがとうございました。
完璧に変換できました。

補足

申し訳ありません。すべて半角の英数字です。

回答No.1

chochobizo

数値になっていなくて文字列だと想定しての式です。

セルB1に下記の式を入れて、下へコピーして下さい。
とりあえず法則を全て埋め込んでみました。

=IF(LEN(A1)=11,A1,IF(ISERROR(FIND("-",A1,1))=FALSE,LEFT(A1,3)&"-"&MID(A1,4,5)&"-"&RIGHT(A1,5),IF(LEFT(A1,1)="7",IF(LEFT(A1,2)="7A",LEFT(A1,3)&"-"&RIGHT(A1,11),LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)),LEFT(A1,3)&"-"&MID(A1,4,5)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))))

お礼

私が悪いと思うのですが
２．○○○○○－○○○○○　→　○○○○○－○○○○○
だけおかしく変換されてしまいました。
ありがとうございました。

補足

申し訳ありません。
コンピューターから吐き出した場合
セルは「標準」になっています。