プロが教えるわが家の防犯対策術！

文字列から英数字のみを抽出する関数

解決済

  • 質問者：golf90
  • 質問日時：
  • 回答数：4件

文字列から英数字のみを抽出する関数を教えていただきたいです。

セルの文字列にはひらがな、カタカナ、数字、アルファベット、記号等を含みます。

色々調べてはみたのですが、数字のみを抽出する関数の説明はたくさんありますが、
アルファベットも含むとなると見つかりませんでした。

数字0～9とアルファベット27個の計37個の文字なので、どうにか関数でできると思うのですが。

具体的には下記のようにしたいのです。

●A列
今日iPhone5をauで購入
最新のNEWSを15時～PCで見る　
Moonshotを生み出す「Google X」

●B列にこう表示したいのです。
iPhone5au
NEWS15PC
MoonshotGoogleX


スペースは残ったままでも結構です。

詳しい方、どうかよろしくお願いいたします。

通報する

この質問への回答は締め切られました。

質問の本文を隠す

このQ&Aに関連する最新のQ&A

「今日 のNEWS」に関するQ&A： 二重発券 ⁉ 今日のYahoo newsです。 http://news.yahoo.co.jp/pi

さらに表示

「au iPhone5」に関するQ&A： au iphone５→６ｓ機種変　icloudへ電話帳

「文字列 抽出」に関するQ&A： MATCH関数の記号から文字列抽出

「最新 iPhone」に関するQ&A： 最新のIPHONEとANDROIDはどちらが優れていますか

「iPhone5 購入」に関するQ&A： iPhone5s購入にあたり

「Google au」に関するQ&A： Google Chrome auのサイトが見れない

「アルファベット 記号」に関するQ&A： アルファベットのアクセント記号（?）

「最新 au」に関するQ&A： auの最新のでかいポスター、写っているのは誰？？？

A 回答 (4件)

No.2ベストアンサー

  • 回答者： keithin
  • 回答日時：

＞数字のみを抽出する関数の説明はたくさんありますが



文字列の中に数字が「一塊だけ」ある場合の説明は多分沢山ありますが、（今回のご質問のように）「文字数字文字数字文字数字」と不定数入り混じってる場合の数式は、全く見つからなかったと思いますよ。

たとえば「英数字が最大限３カタマリまで」といった制約を付けて無理矢理関数をくっつける事は出来なくはありませんが、正直不細工ですし、そもそもあんまり現実的じゃありません。



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

public function myf(a) as string
　dim i as long
　for i = 1 to len(a)
　if mid(a,i,1) like "[0-9a-zA-Z ]" then
　　myf = myf & mid(a,i,1)
　end if
　next i
end function

ファイルメニューから終了してエクセルに戻る

任意のセルに元の言葉を記入し、
=myf(A1)
のように計算する。
    • good
    • 9
    • 件
通報する
この回答へのお礼

やはり、関数では厳しいようですね。
教えていただいたマクロであっさりと思うようにできました。
心から感謝いたします。
ありがとうございました。

通報する
お礼日時：2013/03/19 10:17

No.4

  • 回答者： CoalTar
  • 回答日時：

行数分-3文字までなら対応可能です。



C列は空白セル
D1セルに
=IF(ISNUMBER(SEARCH(MID($A1,COLUMN(A1),1),"abcdefghijklmnopqrstuvwxyz0123456789")),C1&MID($A1,COLUMN(A1),1),C1&"")
右へオートフィル (80文字までとしてCE1セルまで)
そのまま 下へオートフィル

B1セルに =CE1
フィルハンドルダブルクリック
    • good
    • 1
    • 件
通報する

No.3

  • 回答者： mike_g
  • 回答日時：

添付図参照


１．セル C1 に次式を入力して、此れを右方に(Ａ列の最多文字数分だけの列数だけ。此処ではＺ列までとした)ズズーッとドラッグ＆ペースト（Fig-2）
　　=IF(COLUMN(A1)>LEN($A1),"",IF(OR(AND(CODE(MID($A1,COLUMN(A1),1))>47,CODE(MID($A1,COLUMN(A1),1))<58),AND(CODE(MID($A1,COLUMN(A1),1))>64,CODE(MID($A1,COLUMN(A1),1))<91),AND(CODE(MID($A1,COLUMN(A1),1))>96,CODE(MID($A1,COLUMN(A1),1))<123)),MID($A1,COLUMN(A1),1),""))
２．セル B1 に次式を入力
　　=C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1
３．範囲 B1:Z1 を下方にズズーッとドラッグ＆ペースト
「文字列から英数字のみを抽出する関数」の回答画像3

