エクセル文字の振り分け方法とは？
- VBAまたは関数を使用して、エクセルのセル内の文字を抽出し、指定のセルに振り分ける方法について教えてください。
- 文字の抽出にはRIGHT、MID、LEFT、FIND関数を組み合わせているがうまくいかない状況にあります。
- 具体的な例として、名前がセルに入力されており、その名前を姓・名・フリ姓・フリ名の4つのセルに振り分けたい場合について質問しています。
- ベストアンサー
エクセル 文字の振り分け
すみません、教えて頂けますでしょうか。 VBAまたは関数で、１つのセル内にある文字を抽出して、 ４つのセルにそれぞれ振り分けたいのですが、 RIGHT、MID、LEFT、FINDを組合せてもうまくできなくて困っています。 例えば１つのセルに、 A1 名前 ：松下 小太郎(マツシタ コタロウ) A2 名前 ：山田 佐吉(ヤマダ サキチ) のように入っていた場合に、別シート・同一シートのどちらでも良いので、 姓 名 フリ姓 フリ名 B1 松下 C1 小太郎 D1 マツシタ E1 コタロウ B2 山田 C2 佐吉 D2 ヤマダ E2 サキチ としたいのですが、松下小太郎が松下まで取り出せても、 名前の長さが変わると、 B2山田 佐 C2 吉 ヤ のようになったりしてしまいます。 VBAでは、 '購入者『姓』 '入力シート“購入者名”の値を変数に格納 myStr = Worksheets("入力シート").Range("B9").Value '変数から"："を検索して、右側を抜出して格納し直す myStr = Right(myStr, Len(myStr) - InStr(1, myStr, "：")) ：のところまで左を削る myStr = LEFT(myStr, Len(myStr) - InStr(1, myStr, " ")) 左からスペースまで削る myStr = LEFT(myStr, Len(myStr) - InStr(1, myStr, " ")-1) ・・・・ のようにmyStrに入れた変数を再度削るという風に考えて やってみたのですが、できませんでした。 (上記コードはざっくりとこんな感じにしましたという イメージで書きましたので合っていません。) みなさま、お忙しい最中と思いますが、 何卒お力添えを頂けませんでしょうか？ よろしくお願い致します。
- queschoooon
- お礼率97% (46/47)
- Excel（エクセル）
- 回答数4
- ありがとう数4
- みんなの回答 （4）
- 専門家の回答
質問者が選んだベストアンサー
関数で処理してみました。 B1=MID(A1,FIND("：",A1)+1,FIND(" ",MID(A1,FIND("：",A1),LEN(A1)))-2) C1=LEFT(SUBSTITUTE(A1,LEFT(A1,FIND("：",A1))&B1&" ",""),FIND("(",SUBSTITUTE(A1,LEFT(A1,FIND("：",A1))&B1&" ",""))-1) D1=LEFT(SUBSTITUTE(A1,LEFT(A1,FIND("(",A1)),""),FIND(" ",SUBSTITUTE(A1,LEFT(A1,FIND("(",A1)),""))-1) E1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND("(",A1)),""),D1&" ",""),")","") B1からE1までを選択してオートフィルコピーで下へドラックしてください。 尚、A列の未入力についてはチェック機能を付けていません。 また、提示の模擬データと異なるパターンでは正常に区分けができないでしょう。
その他の回答 (3)
- kagakusuki
- ベストアンサー率51% (2610/5101)
関数を使う方法で、「：」や「(」、「)」及び途中に含まれている空白スペースが、半角文字となっているデータと、全角文字となっているデータが混在している場合や、途中に含まれている空白スペースの数が異なっている(一見すると全角スペースの1文字に見えて、実際は半角スペース2文字である場合等)データが混在している場合であっても抽出可能な方法です。 まず、B1セルに次の関数を入力して下さい。 =IF($A1="","",TRIM(REPLACE(LEFT($A1,FIND(" ",SUBSTITUTE($A1," "," ")&" ",FIND(":",ASC($A1)&":"))-1),1,FIND(":",ASC($A1)&":"),))) 次に、C1セルに次の関数を入力して下さい。 =IF($A1="","",TRIM(REPLACE(LEFT($A1,FIND("(",SUBSTITUTE($A1,"（","(")&"(")-1),1,FIND(" ",SUBSTITUTE($A1," "," ")&" ",FIND(":",ASC($A1)&":")),))) 次に、D1セルに次の関数を入力して下さい。 =IF($A1="","",TRIM(REPLACE(LEFT($A1,FIND(" ",SUBSTITUTE($A1," "," ")&" ",FIND("(",SUBSTITUTE($A1,"（","(")&"("))-1),1,FIND("(",SUBSTITUTE($A1,"（","(")&"("),))) 次に、E1セルに次の関数を入力して下さい。 =IF($A1="","",TRIM(REPLACE(LEFT($A1,FIND(")",SUBSTITUTE($A1,"）",")")&")")-1),1,FIND(" ",SUBSTITUTE($A1," "," ")&" ",FIND("(",SUBSTITUTE($A1,"（","(")&"(")),))) 次に、B1～E1のセル範囲をコピーして、B列～E列の2行目以下に貼り付けて下さい。
お礼
kagakusuki 様 この度はとても丁寧にご教示くださいまして誠にありがとうございました。 この数時間で４件もご回答を頂けまして本当にうれしく思います。 どなた様も本当に素晴らしいご回答を下さいましたが、kagakusuki様の データが混在していてもできる方法は本当に助かります。 いろいろ試しながら一番良いカタチにしていこうと思っています。 どなた様もベストアンサーなので選ぶことができず、今回は 一番初めにご回答を下さったbunjii 様を選ばせて頂きました。 本当に皆様のおかげ様で解決することができまして、 全員がベストアンサーだと思っております。 kagakusuki様のご厚情に心より御礼申し上げます。 また何かの際にもぜひとも宜しくお願い申し上げます。 本当にありがとうございました。
- dogs_cats
- ベストアンサー率38% (278/717)
VBAの一例です。 氏名は漢字、カタカナ共に全角スペースで区切られてるものとする。 カタカナの（）は両方共に半角かっこであるものとしています。 Sub test() Dim Maxrow As Long Dim tmp() As String Maxrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To Maxrow If Cells(i, 1).Value <> "" Then tmp = Split(Cells(i, 1).Value, " ") Cells(i, 2).Value = tmp(0) Cells(i, 3).Value = Mid(tmp(1), 1, InStr(tmp(1), "(") - 1) Cells(i, 4).Value = Right(tmp(1), Len(tmp(1)) - InStr(tmp(1), "(")) Cells(i, 5).Value = Replace(tmp(2), ")", "") End If Next End Sub split http://officetanaka.net/excel/vba/tips/tips62.htm splitは上記サイトの通り、指定区切り記号を一次配列に格納します。 要素番号は０から割り振られます。 松下 小太郎(マツシタ コタロウ)の場合 tmp(0)=松下 tmp(1)=小太郎(マツシタ tmp(2)=コタロウ) instr http://officetanaka.net/excel/vba/function/InStr.htm
お礼
dogs_cats 様 この度は唯一VBAのご回答ということで、本当に、本当にうれしく思います。 誠にありがとうございました。 私の勝手都合で誠に恐縮致しますが、時間が無くあまり試行ができない状態だっため、 どなた様もベストアンサーなのですが、今回ははじめにご回答を下さいました bunjii 様にさせて頂きました。 もちろん自身のためにもdogs_cats様の方法をしっかり行って 使いやすい方法を模索していきたいと思っています。 dogs_cats様のお気遣いに心より感謝申し上げます。 VBAはまだまだ不得手なため、教えて下さいました内容を しっかり読んで理解していきたいと思います。 この度は本当にありがとうございました。 また何かの際にもぜひとも宜しくお願い申し上げます。
- m_and_dmp
- ベストアンサー率54% (974/1797)
D6 に、 名前 ：松下 小太郎(マツシタ コタロウ) と記入されているとして、 =MID(D6,FIND("：",D6,1)+1,LEN(MID(D6,FIND("：",D6,1)+1,20))-LEN(MID(D6,FIND("(",D6,1),20))) これで、「松下 小太郎」を抽出することができます。 二つのLEN 関数で、「松」から末尾までの文字数と、「(」から末尾までの文字数の差を求め、Mid 関数の「文字数」として使います。 「マツシタ」、「コタロウ」も同じ手法で抽出することができると思います。 関数では複雑になりますので、VBA でユーザー定義関数を作ると良いと思います。
お礼
m_and_dmp 様 この度は早々にもご回答を頂きまして本当にありがとうございました。 少しLEN関数などの使い方に不得手がありまして、 松下 小太郎を抽出できたのですが、時間がありませんで各セルに どのように分ければ良いかまでは手が出ませんでした。 ですが、このようにご教示頂けたこと本当にうれしく思います。 また機会があるかと思いますが、その際にもぜひとも ご指導いただけましたなら幸いです。 本当にありがとうございました。
関連するQ&A
- エクセルでアドレス帳を製作しているのですが・・・
エクセルでアドレス帳を作成しているのですが、 A1セル：氏名 B1セル：フリガナ C1セル：姓 D1セル：名 の項目名を入れてA2～100をアドレス帳として登録 【C2：姓】【D2：名】に入力した文字を【A2：氏名】の列に結合反映するようにA2～100に関数を=C2&D2&E2と登録しています。 例えば C2には 山田 D2には太郎 と入れたら A2には 山田太郎 と入力されます これをA2の姓と名の間に半角スペースを自動で入れてC2とD2に入力した文字のフリガナもB2に自動で入力させる方法を探しています A2 B2 C2 D2 山田太郎 ヤマダタロウ 山田 太郎 ↓ A2 B2 C2 D2 山田 太郎 ヤマダタロウ 山田 太郎 解かりずらい説明ですいませんが、アドバイスよろしくお願い致しますm(__)m
- ベストアンサー
- オフィス系ソフト
- エクセルVBAの文字列操作について2
エクセルVBAの文字列操作について2 以前、こちらでご教授いただいた以下のような文字列操作方法があります。 この方法ですと例えば[1-3]から3をひいた際に"1-2"と表示されますが 今回は連続する数字が2つの場合は1,2と表示させ3つ以上の場合は-でつないで表示させたいと思います。 一週間ほど考えたのですが解決できませんでした。 どなたかご協力お願いいたします。 質問内容 例えば、[１－１０,15-20,22-38]と入っているセルがあるとします。 このセルに数を足したり引いたりしたいのです。 例えば、このセルから”5”を引いて[１-4,6-１０,15-20,22-38]と表示したり、 "21"を足して[１－１０,15-38]と表示したい。 いただいたご回答 A1 セル に「1-10,12,15-20,22-38」と入力されているとして、別のセルに =NUMORDER(A1,-5) と入力すると「1-4,6-10,12,15-20,22-38」と表示し =NUMORDER(A1,21) と入力すると「1-10,12,15-38」と表示します。 １つ目の引数には「セル番地」または「文字列」を、２つ目の引数には「1 ～ 99 までの整数」をお入れください。 Function NUMORDER(myStr As Variant, num As Integer) As String Dim i As Long Dim j As Double Dim myNum As Variant '文字列中の スペース を削除 myStr = Replace(myStr, " ", "") '文字列の前後に「0」・「100」を挿入 Select Case Left(myStr, 2) Case "1,", "1-" myStr = myStr & ",100" Case Else myStr = "0," & myStr & ",100" End Select '文字列を カンマ で分割し、ハイフン の区間の数字を補完する myStr = Split(myStr, ",") For i = 0 To UBound(myStr) If InStr(myStr(i), "-") > 0 Then myNum = Split(myStr(i), "-") myStr(i) = "" For j = myNum(0) To myNum(1) myStr(i) = myStr(i) & " " & j Next myStr(i) = Trim(myStr(i)) End If Next '欠番に「●」を入れ、「数を足したり引いたり」する myStr = Split(Join(myStr)) For i = 0 To UBound(myStr) - 1 myStr(i) = myStr(i) & Application.WorksheetFunction.Rept(" ●", myStr(i + 1) - myStr(i) - 1) Next myStr = Split(Join(myStr)) If num > 0 Then myStr(num - myStr(0)) = num Else myStr(-num - myStr(0)) = "●" End If '前後に挿入した「0」・「100」を削除 myStr = Replace(Join(myStr), " 100", "") If Left(myStr, 2) = "0 " Then myStr = Right(myStr, Len(myStr) - 2) '連続数字を ハイフン で繋ぐ myStr = Split(myStr, "●") For i = 0 To UBound(myStr) If myStr(i) <> " " Then myNum = Split(Trim(myStr(i))) If UBound(myNum) > 0 Then myStr(i) = myNum(0) & "-" & myNum(UBound(myNum)) End If End If Next 'カンマ で文字列に分割する myStr = Application.Trim(Join(myStr)) NUMORDER = Replace(myStr, " ", ",") End Function
- 締切済み
- オフィス系ソフト
- エクセル・１つのセルに入った漢字（カナ）の分け方
エクセルにお強い方、教えてください＞＜ A1のセルに、 山田 花子（ヤマダ ハナコ） とあるとします。 私は B１のセルに 山田 花子 C1のセルに （ヤマダ ハナコ） と、分けてデータ化させたいのですが、 どのようにマクロを組めばよいでしょうか？ また、分け終わったC１の（ヤマダ ハナコ）に 置換を使って（ ）をとって、 カナ を かな に直すため、 PHONETICと書式のふりがな機能を使って直しても データにエラーは起きずに処理できるでしょうか？ 最終的なデータとして A1セル→山田 花子（やまだ はなこ） B1セル→山田 花子 C1セル→やまだ はなこ としたいのです。 ご存知の方いらっしゃいましたら、教えてください。 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルの関数について詳しい方、助けてください！
すみません、エクセルの関数について詳しい方、教えて頂けませんか？ Q1 A1セルに、 [商品名]：キャンディ イチゴ味[CAN-01] 100 円 x 10 個 [商品名]：もぐもぐグミ グレープフルーツ[MOGUGUMI-02] 80 円 x 5個 など、長さが違う文をペーストします。 B1～E1セルに計算式を入れ、A1セルを分けて入れたいです。 B1に、 キャンディ イチゴ味 C1に、 CAN-01 D1に、 100 E1に、 10 と入れる計算式が知りたいです。 Q2 A1セルに、 お名前 : 山田 太郎 (ヤマダ タロウ) 様 お名前 : 長谷川 太一郎 (ハセガワ タイチロウ) 様 など、長さが違う文をペーストします。 B1～C1セルに計算式を入れ、A1セルを分けて入れたいです。 B1に、 山田 太郎 C1に、 ヤマダ タロウ と入れる計算式が知りたいです。 MID？LEN？自分でもやってみたのですが、出来ません。。。 すごく困っています。よろしくお願い致します！
- ベストアンサー
- その他MS Office製品
- 関数で文字の抜き出し方教えてください
現在の状況では以下で使っています。 A列に下のセルがあったとして、 山田 太郎 半角スペースで区切って A列に =LEFT(A1,SEARCH(" ",A1,1)-1) で山田を抜き取り C列に =MID(C13,FIND(" ",C13)+1,LEN(C13)) で太郎を抜き取っています A1 B1 C1 山田 太郎 山田 太郎 しかし、 A1 佐藤 のように半角を使っていないものが#VALUEです。 A2 B2 C2 佐藤 ＃VALUE #VALUE となります。 A2 B2 C2 山田 太郎 山田 太郎 佐藤 佐藤 NULL のようにスペースで区切られていないものを表示しつつ スペースで区切られているものは分けて表示させたいのですが ご教授おねがいします。
- ベストアンサー
- オフィス系ソフト
- エクセルでのランダム抽出・並び替えについて
エクセル初心者です。オフィス2003を使用しています。 Sheet1のA1～A55まで５５行ある 各セルにバナナ、リンゴ、みかん・・・などの文字列があります。 これを Sheet2のB列に Sheet1からランダムに２０～２１個抽出し、 みかんリンゴバナナ・・・みたいに ランダムに並び替えたものを１０００個そろえたいと思っています。 http://oshiete1.goo.ne.jp/qa4993926.html で質問し、回答いただいたのですが、 実際の作業が、質問した内容から改変する必要があったため、 自分で改変してやってみると 「応答なし」の表示が出て、何度やってもうまくできません。 どうやら、上記ＵＲＬの質問では Ａ，Ｂ，Ｃといった各セル内の文字が１文字としていたために、 実際の作業のバナナ、リンゴなどといった文字数に対応できないようです。 自分で改変した以下のマクロの式を どのように改変したら、実際の作業に沿うようにできるのでしょうか？ ここから＊＊＊ Sub test02() Dim myRng As Range '変数宣言 Dim myDic As Object Dim x As Integer, myStr As String, v As String Randomize '乱数初期化 Set myRng = Sheets("マクロ用１").Range("A1:A55") 'データ範囲 Set myDic = CreateObject("Scripting.Dictionary") 'オブジェクト準備 Do Until myDic.Count = 1000 'ユニークで1000そろうまで x = Int(19 * Rnd) + 20 '乱数で桁数設定（20～21） myStr = "" Do Until Len(myStr) = x v = myRng(Int(55 * Rnd) + 1) '乱数で文字設定 If InStr(myStr, v) = 0 Then '文字列内で重複しなければ myStr = myStr & v '文字列につなげる End If Loop If Not myDic.exists(myStr) Then '単語が重複しなければ myDic.Add myStr, x '収録 End If Loop '繰り返し Sheets("マクロ用２").Range("B1").Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Keys) '転記 End Sub ここまで＊＊＊ 先の質問を締め切ってしまったため、 もう一度質問させていただきました。 ご回答よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルVBAで任意の文字列を抽出するには・・・
エクセル2003で作成した住所録があります。 県名（３文字）のみを抽出して、新たに設けたD列に表示させたいと考えています。 Sub 県名の列作成() Dim myStr As String myStr = ActiveCell.Value Range("D2").Value = Left(myStr,3) End Sub ここまで、できたのですが・・・・ B列の２行目から順に処理をして、一覧表の最後まで行って、 空白セルの行が見つかったら終了させる方法が分かりません。 どうかよろしくお願いします。
- ベストアンサー
- Visual Basic
- エクセルVBAで文字列の重複を削除する方法？
VBAでの質問でしゅ。エクセルは2000です。 変数MyStrに全角カンマで区切られた文字列データが１０種類程度あります。 現在、ある条件のもとに、MyStr=MyStr & Cell(i,2) & "、"のように取り込んで、最後にMyStr=Left(MyStr, Len(MyStr) - 1) で一番最後の「、」を削除しています。 例えば、MyStrの中身は、 OK建設株式会社、Goo工業合資会社、MM商事有限会社、AA合名会社、株式会社BB企画、OK建設株式会社、CC工業合資会社、DD商事有限会社、EE合名会社、株式会社BB企画 のような感じです。この中には重複したデータがある可能性があります。（例ではOK建設株式会社と株式会社BB企画） これを重複を排除し、変数MyStrの中身を、 OK建設株式会社、Goo工業合資会社、MM商事有限会社、AA合名会社、株式会社BB企画、CC工業合資会社、DD商事有限会社、EE合名会社 と変える方法はないでしょうか？ あるいは、変数 MyStrにデータを取り込む際にすでにMyStrに入っているデータは除くような方法でもかまいません。 お知恵をお貸しくださいませ。 (o。_。)oペコッ.
- ベストアンサー
- オフィス系ソフト
- データの個数の数え方【エクセルについて】
エクセル表のデータの個数の数え方について、どなたかご教授頂きたくお願い致します。 具体的に言うと、1つのシートに10万件（縦列セルＮｏ.1からＮｏ.10万）のエクセルデータがありその中で、 (1)山田姓は○○人、田中姓は○○人というように1人に至るまで姓の数をすべて数える方法を教えて下さい。もちろん数えた数の合計は10万になるはずです。但しセルには「山田太郎」、「山田次郎」等があり、同じ1つの山田姓として数えます。 (2)住所を区単位で数える方法を教えて下さい。但しセルには「東京都千代田区丸の内○○～」、「東京都中央区銀座○○～」のように入力されており、千代田区の個数や、中央区の個数を数える方法を教えて下さい。 名前はＡ行、住所はＢ行に入力されております。 データが膨大なので、オートフィルタで集めた後、1から数えることもできず困っております。
- 締切済み
- Windows系OS
お礼
bunjii 様 この度はお力添え頂きまして本当にありがとうございます。 とても分かりやすくまたご丁寧に教えて下さいまして、 助かりました。 先ほどトライしたところこちらでできました。 また、全角・半角などのところは関数内のスペースを半角にするなどすればできるため 非常に簡潔に済むかと大変ありがたく思っています。 取り急ぎで失礼かと思いますが、心より御礼申し上げます。