excel関数 単語単位での抜き出し方法について

英文で3000文字前後の文章が一つのセルに入っているものがあり
読みづらいのと他の資料へのコピペの為に
指定した長さで改行させたいと考えています。

自力で考え付いたのはMID・ROW関数で
指定した文字数だけを表示させ、
改行の代わりにセルをかえるというものです。
MID(参照セル,(ROW(A1))*文字数指定セル+1,文字数指定セル)

しかしこれはあくまでも文字数で表示させているだけなので単語の途中でセルが変わってしまい読みづらくて仕方ありません…

文字数はだいたい指定しつつ、単語単位(
またスペース単位)で区切る方法はありますでしょうか？
これを解決するような方法は何かございませんでしょうか？
よろしくお願いします。

例文 Where are you from? I from Japan.

現状)
Where are y
ou from? I fro
m Japan.

希望)
Where are
you from? I
from Japan.

No.7 ベストアンサー 回答者： weboner 回答日時： 2014/12/11 20:45

>改行一切なしの3000文字がひとつのセルに入っているんです

どんな状態なのか全く理解できませんが

A1セルに問題の英文が有るとして

A2セルに
=FIND(" ",$A$1,60)

A3セルに
=FIND(" ",$A$1,A2+61)
以下下方向にコピー

B2セルに
=MID($A$1,1,A2)

B3セルに
=MID($A$1,A2+1,A3-A2)
以下下方向にコピー

60文字以降の最初のスペースを区切りにして分割しています
ただ文面を理解して分割しているわけではないので、おかしな表示になる可能性は高いですよ

この回答へのお礼

ご回答ありがとうございました！

新しい取引先の注文書が何故か一つのセルに大量に文字が入っていて困ってたんです。
入力するシステムが60文字前後×3行ずつしか貼り付けが出来なくて上に
貼り付けたデータの改行は消えるという変なシステムなのでわたしが思いついた方法だと
単語の途中でセルが変わる→システムに入力すると単語の間にスペースがはいるというものでほとほと困ってたので助かりました。

お礼日時：2014/12/12 11:29

No.10 回答者： tom04 回答日時： 2014/12/11 22:49

こんばんは！

横からお邪魔します。

他の方の回答、それに対する補足を読ませていただいて・・・

＞文字数はだいたい指定しつつ、
の「だいたい」という文言があるばかりに難しくなっていると思われます。
単純に「3単語」ずつという感じであれば簡単なのですが、
とりあえずお望み通りになるかどうかは別として、VBAでやってみました。

Sheet1のA1セルにある文章をSheet2のA列に表示するようにしてみました。
尚、文字数として15文字（スペースを含む）を超えたらその単語までは1セル内に表示するようにしています。
おそらく、1セル内に3～5単語表示されると思います。（単語の文字数によって変わってきます）

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long, buf As String, myAry
myAry = Split(StrConv(Worksheets("Sheet1").Range("A1"), vbNarrow), " ")
For i = 0 To UBound(myAry)
For k = i To UBound(myAry)
buf = buf & " " & myAry(k)
If Len(buf) > 15 Then Exit For
Next k
cnt = cnt + 1
Worksheets("Sheet2").Cells(cnt, "A") = Trim(buf)
buf = ""
i = k
Next i
End Sub 'この行まで

※　1セル内の文字数を減らしたい場合は
＞If Len(buf) > 15 Then Exit For
の「15」を減らしてみてください。m(_ _)m

No.9 回答者： Chiquilin 回答日時： 2014/12/11 21:34

また「出来ない」という返事があったとしても 私はここまでとします。

=MID(A1,FIND("!!!",SUBSTITUTE(A1," ","!!!",50))+1,FIND("!!!",SUBSTITUTE(A1," ","!!!",100)))

No.8 回答者： Chiquilin 回答日時： 2014/12/11 21:16

どうも回答と 返事が噛み合っていないのですが……

> 割付は255文字までなので10個以上に分割しなければならず
ですから分割すればできるはずです。何が問題なのですか？

SUBSTITUTEで指定して割れば なんてことのない作業のはずです。
ましてや マクロで処理したって良い話です。

> 文字の割り付けでは希望の操作は出来ない
とあなたは最初に書きました。私が返答をしたのは「出来ない」訳がないから
であって「この方法では あなたが大変だから」じゃありません。

というか質問タイトルに「excel関数」と書いてあるから それだけで処理する
よりははるかにましな方法だろうと思って回答をしたまでです。ハイフネーショ
ンを Excelでやる意味なんて そもそもないと思っています。

「いい方法はないか」と回答をした人に訊くのはどうかと思いますが 素直に回
答するなら「Excelでやるのを止めたらどうですか」になります。

この回答へのお礼

お時間を割いて回答頂いている方にこのような事を申し上げるのはいささか苦しいですし
そもそもの質問内容にて私の言葉足らずの部分もございますので仕方ないのかもしれませんが

何に顔真っ赤にしてるんですか？

対象を貼り付けてそれを関数で改行された状態で表示する
という簡単な動作がしたかっただけでして

対象を貼り付ける
関数で対象を分割し、コピーして他に貼り付け
文字の割付操作

と明らかに手順が増えているようにみえますがそれでもはるかにマシなのでしょうか？
もしそれが遥かにマシなのであれば
他の方達がご提示くださった方法はどうなんでしょう？
よっぽど手順が少ないように感じられるのは私が素人だからでしょうか？

素人がボタン一つでできる割付をわざわざ選ばず
無い知恵を絞って使う必要のない関数を使用したいと質問で書いていて
なおかつ追記で割付は使わないと申告しているのに何故ムキになって割付を押し付けてくるのでしょうか？