この回答への補足

ご回答ありがとうございました。
A列は60文字程あるので、今回はマクロでやることにしました。
考えていただいて本当にありがとうございました。

補足日時：2013/03/19 10:16
通報する
    • good
    • 0
    • 件
通報する

No.1

  • 回答者： JazzCorp
  • 回答日時：

エクセルのセル内の英数字のみを抽出したい


http://detail.chiebukuro.yahoo.co.jp/qa/question …

この回答への補足

試してみましたが、カナが残ったりしてうまくいきませんでした…
ご回答ありがとうございました。

補足日時：2013/03/19 08:03
通報する
    • good
    • 0
    • 件
通報する

このQ&Aに関連する人気のQ&A

「iPhone5 購入」に関するQ&A： iphoneのメールが勝手に消えた。

さらに表示

「今日 のNEWS」に関するQ&A： 男が結婚するメリットって何ですか？

「au iPhone5」に関するQ&A： iphone6への連絡先の移行ができません。

「最新 iPhone」に関するQ&A： iphone スマホ　どっちがいいの？

「文字列 抽出」に関するQ&A： 【Excel】複数の列を参照して、特定の文字列を含む行全体を別シートに抽出する関数

「アルファベット 記号」に関するQ&A： ワードで発音記号を表すにはどうやるのですか？

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう！

Q質問する（無料）

このQ&Aを見た人はこんなQ&Aも見ています

関連するQ&A

関連するカテゴリからQ&Aを探す

専門家回答数ランキング

専門家

※過去一週間分の回答数ランキングです。

Q質問する（無料）

他の専門家の回答をチェック

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q数値とアルファベットに分ける（エクセル）

例えばＡ列に
ＡＢＣＤ１２３４
ＫＪＤ２３４
ＬＯ２３３５
ＦＤＲＴＹ３５２３
上記のように品番が入力されていたとします
これをアルファベットをＢ列に数値をＣ列に
分ける簡単な方法はありませんか？
やはり、関数しかないでしょうか？

Aベストアンサー

こんにちは。KenKen_SP です。

A1 からデータが始まるとして、

B1 の数式：（アルファベット部）
=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},ASC(A1)&1234567890))-1)

C1 の数式：（数字部）
=RIGHT(A1,LEN(A1)-LEN(B1))

とし、B1:C1 を選択して、データ終端までフィルでコピーです。
関数のままが嫌なら、最後にコピー＆形式を選択して貼り付け で値にすれば
良いと思います。

他の回答も見る

Qエクセル　数値だけ抽出するには？

エクセルで、文字列＋スペース＋数字と入力されているセルから数値だけ取り出す方法を教えていただけますか？
文字列とスペースの字数は一定で、数値の桁数は6-8桁と変動します。
A列からb列のように変換したいのです。

Ａ列　　　　　　　　　　Ｂ列
PMID:_12345678　　　　12345678
PMID:_123456　　　　　123456

宜しくお願いいたします。

Aベストアンサー

B1に
VALUE(REPLACE(A1,1,6,""))
でよいでしょう.

数値の桁数には依存しません.

他の回答も見る

QExcel　文字列の中から数字だけを削除したい

Excel　2007で各セルに文字列として入力されている、数字を削除し文字だけにしたい。
数字は先頭に入力されています。（顧客コード）

例：
12345-001 ABC株式会社
98765-　ZYX有限会社ごお商会　等

数字を削除し文字列だけで元のセルにセットしたいです。


きっと簡単なことなんでしょうけど、過去を検索しても良くわかりませんでした。

よろしくお願いいたします。

Aベストアンサー

[No.3お礼]へのコメント、

私が提示した式の意味が分かる(と思う)添付図を参照ください。
B1: =TRIM(ASC(A1))
C1: =FIND(" ",B1)
D1: =MID(B1,C1+1,99)

最後の式中の B1、C1 に上２つの式を代入すれば、提示式になります。

セル A2 の「ZYX有限…」の直前は全角スペースだったので、半角スペース１個にするために、先ずはASC関数で全角スペース１個を半角スペース２個にし、TRIM関数で複数個の半角スペースを１個に置き換えています。ちなみに、文字列の左端や右端にスペースがあればそれらを除去します。
最初のスペースの「次」から文字列を切り出すので「+1」になっています。
Ａ列の文字数は百文字以上は考えなくてよかろうと推測して「99」にしました。

