「 ABC defg 」から「ABC defg」を抽出するには？ ********************************************************************** ********************************************************************** ********************************************************************** CHIQUILIN 2005/03/05（土）15:54:24 いつもお世話になっております。名前が変わってからは初質問に なります。 実際に困っているというよりは 興味本位の質問です。 土日の暇つぶしになればいいなと……仕事中なんですけどね。 A 1 「 ABC defg 」 2 「 hhh 」 3 「ij kl 」 4 「 qrs」 これを A 1 「ABC defg」 2 「hhh」 3 「ij kl」 4 「qrs」 ※実際の文字列に括弧はありません。 こうしたいんです。 間に空白がない場合は TRIM関数でいいんですが 間の空白が 一字 に詰められてしまいます。もちろん VBA でならできるでしょうが 何とか関数でできないかと考えています。 スペースも文字も全て 半角であると仮定して下さい。半角カタカナは入りません。 自分でも考えてみたんですが ■B1セル =REPLACE(A1,1,LOOKUP(256,COLUMN(1:1)/(LEFT(" "&A1,COLUMN(1:1)) =REPT(" ",COLUMN(1:1))))-1,"") ■C1セル =LEFT(B1,LEN(B1)-LOOKUP(256,COLUMN(1:1)/(RIGHT(B1&" ",COLUMN(1:1)) =REPT(" ",COLUMN(1:1))))+1) 私のカチカチ頭では こんなやり方しか 想像付きません。何とか もう少し短くまとめられないでしょうか。 質問しておいてなんですが 多分 今日はもう返答できないと思い ます。明日には必ず返答致しますので ヒントだけでも ご教示願 います。 ********************************************************************** たっきー 2005/03/05（土）16:34:49 >もちろん VBA でならできるでしょうが VBAでは、Trim で一発ですね。（Ltrim と Rtirm の機能しかないので） =REPLACE(LEFT(A1,MATCH(2,IF(CODE(MID(A1,COLUMN(1:1),1))=32,"",))),1, MATCH(0,IF(CODE(MID(A1,COLUMN(1:1),1))=32,"",),)-1,) Ctrl+Shift+Enter では、だめでしょうか？ ********************************************************************** たっきー 2005/03/05（土）16:45:59 > >もちろん VBA でならできるでしょうが > VBAでは、Trim で一発ですね。 旧 HN TNKさんはご存知と思いますが Function myTrim(a As Range) As String myTrim = Trim(a) End Function ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** たっきー 2005/03/05（土）17:08:33 Ｙ・Ｔ さん 後ろにスペースが残こる場合がありませんか？ ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** Ｙ・Ｔ 2005/03/05（土）17:19:25 CHIQUILINさん、申し訳ないです。<(_ _)> なんか何度もレスをしてしまって汚くなったので、消したら よけい汚くなりました・・・。(-_-;) たっきーさんも、気が付いてくれたのですね。 わざわざレスありがとうございます。 さっきより、式が短くなったみたいですので、レスします。 そして、このレスでは、二度とレス消しません(*^_^*) =MID(A1,FIND(" "&LEFT(TRIM(A1))," "&A1),FIND(RIGHT(TRIM(A1))&REPT(" ", 255),A1&REPT(" ",255))-FIND(" "&LEFT(TRIM(A1))," "&A1)+1) ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** HAL 2005/03/05（土）18:21:03 上記削除しました。m(._.)mペコ (^_^)/ ハーイ A1セルに文字列入力されてるものとして ｛文字列間の空白が最大どれくらいか分かりませんが （14文字以下として）｝ =SUBSTITUTE(TRIM(A1)," ",REPT(" ",LARGE(FREQUENCY(IF(MID(REPT(" ",15)& A1&REPT(" ",15),ROW($A$1:$A$100),1)=" ",ROW($A$1:$A$100),""),IF(MID(RE PT(" ",15)&A1&REPT(" ",15),ROW($A$1:$A$100),1)<>" ",ROW($A$1:$A$100)," ")),3))) CTRL+SHIFTを押しながらENTERで確定。 ※文字間の空白が14文字以上ある場合はレスください。 また半角の空白を対象としています。 ********************************************************************** nezame 2005/03/05（土）18:44:55 短くなりませんね。 =SUBSTITUTE(TRIM(A1)," ",REPT(" ",LOOKUP(256,FIND(SUBSTITUTE(TRIM(A1), " ",REPT(" ",COLUMN(1:1))),A1),COLUMN(1:1)))) ********************************************************************** 　 　 　 　 このメッセージは投稿者によって削除されました。 ********************************************************************** kym 2005/03/05（土）20:09:12 某チャレンジ問題よりやりがいありますね(^^) そして皆様のスキルの高さ、脱帽します。 自分はこの程度なので僭越ですが、、、 =IF(ISERROR(FIND(" ",TRIM(A1),1)),TRIM(A1),LEFT(TRIM(A1),FIND(" ",TRIM (A1),1)-1)&REPT(" ",SEARCH(MID(TRIM(A1),FIND(" ",TRIM(A1),1)+1,31),A1) -SEARCH(MID(TRIM(A1),FIND(" ",TRIM(A1),1)-1,2),A1)-1)&MID(TRIM(A1),FIN D(" ",TRIM(A1),1)+1,31)) 間違ってなきゃいいけど(^^ゞ ********************************************************************** xxx 2005/03/05（土）21:34:55 > 某チャレンジ問題よりやりがいありますね(^^) > そして皆様のスキルの高さ、脱帽します。 > 自分はこの程度なので僭越ですが、、、 > > =IF(ISERROR(FIND(" ",TRIM(A1),1)),TRIM(A1),LEFT(TRIM(A1),FIND(" ",TR IM(A1),1)-1)&REPT(" ",SEARCH(MID(TRIM(A1),FIND(" ",TRIM(A1),1)+1,31),A 1)-SEARCH(MID(TRIM(A1),FIND(" ",TRIM(A1),1)-1,2),A1)-1)&MID(TRIM(A1),F IND(" ",TRIM(A1),1)+1,31)) > > 間違ってなきゃいいけど(^^ゞ > > > > ********************************************************************** Touchan 2005/03/05（土）23:09:45 ♪こんばんは いや〜短くできません。(ーー;) =REPLACE(LEFT(A1,MAX(IF(LEN(A1)>=COLUMN(1:1),(MID(A1,COLUMN(1:1),1)<>" ")*COLUMN(1:1)))),1,MIN(IF(MID(A1,COLUMN(1:1),1)<>" ",COLUMN(1:1)))-1 ,) ********************************************************************** Chromate 2005/03/05（土）23:43:00 御機嫌如何? クロメートです お遊びで ツール - オプション - 反復計算 100回 B1セルに =IF(C1=1,A1,IF(LEFT(B1)=" ",REPLACE(B1,1,1,), IF(RIGHT(B1)=" ",REPLACE(B1,LEN(B1),1,),B1))) C1セルに =IF(C1=100,1,C1+1) ********************************************************************** CHIQUILIN 2005/03/06（日）08:17:55 おはようございます。皆さん本当にありがとうございます。 まだ あまりちゃんとは見てないんですが とっても面白い 方法ばかりで楽しいです。すみません。実のところ昨日か ら まだ寝てません。頭が回らない〜。 たっきーさんへ： > > VBAでは、Trim で一発ですね。 > 旧 HN TNKさんはご存知と思いますが ええ 知りませんでしたとも(笑) HALさん や nezameさんが やってる SUBSTITUTE を使う方 法を最初考えてたんですが 私は途中で諦めました。 さす がです。 他の方々の式は 今の状態では 理解できそうにもありませ んので 一睡してから 返答させて頂きます。 まずは御礼まで。zzz ********************************************************************** 辛口 2005/03/06（日）14:32:47 CHIQUILINさん、こんにちは。 >実際に困っているというよりは 興味本位の質問です。 つまり「問題」を作られたのですね。 =MID(LEFT(A1,MATCH(1,0/(MID(A1,COLUMN(1:1),1)>" "))),FIND(LEFT(TRIM(A1 )),A1),256) Ctrl + Shift + Enter それでは逆に「問題」です。 CHIQUILINさんのデータで各セルの最大スペースは、スペース何個でしょう。 例 _hh___h__h_ ( _ はスペースの意）答え３個 こんなことしていいのかな。 よくなかったら言って下さい。 ********************************************************************** クロ 2005/03/06（日）14:54:19 > こんなことしていいのかな。 　分かりませんが、暇つぶしに。 　スペース以外が"h"なのを利用して、 　 　=MAX(IF(SUBSTITUTE("h"&A1&"h","h"&REPT(" ",COLUMN(1:1))&"h","")<>"h" &A1&"h",COLUMN(1:1))) 　Ctrl + Shift + Enter ********************************************************************** クロ 2005/03/06（日）15:02:51 早とちりしたようですね。 > CHIQUILINさんのデータで 　を見落としていました。 >　スペース以外が"h"なのを利用して、 　が成り立ちませんので、上の答えはボツですね。 ********************************************************************** クロ 2005/03/06（日）15:07:55 これでよかったのですね。 =MAX(IF(SUBSTITUTE(A1,REPT(" ",COLUMN(1:1)),"")<>A1,COLUMN(1:1))) 　Ctrl + Shift + Enter ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** CHIQUILIN 2005/03/06（日）15:14:39 やっと今 目覚めました。復活。 目覚めたら一日の大半が 終わってました。 > それでは逆に「問題」です。 うへー 質問し返されたー。 というか辛口さんの式すごすぎ。 > CHIQUILINさんのデータで各セルの最大スペースは、スペ > ース何個でしょう。 =LEN(A1)-MIN(LEN(SUBSTITUTE(A1,REPT(" ",COLUMN(1:1)),"",1))) Ctrl + Shift + Enter で確定 これでどうでしょう。起き抜けだからポカがあるかもしれ ませんが。 > こんなことしていいのかな。 楽しいからいいんじゃないでしょうか。平日だと切羽つま った人が多いので嫌味かもしれませんが土日ですし。 ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** クロ 2005/03/06（日）15:26:31 CHIQUILINさんの問題に戻って 　=MID(LEFT(A1,LEN(A1)-MAX(IF(COUNTIF(A1,"*"&REPT(" ",COLUMN(1:1))),CO LUMN(1:1)))),MAX(IF(COUNTIF(A1,REPT(" ",COLUMN(1:1))&"*"),COLUMN(1:1)) )+1,256) 　Ctrl + Shift + Enter ********************************************************************** HAL 2005/03/06（日）15:29:47 たびたび削除申し訳ありません。m(._.)mペコ > それでは逆に「問題」です。 の回答例として =MAX(FREQUENCY(IF(MID(A1&"V",ROW($A$1:$A$100),1)=" ",ROW($A$1:$A$100)) ,IF(MID(A1&"V",ROW($A$1:$A$100),1)<>" ",ROW($A$1:$A$100)))) CTRL+SHIFTを押しながらENTERで確定。 ********************************************************************** Touchan 2005/03/06（日）15:54:27 ♪こんにちは 辛口さんの式の意味がや〜っと理解できました。（*^_^*） では、問題法ですが、これは、nezameさんの式をまねび（学び）まして、 =MAX(FREQUENCY(IF(MID(A1,COLUMN(1:1),1)=" ",COLUMN(1:1)),IF(MID(A1,COL UMN(1:1),1)>" ",COLUMN(1:1)))) これで、どうかなあ。 というか、もうHALさんにさき越されてましたぁ〜(-_-;) ********************************************************************** 辛口 2005/03/06（日）17:19:24 CHIQUILINさんにも見ていただけたので。 問題を提示しておきながら自分也の解を出さないで終わっては失礼になり過ぎ なので。 =COUNT(FIND(REPT(" ",COLUMN(1:1)-1),A1))-1 Ctrl + Shift + Enter Ctrl+Shift+Enterの代わりにINDEXを入れて、 =COUNT(INDEX(FIND(REPT(" ",COLUMN(1:1)-1),A2),))-1 同じくLOOKUPを使って、 =LOOKUP(256,FIND(REPT(" ",COLUMN(1:1)-1),A1),COLUMN(1:1))-1 などなど。 自分なりには、 たまにはこう言うのも、色々な考え方を見られて勉強になりました。 CHIQUILINさんはじめ、みなさんありがとうございました。 勝手な失礼、すみませんでした。 ********************************************************************** このメッセージは投稿者によって削除されました。 ********************************************************************** Touchan 2005/03/06（日）21:27:39 ♪こんばんは いやー、さすがに辛口さんの解はすごい。 これは =COUNT(FIND(REPT(" ",COLUMN(1:1)-1),A1))-1 これでも、同じことなのでは・・・ =COUNT(FIND(REPT(" ",COLUMN(1:1)),A1)) " "と" "と" "・・・とそれぞれFINDできれば、その個数が " "の個数と同じという意味ですよね。 でも、きっといけない理由があるのですね・・・ ********************************************************************** CHIQUILIN 2005/03/06（日）23:35:19 皆様 ご協力ありがとうございます。土日の頭の体操には 充分過ぎるほどなりました。 こうしてみると SUBSTITUTE を使う有用性ってあまりない のかなぁと思えてきます。配列数式で SUBSTITUTE を使う ものもたまに見かけますが 他の方法と比べると どうして も長くなってしまいますね。 あと Chromate さんのレスにある反復計算は 実は 一番最 初に作ってました。 確かに遊び感覚ですけど 面白い機能 ですね。実際に使うのには向いてないと思いますが。 > これでも、同じことなのでは・・・ > =COUNT(FIND(REPT(" ",COLUMN(1:1)),A1)) 私もそう思います。配列を「0:255」にして 空白がない場 合に対応させようとしたのかな と思いますが……？ でもやはり エラーが無視できる関数は強いなぁ…… 明日の朝に閉じさせて頂きます。 ********************************************************************** CHIQUILIN 2005/03/07（月）09:58:53 永久保存版にさせて頂きます。ありがとうございました。 追記： この手の質問 たまにでいいんで誰か考えてくれません？ 質問って 「答えが分かればいい」と「今後につなげたい」の２ 種類に大別できると思います。定期的に こういうのを考えると 勉強になっていいんですけど。 実際 今回の質問だけでも私にとっては大収穫でした。本当に皆 様に感謝致します。ただ最初の自分の数式が ちょっと恥ずかし いです……。 ********************************************************************** ********************************************************************** ********************************************************************** Ｙ・Ｔ 2005/03/07（月）10:15:03 前の数式とあまりかわらなかったので、掲載しようか迷ったのですが、永久保 存版ということであれば、↓も仲間に入れてやってください(*^_^*) http://www2.moug.net/app/bbs/message.php?cat=exopr&id=20050305-000013 =MID(LEFT(A1,ABS(LEN(A1)-MATCH(1,0/(TRIM(RIGHT(A1&0,COLUMN(1:1)))&" a1 /1")*1)+1)),MATCH(1,0/(LEFT(0&A1,COLUMN(1:1))&" a1/1")*1),255) ********************************************************************** Ｙ・Ｔ 2005/03/07（月）10:15:53 閉じ閉じ・・・(^_^;) ********************************************************************** ********************************************************************** ********************************************************************** 以上「Excel一般機能 Q&A掲示板」での質問内容を編集させて頂きました。