Excelのセルデータを日付に変わらないようにする

質問

Excelで、日本語の住所データを英語に変換しようとしています。

ASC関数などで数字を半角にするところまではしたのですが、
その後、セルの書式で「文字列」にしているにも関わらず
「丁目」「番地」などを置換すると「1-1-1」という感じのデータは、
強制的にに日付データになってしまいます。

対応方法などご存知の方いらっしゃいましたらご教示いただけましたら幸いです。
VBAで対処する方法でも大丈夫です。

回答No.6

intin · ベストアンサー

置換をした結果、
日付っぽい見た目になったデータが、勝手に日付に変わってしまうのは、
エクセルの仕様です。

ですから、
置換を使う限りはどうしようもありません。

VBAを使わない前提であれば、

下記のような感じで、
=substitute(substitute(A1,”丁目”,”-”),”番地”,”-”)
関数を使うといいですよ。

VBAを使っても、もちろんできると思います。

参考URL：

http://www.excelspeedup.com/jyuusyowochikan/

お礼

ご回答いただきましてありがとうございます。
仕様なのですね。
substitute関数についてもありがとうございます。
試してみたところ、おっしゃるとおり日付に置き換わることはありませんでした。

昼間こちらを見ることができなかったので試しに下記のようなコードをVBAで書いて対応しました。

Dim i As Long, str As String

str = "番地"

For i = 530 To 1743
If InStr(Cells(i, "W"), str) > 0 Then
Cells(i, "W") = Replace(Cells(i, "W"), str, "-")
End If
Next i

回答No.7

kagakusuki

>「丁目」「番地」などを置換すると「1-1-1」という感じのデータは、強制的にに日付データになってしまいます。

　それは、文字列の置き換えをするのに置換を使っているからです。
　関数を使って置き換えればその様な事は起きません。

=ASC(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"丁目","-"),"番地","-"),"号",))

お礼

ご回答ありがとうございます。

置換を使っているからだったのですね。
この関数の組み合わせだと大分一気に解決しますね。

回答No.5

hayasi456

ご使用のexcelは？

ASC関数の設定が原因？
シート全体をセルの書式で「文字列」にし
郵便番号、電話番号、Eメールアドレスの列はデータの入力規則　日本語で「オフ（英語モード）」にしています。
https://121ware.com/spsearch/public/app/servlet/qadoc?QID=016607
http://kokoro.kir.jp/excel/nkisoku_ime.html
住所、氏名などの列は「ひらがな」にしています。

都道府県市町村等と同じセルに「丁目」「番地」も入力すれば確実でしょう。
数字を半角にしたいならばデータの入力規則　日本語で　「半角カタカナ」を選択

ご存じかもしえませんが
郵便番号を入力し変換すると町名まで表示できます。
http://soft1.jp/trouble/w/w067.html
http://www.sakyou.com/ManualShop/Windows/08_EnterMode/10_Post-dictionary/17_Win7PRO/index.html

お礼

ご回答ありがとうございます。

郵便番号については、下記からCSVデータを用いて英語表記に変換していたのですが、問題はその後の「1丁目1番地1号」などの変換についてでした。
http://www.post.japanpost.jp/zipcode/download.html

回答No.4

bunjii

Excelのバージョンは？
住所データの番地のみのセルが有るのでしょうか？
>ASC関数などで数字を半角にするところまではしたのですが、・・・・「1-1-1」という感じのデータは、 強制的にに日付データになってしまいます。
元のデータが全角の文字列として１－１－１となっているものをASC関数で半角の文字列の1-1-1に変換できます。
=ASC("１－１－１")　→　1-1-1
Excel 2013で検証した結果では"１－１－１"の代わりにセル番地を指定しても強制的に日付データになりませんでした。
尚、Excel 2002でも検証しましたがASC関数で全角文字を半角文字に変換しても強制的に日付データにはなりませんでした。
あなたのExcelに不具合が生じているかも知れません。

お礼

いろいろと検証していただきましてありがとうございます。
Excelは、Windows版の2010です。
他の方の回答の通り、置換の場合には日付になってしまうようです。

回答No.3

msMike

》 「1-1-1」という感じのデータは、強制的にに日付データに…
貴方の当面のお悩みは感じられますが、それと「英語に変換」との関係は？

補足

dogs_catsさんのお礼に記載したとおりでございます。

回答No.2

dogs_cats

文字列で対応出来るはずですが、下記でも可能かと思います。

数式に&""をつけ加えると文字列になります。

一例は
Aに住所
Bに番地まで
Cに番地

C2式
=ASC(SUBSTITUTE(A2,B2,""))&""

B2式（不要な式ですが、念のため）
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A2)&1234567890))-1)

お礼

ご回答ありがとうございます。

「東京都千代田区千代田１丁目１番地１号」的なデータを分割、アルファベット表記にする必要があり、「１丁目１番地１号」の部分を「1-1-1」にする際に上記の問題が発生しておりました。

今回は、VBAにてReplace関数にて解決しましたが、
substitute関数も試した結果日付と認識されることはありませんでした。

回答No.1

kia1and2

数字の前に　’　記号をいれて入力すると、１－１－１って表示されます。

お礼

ご回答ありがとうございます。
これは存じておりすが、1000件以上あるので、置換等で対応したかったのですよね。