◇大量データをExcelで至急旨く処理したく教えて下さい◇

1.大量データは元々は　テキスト.txt　形式でしたが、コピペで　Excel2010　へ貼り付けています
2.大量とは　テキスト.txt　約5000枚　で　１テキスト.txt　に　処理対象項目が　約２００前後です
3.項目とは　例えば　Spearmint 1.37%　7+8+9+9 x 9+9+9+9+8　といった
　文字列です
4.項目中で必要項目は先頭文字Spearmintと文字1.37を数字1.37へ変換した値のみです　
5.最終的に　約200前後ある項目を　『指定された規則』に従って　分類して数値を合算する事です
＜　現在迄に行った事　＞
6.先ず　必要文字列のみを　抽出すべく　=LEFT(A1,FIND("%",A1)-1)　を　B１～処理対象項目
　の最後までドラッグしております
7.此れにより　Spearmint 1.37　等の文字列を全項目抽出しております
8.次に　C　に　=IF(OR(ISNUMBER(FIND({＜割愛＞","Sunstar","Black Toney","Fair Play","Aja　　x","Swynford","St. Simon","Spearmint"",＜割愛＞"Charless O'Malley"},B1))),B1,"")　を
　C1～処理対象項目の最後までドラッグしております
　補足-1）：余りにも長いので　一部＜割愛＞しております
9.上記8.では　5.で記しました『指定された規則』に従って全項目から　必要な項目のみの集まり
　を１系統仕分けしております（アップロード画像の　グリーン色が該当しております）
　補足-2）：此処での　系統とは　『指定された規則』に従って全項目より　２系統と其他　または
　３系統と其他　に仕分けする　との意味です
　アップロード画像は２系統の例です
10.8.と同じ　形式で　もう１系統を　E　にて行っております
　（アップロード画像の　オレンジ色が該当しております）
　補足-3）：２系統共に含まれる項目はアップロード画像の　パープル色が該当しております
11.　D　と　F　に　=SUBSTITUTE(D1,LEFT(E1,FIND(" ",D1)),)*1　を　D1～　と　F1～
　処理対象項目の最後までドラッグしております
＜　御教示お願い項目　＞
12.上記で見様見真似で用いた式では；
Q-1：Spearmintのような連続したスペルが項目名称で無く　例えばBlack Toneyとスペースが
　　　入っている名称ではエラーしてしますので　このエラーを回避する　修正した上記式　または
　　　全く別の目的に合致した式を教えて下さい。
　　　補足-４）項目には例えば　Bill and Coo の様にスペースが２箇所ある項目名称とか　
　　　　　　　St. Simon　の様に　.　が入っている項目名称とか　
　　　　　　　Charless O'Malley の様に　' 　が入っている項目名称とかが有ります
　　　　　　　此れ以外の特徴あるエラーを起こしている名称は今の処無いです
Q-2：上記の式では必要無いセルも式を適用しているために　＃VALUE!　が生じてしまいます
　　　必要なセルのみに式を入れていては時間的にも作業的にも簡単にいかないので、
　　　修正した上記式　または　全く別の目的に合致した式にて　＃VALUE!　となるセルには
　　　数字の　0　を挿入する形の式を教えて下さい。
補足-5）Excelほとんど使った事が無いです。　
宜しくお願い申し上げます。

質問者からの補足コメント

• 本お願いは2016年4月14日から続く熊本地震で大きな被害を受け続けている
  遠い知人達への細やかなお手伝いをできそうな事案をボランタリーとして
  今も現地に赴いている知人と交信する中でピックアップして取り組みを開始
  したもので御座います。
  大量データテキスト約５０００枚は大容量転送サービスにて取り寄せました。
  重ねて、御教示をお願い申し上げます。

    補足日時：2016/05/31 18:02

