Excelアルファベットを含む数値から文字に変換

Excelで質問です。
アルファベットを含む数値から文字に変換する方法
例えば、下記のような変換方法はありますでしょうか？
101 → りんご
1A2 → みかん
101,1A2 → りんご,みかん

No.1 回答者： axuaxua 回答日時： 2013/01/08 00:34

このPCではExcelが使用できないので大雑把になりますが、

数値と文字の対応表をシートに作成し、そこをlookup関数で参照すればいいと思います。

参考URL：http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/ …

No.2 回答者： keithin 回答日時： 2013/01/08 01:00

＞101 → りんご

＞1A2 → みかん

これだけなら、隣の列にＶＬＯＯＫＵＰ関数を並べてりんごやミカンを計算して表示させるのがふつーのエクセルの使い方です。

が、
＞101,1A2 → りんご,みかん

こういう何でもアリな事をしたいというのでしたら、一般的な簡単操作ではできません。



次の通りに操作してください。
手順：
シート２のＡ列に101や1A2を列記する
隣のＢ列にりんごやミカンを並べておく

シート１に101や1A2や101,1A2を記入する

ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
dim h as range
on error resume next
for each h in worksheets("Sheet2").range("A1:A" & worksheets("Sheet2").range("A65536").end(xlup).row)
worksheets("Sheet1").cells.replace what:=h.value, replacement:=h.offset(0, 1).value, lookat:=xlpart
next
end sub

ファイルメニューから終了してエクセルに戻る
ＡＬＴ＋Ｆ８を押し、マクロ１を実行する。


＃説明
１０１をりんごに、１Ａ２をみかんに、それぞれ丁寧に置換の操作をしていけば、特に難しい操作は何もなしに結果を得られます。
でも手を動かすのはメンドクサイので、マクロでそれを自動でつるつるっとやらせます。

No.3 回答者： KURUMITO 回答日時： 2013/01/08 07:29

101,1A2のような文字が有る場合には関数で対応するよりもマクロで置換の操作をするのがよいでしょう。

例えばＡ１セルからＡ100セルにかけてお示しのような文字が並んでいるとして、Ｄ1セルから下方に101,1A2のように文字が入力されておりＥ1セルから下方に対応するりんご、みかんなどの文字が並んでいるとしたら次のようにすればよいでしょう。
シート見出しで右クリックして「コードの表示」を選択します。
表示の画面の右側に次のコードを入力します。

Sub 置換()
Dim i As Integer
Range("a1:a100").Select
For i = 1 To Cells(Rows.Count, "d").End(xlUp).Row
Selection.Replace What:=Cells(i, "d"), Replacement:=Cells(i, "e"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
Range("a1").Select
End Sub

マクロの操作は「開発」タブの「マクロ」から「置換」を選択して「実行」をクリックすればよいでしょう。

No.4 ベストアンサー 回答者： tom04 回答日時： 2013/01/08 09:39

こんにちは！

横からお邪魔します。

↓の画像のようにSheet2に表があり、Sheet1のB列に表示するようにしてみました。
他の方々と同じようにVBAになってしまいます。

Alt+F11キー　→　メニュー　→　挿入　→　「標準モジュール」を選択　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）
尚、Sheet1のデータは1行目からあるとします。

Sub Sample1() 'この行から
Dim i As Long, k As Long, n As Long, c As Range, buf As String, myArray
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

wS1.Columns(2).ClearContents
For i = 1 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
buf = ""
If InStr(wS1.Cells(i, 1), ",") > 0 Then
myArray = Split(wS1.Cells(i, 1), ",")
For k = 0 To UBound(myArray)
Set c = wS2.Columns(1).Find(What:=myArray(k), LookIn:=xlValues, LookAt:=xlWhole)
n = c.Row
buf = buf & wS2.Cells(n, 2) & ","
Next k
wS1.Cells(i, 2) = Left(buf, Len(buf) - 1)
Else
Set c = wS2.Columns(1).Find(What:=wS1.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
n = c.Row
wS1.Cells(i, 2) = wS2.Cells(n, 2)
End If
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

No.5 回答者： gt-t 回答日時： 2013/01/08 13:47

こんにちは。

以前作った関数です。

---ここから、VBAのコード---

Function a_vlookup(s As String, r As Range, c As Integer, f As Boolean) As String
Dim ss() As String
Dim i As Integer
Dim aa As String
aa = ""
ss = Split(s, ",")
For i = LBound(ss) To UBound(ss)
On Error Resume Next
ii = CInt(ss(i))
If Err.Number = 0 Then
aa = aa & WorksheetFunction.VLookup(ii, r, c, f) & ","
Else
aa = aa & WorksheetFunction.VLookup(ss(i), r, c, f) & ","
End If
Err.Clear
On Error GoTo 0
Next
aa = Left(aa, Len(aa) - 1)
a_vlookup = aa
End Function


---コードここまで---

標準モジュールにコードを張り付けて、セルに一般の関数と同じように
=a_vlookup( … と入力します。
引数は、vlookupと同じです。（ただし、第4引数の省略は不可になっています。省略する場合はコードを改造してください。）

No.6 回答者： MarcoRossiItaly 回答日時： 2013/01/09 02:03

もしも、変換すべき文字列の種類数が少ない場合は、VBA ではなく手動での置換（Ctrl+H）でもいいですね。

置換したいセル範囲を選択した状態でこのショートカットキーを押し、表示されるダイアログ中のオプションボタンを必要に応じて押せば、No.2 さん・No.3 さんのコードと同じ処理ができます。