【注意】３行目以降に示したように、全角アルファベットおよび全角カナは、何れも半角文字になってしまいます。

以上の解説で分からなければ、貴方の「レベル」に合った回答に従ってください。

他の回答も見る

QエクセルのＩＦ関数で、文字が入力されていたならば～

エクセルのＩＦ関数で文字が入力されていたならば～、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またＩＦ関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか？

他の回答も見る

Q文字列からアルファベットを抽出

ＥＸＣＥＬ2007を使用致しております。
下記のように不規則な文字列から英字（全角・半角）を抽出する方法をご教示願えましたら幸いです。かなり無理が有るでしょうか…　どうぞ宜しくお願い致します。


かきくけこkakikukeko
さしすせそsashisusesoたちつてと
naninunenoなにぬねの
【はひふへほ】hahihuheho
mamimumemo《まみむめも》
ＹＡＹＵＡＯやゆよ
『らりるれろ』rarirureroわをん1230

Aベストアンサー

またまたお邪魔します。

[ ] やスペースが邪魔のようですので・・・

考え方としてはアルファベットを抽出前に「[ ]」を削除したデータを利用するか、
「[ ]」を抽出後に削除するかのどちらかだと思います。

とりあえず、前者の方法で作業用の列としてC列を使っています。

尚、抽出時に「スペース」は抽出しないでアルファベット・スペース以外の文字が出るまでループを繰り返すようにしていますので、
仮に「Aiueo kakikukeko」のような場合でもスペースがなくなり「AiueoKakikukeko」となってしまいます。

Sub test()
Dim i, k As Long
Dim str, buf As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 3) = WorksheetFunction.Substitute _
(WorksheetFunction.Substitute(Cells(i, 1), "]", ""), "[", "")
For k = 1 To Len(Cells(i, 3))
str = Mid(Cells(i, 3), k, 1)
If str Like "[A-z,Ａ-ｚ]" Then
buf = buf & str
End If
If Len(buf) > 0 And Not str Like "[A-z,Ａ-ｚ, ]" Then Exit For
Next k
Cells(i, 2) = buf
buf = ""
Next i
Columns(3).ClearContents
End Sub

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

またまたお邪魔します。

[ ] やスペースが邪魔のようですので・・・

考え方としてはアルファベットを抽出前に「[ ]」を削除したデータを利用するか、
「[ ]」を抽出後に削除するかのどちらかだと思います。

とりあえず、前者の方法で作業用の列としてC列を使っています。

尚、抽出時に「スペース」は抽出しないでアルファベット・スペース以外の文字が出るまでループを繰り返すようにしていますので、
仮に「Aiueo kakikukeko」のような場合でもスペースがなくなり「AiueoKakikukeko」となってしまいます。

S...続きを読む

他の回答も見る

Q【Excel】文字列か数字か判断する関数ってありますか？

お世話になっております。<(_ _)>
タイトル通りなのですが、セルに入力されているものが文字列だったらTrueかFalse、またはその逆。
というように返してくれる関数ってありますか？

Aベストアンサー

ごめんなさい
ISNUMBER関数は数字か否かです。
ISTEXT関数のようですね

他の回答も見る

Qエクセルで、半角文字列を抽出するには

使用機種はXPでEXCEL2003です。

住所のデータ整理をしています。
地名の後の番地のみ半角数字（ハイフンも半角）で入力されており、その部分だけを別の列に移動させる作業です。

関数で、半角文字列だけを抽出することはできますか？
left関数を使おうとも思いましたが、文字数がバラバラなので、難しいと思いました。

私は、一つひとつコピー→ペーストをするしか思いつかないほどの初心者です。
どうぞよろしくお願いいたします。

Aベストアンサー

当該住所がA1に入っていると仮定して
=RIGHTB(A1,LENB(JIS(A1))-LENB(A1))
でどうでしょう？

住所だけ（番地以外）を抜き出すのは
=LEFT(A1,LEN(A1)-LENB(JIS(A1))+LENB(A1))
です。

他の回答も見る

Q全角・半角混在の文字列から半角文字のみ取り出す

エクセル勉強中です。問題集で理解できないところがあります。
数式がどういう意味をもつのか教えて頂けるとありがたいです。
よろしくお願いします。