• ◇補足説明-1◇
  1.本文説明での　例えば　Black Toney　を　スペースを外して　BlackToney　とすることは
  　原則できません（名称としてスペースが入ったモノ（状態）が正規名称だからです）
  　同様に本文にてエラーを起こしている名称から何かを除く事も原則できません
  ★但し一旦Excelの機能で自動で除いて　例　文字1.37を数字1.37　へ変換合算できれば目的は
  　元の名称がExcelでは見やすいので可能になるような気がしますが、Excelの機能で自動で除く
  　機能（関数？）が有るのか無いのか分かっておりません★
  2.本文にて　スペースが２つの名称　Bill and Coo　を記載しましたが、別のコピペしたExcelにて
  　スペースが３つの名称　Maid of the Mist　が出て来ました。頻度から手動対応で、と思います
  宜しくお願い致します

    補足日時：2016/05/31 19:03

• 

  ◇◇反省◇◇
  1.元を正せば　TEL　で　交信しており　スペアミント　とか　テディー　とか　ブルドッグ
  　などの聞き慣れた　言葉　の　英単語+何かを意味する率（パーセント表示　％）と思い込み
  　加えて　データ量はテキスト５００枚前後　と　１桁聞き間違えたのか　言い間違いだったの　　　　か・・・・
  2.然し、引き受けた事実は変わらず何が何でも　集計計算してお返事をする責務を全うするのが
  　当然で反省してます
  3.Excelにデータを移行して・・・Accessは一層分からないので・・・できそうでできないです
  　NETで検索を繰り返していますが　VBA　とか　マクロ　の解説がヒットしてしまい　HINT
  　の式だけでも欲しいです
  繰り返しですが、宜しくお願い申し上げます

    補足日時：2016/05/31 22:57

• 

  ◆◆◆◆至急　助言等をお願いします◆◆◆◆■■データ量が多くワーク的にピンチです■■　
  No.1回答者：fujillin様へ　またはfujillin様の回答文に記載された　VBA　が分かる　皆様へ
  
  ◆ご提案頂きました　VBA　に関しまして（この表現が適切で無い可能性が有りますが便宜上
  VBAと呼称させて頂きます。標準モジュールで定義されたFunctionプロシージャと呼ぶのかも
  ）。Excelもほとんど使っていない者で・・・。VBA　のモジュール名はextractionとしており
  ます。またデータが有るExcelシート名はex01 です。
  アップロード画像の通りVBAでエラーFalseが生じており結果　fujillin様の回答文での
  B1　と　C1　に入れている式が機能しておりません。＃NAME?　となります。
  どのように修正（デバッグ？）したら良いのかが分かりませんのでご教示下さい。

  
    補足日時：2016/06/02 15:21

No.1 回答者： fujillin 回答日時： 2016/06/01 17:59

こんにちは

先にお断りしておきますが、説明文が長く不要な情報がたくさんあるのと、添付の画像もほとんど読めないので、内容を理解せず、雰囲気だけで回答しています。

なさりたいことは文字列データ（例：Spearmint 1.37%　7+8+9+9 x 9+9+9+9+8）から、「Spearmint」と「1.37」を取り出したいということだと解釈しました。
数字部分には必ず「%」が最後についていて、（スペース、一連の数値、%）でセットになっていると仮定しました。
「Spearmint」に当たる部分はこの数値の前にある文字列全部という意味と考えています。

ご提示のように関数で文字列を分解して解析してゆく方法でも可能だと思いますが、私は関数をよく知らないので、「ユーザー定義関数」を利用する方法で一発で出せるのではと試してみました。

A列の各行に対象となるテキストがあるものとして・・・
1）VisualBasicの標準モジュールに下記の
　ユーザー定義関数を記述しておく。（コピペで可）
2）B1セルに =extraction(A1,1)　を入力
3）C1セルに =extraction(A1,2)　を入力
4）B1、C1セルを選択して、必要なだけ下方にフィルコピー
これで、必要な情報が抽出できないでしょうか？

◇ユーザー定義関数
Function extraction(str As String, f As Integer)

Dim reg, regMatch
Set reg = CreateObject("VBScript.RegExp")
extraction = ""

reg.Pattern = "^(.*?)\s(\d?\.?\d+)%"
Set regMatch = reg.Execute(str)

If regMatch.Count = 0 Then Exit Function
If f = 1 Then
　extraction = regMatch(0).SubMatches(0)
ElseIf f = 2 And regMatch(0).SubMatches.Count > 1 Then
　extraction = regMatch(0).SubMatches(1)
End If

End Function