タイトルに関数とあって関数以外の回答を押し付けてくるのはただの嫌がらせでしかないのですが…

ソフトへの貼り付けの事もあり改行が必要だから質問したので
そもそもExcelで改行の必要がないと
いう持論をお持ちなのであれば回答なさらなかったら宜しいのでは無いでしょうか？

私はあなたのExcel教室の塾生ではありませんし、あなたのやり方を無条件には受け入れません。
また、あなたも私専任のExcel講師ではないので
私の言っている意味が分からなかったり回答したくないと思えばここまでくる間に回答をお辞めになる自由もあったかと思います。

自分が正しい！自分のやり方をやれ！
と押し付けられても
正直「なにこのひとアスペが何かなのかな？」と思ってしまいます。

お礼日時：2014/12/12 11:51

No.6 回答者： weboner 回答日時： 2014/12/11 13:34

通常考えると何もしなくてもいいように思えるのですが？

>英文で3000文字前後の文章が一つのセルに入っているものがあり

この英文自体は改行のない、一行3000文字の文章なのでしょうか？


>読みづらいのと他の資料へのコピペの為に
>指定した長さで改行させたい


添付した画像を見てもらえばお分かりかと思いますが
（英文のサイトから適当にコピペした1649文字の文章です、内容は判りません）

基本的にコピー元の体裁を維持して、ちゃんと英文の規則処理されての表示になっています

このままこのセルをコピーして、メモ帳等に貼り付けた場合でも
貼り付け先のメモ帳で折り返し設定をすれば英文の禁則処理された状態で折り返し表示になります

通常の英文であれば、Excel上で何か処理しなければいけないことはないはずなんですが

この回答への補足

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

改行一切なしの3000文字がひとつのセルに入っているんです…

補足日時：2014/12/11 17:33

No.5 回答者： Chiquilin 回答日時： 2014/12/11 10:01

> 文字割付では上限文字数の制限があり3000文字では出来ないのです。

……予め いくつかに分割してから処理すればいいだけでは？

SUBSTITUTEで 何番目かのスペースを違う文字列に変えてやれば切り
出すのは難しくないでしょう。

全部自動でという話なら 数式で手間を掛けるのも馬鹿馬鹿しい話ですし
マクロでやった方がいいと思います。スペース区切りで配列にしてから
処理すれば大した手間でもないはずです。

この回答への補足

何度もご回答ありがとうございます

予め分割するにも
割付は255文字までなので10個以上に分割しなければならず
何かいい方法がないかお伺いした次第です

補足日時：2014/12/11 17:34

No.4 回答者： MackyNo1 回答日時： 2014/12/11 00:04

＞コピペ先がエクセルではない為 割り付けで見やすい状態にしても反映されないため

見やすい状態にしても反映されないとはどのようなことを指しているのでしょうか？
貼り付け先がエクセルでなくても、関数で分離したときとまったく同じ方法で、コピー貼り付けすれば（アプリケーションによっては形式を選択して貼り付けで「テキスト」貼り付けする）うまくいくはずですが・・・

ご希望の操作は、関数でも対応できますが、補助列を使用したり煩雑な関数を駆使する必要がありますので、
わざわざこのような面倒な方法を使って文字列を分離する意味がないように思います。

この回答への補足

回答ありがとうございます。
文字割付では上限文字数の制限があり3000文字では出来ないのです。

補足日時：2014/12/11 09:42

No.3 回答者： Chiquilin 回答日時： 2014/12/10 22:48

> 文字の割り付けでは希望の操作は出来ないんです。

何がどうなればいいのですか？　少なくとも最初の質問文を読む限り 文字の
割り付けでダメな理由が分かりません。

この回答への補足

回答ありがとうございます。
文字割付では上限文字数の制限があり3000文字では出来ないのです。

補足日時：2014/12/11 09:42

No.2 回答者： MackyNo1 回答日時： 2014/12/10 19:08

ご希望のレイアウトにするには「文字の割り付け」機能を利用します。

たとえば、A列に文字が入っているなら、A列の列幅を文字を配置したい希望の幅に広げて、そのセルを選択しホームタブの「編集」「フィル」（左真ん中のアイコン）、「文字の割り付け」（バージョンによっては「両端揃え」）をクリックしてください。

列幅を変更したい場合や、単語を追加した場合など、レイアウトの修正が必要な場合は、もう一度データ範囲を選択して上記の操作を行ってください。

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際にはバージョンを明記するようにしましょう。

この回答への補足

わざわざSSの貼り付けまでしてくださりありがとうございました!!
しかしながら申し訳ないのですが文字の割り付けでは希望の操作は出来ないんです。
(コピペ先がエクセルではない為
割り付けで見やすい状態にしても反映されないため)
ですので抽出する方法で単語が分断されない方法があればと考えたのですが…
もしご存知であればお伺いできれば幸いです。

補足日時：2014/12/10 22:26

No.1 回答者： Chiquilin 回答日時： 2014/12/10 18:55

OSとか Excelのバージョンは必須情報です。

A1セルを選択した状態で [ホーム]→[編集]の[フィル]→[文字の割り付け]
→[はい]

この回答への補足

回答ありがとうございました。エクセルは2010です。
申し訳ないのですが文字の割り付けでは希望の操作は出来ないんです。
(コピペ先がエクセルではない為
割り付けで見やすい状態にしても反映されないため)
ですので抽出する方法で単語が分断されない方法があればと考えたのですが…
もしご存知であればお伺いできれば幸いです。

補足日時：2014/12/10 22:24