画像添付の問題になります。
A列に製品名が入っています。（製品番号：半角文字）（製品名：全角文字）
B列に半角文字の製品番号だけを取り出しなさいというものです。
半角文字の開始位置がバラバラになっているところが問題のポイントになっています。

回答ですが
B2：
=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0),),LEN(A2)*2-LENB(A2))
こちらで半角文字のみ取り出せるようです。回答には数式のみで何故この関数を使うのか？
使うことでどういった結果を導くなどの解説が一切ありません。（ちなみに出版会社の便利技的な問題集です）

MID関数で製品名A2から開始位置を指定して、全角半角をLEN関数LENB関数で半角文字数を
算出して文字列を抽出するという事は理解できます。
ただ、この開始位置の指定の所が理解できません。
数式を分割してみましたが
=MID(A2,COLUMN(2:2),1)の所はどの行も製品名の１文字目ですよね・・・
その値にLENB関数で文字数？
数式の検証で見てみると配列のような結果が次々と現れて・・・
MATCH関数もありますしINDEX関数が何か関係しているような気はしているのですが、
INDEX関数と言えば配列に行番号・列番号と例えば表の該当するセルの位置抽出の
知識しかありません。一つのセルでINDEX関数？

すいません。独学で勉強していてこの程度の知識ですが、この数式の考え方教えてくださる方よろしくお願いします。

エクセル勉強中です。問題集で理解できないところがあります。
数式がどういう意味をもつのか教えて頂けるとありがたいです。
よろしくお願いします。

画像添付の問題になります。
A列に製品名が入っています。（製品番号：半角文字）（製品名：全角文字）
B列に半角文字の製品番号だけを取り出しなさいというものです。
半角文字の開始位置がバラバラになっているところが問題のポイントになっています。

回答ですが
B2：
=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0),),LEN(A2)*2-LENB(A2))
こち...続きを読む

Aベストアンサー

ご質問のような半角文字を抽出するなら、提示された数式は一部非効率でわかりにくいところがありますが、個人的には最も簡単な配列数式になっていると思いますので参考までに検証方法を補足します。

まずB2セルに以下の式を入力してください(半角文字が１１文字目までに出現する場合）。

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(A:K),1)),0),),LEN(A2)*2-LENB(A2))

COLUMN関数の部分はROW関数で以下のようにするほうが数式がわかりよいかもしれません。

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,ROW($1:$11),1)),0),),LEN(A2)*2-LENB(A2))

この画面上部の数式バーの数式のCOLUMN(A:K)（またはROW($1:$11)）の部分をドラッグして選択しF9キーを押すと,「{1,2,3,4,5,6,7,8,9,10,11}」と表示されます（Escで解除）。

次に、MID(A2,COLUMN(A:K),1)の部分を選択し、F9キーを押すと、「{"お","徳","用","M","G","0","5","K","R","カ","ラ"}」とA2セルの先頭から１１文字目までの文字が取得できていることがわかります。

次に、LENB(MID(A2,ROW($1:$11),1))の部分を選択し（INDEXを含む部分はこの配列を範囲に変換しているだけですので、この部分を省略するならCtrl+Shift+Enterで確定する必要があります）、F9キーを押すと、「{2,2,2,1,1,1,1,1,1,2,2}」のように、それぞれの文字の半角、全角が1と2で表示されます。

次にMATCH(1,INDEX(LENB(MID(A2,ROW($1:$11),1)),0),)の部分はMATCH関数(第三引数が０または省略）で１（＝半角文字）が最初に出現する場所を調べています(上記の例なら4番目）。

ご質問のような半角文字を抽出するなら、提示された数式は一部非効率でわかりにくいところがありますが、個人的には最も簡単な配列数式になっていると思いますので参考までに検証方法を補足します。

まずB2セルに以下の式を入力してください(半角文字が１１文字目までに出現する場合）。

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(A:K),1)),0),),LEN(A2)*2-LENB(A2))

COLUMN関数の部分はROW関数で以下のようにするほうが数式がわかりよいかもしれません。

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,ROW($1:$11),1))...続きを読む

他の回答も見る

QExcel2007でカタカナの文字のみを抽出、表示するには？

表題のとおりExcel2007でカタカナの文字のみを抽出し、となりのセルに表示する方法を探しています。

具体的には、