※ ユーザー定義関数に関しては、簡単な説明が以下のサイトにあります。
　（検索すればたくさん見つかると思います）
http://www.kokodane.com/tec3_3.htm

※ 私が、なさりたいことを勘違いしていた場合は、まったくの見当はずれですので、無視してください。

この回答へのお礼

No.1
回答者： fujillin 様

回答誠に有難うございます

1.＞説明文が長く不要な情報がたくさんあるのと、添付の画像もほとんど読めない

　A　:　申し訳け御座いません、お詫び致します

2.＞なさりたいことは文字列データ（例：Spearmint 1.37%　7+8+9+9 x 9+9+9+9+8）から

　「Spearmint」と「1.37」を取り出したいということだと解釈しました

　A　:　ご認識の通りです

3.＞数字部分には必ず「%」が最後についていて

　A　:　100％　「%」が最後についております

4.＞（スペース、一連の数値、%）でセットになっていると仮定しました

　A　:　スペースの数は一定では御座いませんが、１つもスペースが無い、と云うケースは御座いません

　　　　最小でも1つのスペースが入っている様です
　　　　
　　補足-1）事例　Swynford or Golden Boss 0.39%　7 x 8+5+5+5+4　

　：　一連の数値　といいますかデータは　名称　複数のスペース　数値（最大約40.00～最小約0.01）

　　　直後に必ず％　其後は（不必要なデータです）色々な形の文字列　の構成です

　　補足-2）名称で一番多い事例　　　：Spearmint　英スペル１語
　　　　　
　　　　　　　　　二番目に多事例-1　：La France　スペース１箇所入語
　　　　　　
　　　　　　　　　　稀名称事例-2　　：Bill and Coo　スペース2箇所入語

　　　　　　　　　　稀名称事例-3　　：Maid of the Mist　スペース3箇所入語

　　　　　　　　　　稀名称事例-4　　：St. Samon　　.　入語

　　　　　　　　　　稀名称事例-5　　：Charless O'Malley　'　入語

　　　　　　　　　　稀名称事例-6　　：James-towns　　-　入語

　　　　　　　　　　稀名称事例-7　　：San-in-Law 　　-　2箇所入語

　　　　　　　　　　稀名称事例-8　　：カリフォルニヤフラワ　カタカナ語

5.＞「ユーザー定義関数」を利用する方法で一発：

　：凄いです、案内URLで導入方法確認中です

　　一先ず、御礼とさせて頂きます

お礼日時：2016/06/01 19:55

No.2 回答者： fujillin 回答日時： 2016/06/03 10:10

ANo1です。

＞画像の通りVBAでエラーFalseが生じており
画像が見えないのでわかりませんが、通常ユーザ定義関数内でエラーが発生した場合は、セルの表示が「#VALUE!」というエラー表示になります。
Falseの表示が出ることに心当たりはありません。
（関数が論理値を返すものである場合はそのような表示がされますが、今回はそうではないので）

＞入れている式が機能しておりません。＃NAME?　となります。
全てのセルで#NAME?表示となるということですよね？

ユーザ定義関数も定義が認識されれば、他の関数とほぼ同様に扱われます。（計算内容が定義した内容で処理されるだけ）
#NAME? がでるのは、エクセル側で関数名が照合できないか、引数が名前付きなどの場合に照合ができないなどの意味だと思います。
上にも書きましたが関数内の計算でエラーが起きた場合は#VALUE!、セルの参照範囲が不適切な場合は#REF!など通常の関数とほぼ同様の表示になります。


さて、今回の#NAME? から考えられるのは、ユーザ定義関数が登録されていない（エクセル側で認識できていない）か、呼び出すときの関数名が正しくないなどがありがちと考えられます。
関数定義ができているかどうかのテストとして、関数を標準モジュールに登録後に、セルに
　=ex
のように入力中の状態で1秒くらい待つと、エクセル側で候補関数のリストを出すと思いますが、そのリストに定義したextractionが表示されているかどうかでチェックできると思います。

表示されていない場合は、関数として認識されていないということで、うまく設定ができていないことになります。
表示されている場合は、そのリスト内から選択して入力を続ければ、関数名違いによる#NAME? エラーは自動的に回避することができるはずです。

上記以外にも、#NAME? エラーになる可能性はあるかもしれませんが、すぐには思いつきません。

この回答へのお礼

No.2
回答者： fujillin様

ご親切に有難う御座います。
現在、ご教示賜った内容、其他WEB上にての情報類、などを用いまして確認中で御座います。

御礼迄。

お礼日時：2016/06/03 11:42

No.3 回答者： fujillin 回答日時： 2016/06/03 12:43

ANo1、2です。

10秒もかからないテスト方法のつもりだったのですが・・・
添付（↓）のように関数として表示されませんか？

No.4 回答者： fujillin 回答日時： 2016/06/03 17:24

ANo3です。

もしかして、環境が違うのかな？
Windows環境ではなくて、正規表現（RegExp）が使えないとかでしょうか？
実は、エクセルVBAそのものには正規表現の機能がないので、VBScriptから呼び出して利用しているのですが、その環境がないのかしらん？
（その場合どのようなエラーになるのかわかりませんが・・・）

この回答へのお礼

ANo3 fujillin 様
有難う御座います、状況説明です
1：リソース　光接続
　PC-1系：OS　Win7-64-pro　Core-i5　Ram12　HDD500程度：4台
　PC-2系：OS　Win10-64-pro　Core-i7　Ram32　SSD500程度：4台
　他でofficeを必要としていたPC=0
2.今般の事案に伴いoffice2010を購入
3.『関数を標準モジュールに登録後にセルに=exのように入力中の状態で1　秒くらい待つと』を実行した結果：Excel画面にてグレーアウトからホワイ トアウト更にブラックアウトし全てのキー操作無効となりました
4.回復するのでは・・・で１時間程待ちましたが変化無く無論Esc、
　Ctrl+Alt+Delete、なども全く受け付けませんのでパワーキー長押し　　　で強制シャットダウンを試みましたが此れも受け付けないのでACアダプ　　タ切断しBATTも取り外しを行ないました
5.当該PCは非常にクリーンな状態を維持しており各種ログ等には何ら
　異常なくBIOS、DRIVER、OS、API　等全て最新状態でした
6.通常であればBATT取り外しにて、CMOSがｔ（タウ）時定数にもより
　ますがディスチャージされBIOS画面には入れる状態に約30分後には
　なるのですが２時間程待ちましたがBIOS画面に入ません
7.このPCはPC-1系の１台で無論、元のテキストデータ約５０００枚
　正確には５３０２枚は、テキスト系 .txt や Excel系.xlsx 　教えて頂き
　初めて接した　VBAを含む系は　.xlsｍ　で外付けHDD、SDD、USB、
　CC等へ多重バックアップ保管していますのでデータは問題有りません
8. officeは１ランセンスしか購入しておりませんでしたのでSHOP
　にてもう１ランセンス購入して参りました
9.非常に時間が経過したので、6.のPC-1系の１台はBIOSには入れる
　のではと思ったのですが全く反応しないままですのでPC-1系のもう
　1台へofficeをセットアップしました、上記PC同様にクリーンです
10.office系に防御策：第１階層　.exe　は2010では38個の様ですので
　サンドボックスを活用しご教示VBAをリトライします

お礼日時：2016/06/03 19:31

No.5 ベストアンサー 回答者： fujillin 回答日時： 2016/06/04 10:56

ANo4です。

なんだか大変なことになっているようですね。

その辺になるとさっぱりわからないので、お役には立てないですが、Windows環境であればVBScriptも使えると思いますので、No4は関係なさそうです。

エクセルの関数表示でハングアップしちゃったみたいですが、その際にはマクロ（VBA）自体は動作していないので、内容が関係しているとも思えません。
エクセル本体のインストールがどこか不完全だったのか、あるいはOSがらみなのか・・・
智識の無い私が考えたところで、何の足しにもならないですね…(^_^;)

この回答へのお礼

No.5
回答者： fujillin様
色々有難う御座いました。
当該PCは　MB(マザーボード）破損　がメーカーでのテストにて確定しましたので　MB交換　修理後に
折り返して頂きます。
御礼迄。
敬具

お礼日時：2016/06/07 16:04