　　　　　　Ａ列　　　　　　　　　　　　　Ｂ列
意識がモウロウとしている。　　モウロウ
カミシモを脱いで談笑する。　　カミシモ

といった感じで表示させたいのですが、可能でしょうか。
また、同様に半角文字のみをとなりのセルに表示させる方法も探しています。
なるべく早くご教授くだされば幸いです。

Aベストアンサー

たとえば、半角文字を抽出するなら以下のような数式がわかりよいかもしれません。

=MID(A1,MIN(INDEX((MID(A1,ROW($A$1:$A$100),1)=MID(JIS(A1),ROW($A$1:$A$100),1))*100+ROW($A$1:$A$100),)),LENB(JIS(A1))-LENB(A1))

他の回答も見る

Qエクセルでアルファベットと日本語の文字列を分割する方法

エクセルで各セルに
"Beatlesビートルズ"
"Rolling Stonesローリング・ストーンズ"
のような英語とカタカナ表記がつながっている（間にスペース等ない）文字列を
"Beatles" "ビートルズ"
"Rolling Stones" "ローリング・ストーンズ"
のように分割する方法はありますでしょうか
データが少なければコピペですみますが
何千件になるととてもやってられません
関数もしくはＶＢＡで可能であれば教えてください
データは同じ列に縦に並んでいます

Aベストアンサー

こんばんは。Wendy02です。

#3 のマクロの訂正です。しかし、次のマクロのほうがスピードは速いはずです。

'----------------------------------
　'出力する列は？
　Const OUTPUTCOL As String = "C"
　'----------------------------------
　
　iCol = Columns(OUTPUTCOL).Column　　'← 入れ替え
　
　Set rng = Range("A1", Range("A65536").End(xlUp))　'←入れ替え
　Application.ScreenUpdating = False

　　↓
　　以下のようになります。

　Set rng = Range("A1", Range("A65536").End(xlUp))
　
　iCol = Columns(OUTPUTCOL).Column - rng.Column
　

'----------------------------------------------------------------
なお、混在した場合のものも作っておきました。
ローリング・ストーンズRolling Stones
Rolling Stonesローリング・ストーンズ
でも、両方とも切り分けるように作りました。
'---------------------------------------------------------------


Sub reSeparateAlphabetKana()
'英語・カタカナ混在の場合に区切るマクロ
Dim rng As Range
Dim c As Range
Dim iCol As Long
　'----------------------------------
　'出力する列は？
　Const OUTPUTCOL As String = "B"
　'---------------------------------
　
　Set rng = Range("A1", Range("A65536").End(xlUp))
　
　iCol = Columns(OUTPUTCOL).Column - rng.Column
　
　Application.ScreenUpdating = False
　
　For Each c In rng
　　 If VarType(c.Value) = vbString Then
　　　 c.Offset(, iCol).Resize(, 2).Value = reSplit(c.Value)
　　 End If
　Next c
　Application.ScreenUpdating = True
End Sub
Function reSplit(strText As String) As String()
'英語・カタカナを分離する関数
Dim Matches As Object
Dim Match As Object
Dim buf(1) As String
With CreateObject("VBScript.RegExp")
　.Pattern = "^([A-z ]+|[ぁ-龠 ]+|[\uFF64-\uFF9F ]+|[Ａ-ｚ ]+)"
　'\uFF64-\uFF9F 半角カタカナ 注意：パターンには半角空白が一つ入る
　.Global = False
　'全角空白は紛れ込ませない
　strText = WorksheetFunction.Substitute(strText, "　", " ")
　Set Matches = .Execute(strText)
　If Matches.Count Then
　　 Set Match = Matches(0)
　　 buf(0) = Match.Value
　　 buf(1) = Replace(strText, Match.Value, "")
　End If
　reSplit = buf()
End With
End Function

こんばんは。Wendy02です。

#3 のマクロの訂正です。しかし、次のマクロのほうがスピードは速いはずです。

'----------------------------------
　'出力する列は？
　Const OUTPUTCOL As String = "C"
　'----------------------------------
　
　iCol = Columns(OUTPUTCOL).Column　　'← 入れ替え
　
　Set rng = Range("A1", Range("A65536").End(xlUp))　'←入れ替え
　Application.ScreenUpdating = False

　　↓
　　以下のようになります。

　Set rng = Range("A1", Range("A65536").E...続きを読む

他の回答も見る
ページトップページトップ

Q質問する（無料）

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報