…E関数を使う方法と、TEXTSPLIT関数を使った方法を紹介します。 SUBSTITUTE関数をネスト（入れ子）する方法 SUBSTITUTE関数とREDUCE/LAMBDA関数を使う方法 TEXTSPLIT関数を使う方法 SUBSTITUTE関数をネスト（入れ子）する方法 単純ですが削除対象となる文字列が多いと手間がかかる方法です。 次の画像ではB3:B5の範囲に削除対象となる3種の文字列が入力されています。 E3セルにSUBSTITUTE関数を使った式を入力し、D3セルの…

…TBEFORE関数やTEXTSPLIT関数が使えるバージョンならFINDを使って位置を求める場面がなさそうな気もしますが、参考まで。「右から数えて何文字目か」を取得する方法FIND関数とLEN/SUBSTITUTE関数を併用する方法 ここでもC列にFIND関数を使った式を入力し、B列の文字列中の「-」の位置を取得しています。 ただし取得しているのは「-」のうち最も右にあるものが、右から何文字目の位置にあるかです。 C3セル（下方にコピー） =LEN(B3)-FIND("★",…

ExcelのTEXTSPLIT関数を使って、テキストを縦方向に分割する方法についてです。 区切り文字（列）をTEXTSPLIT関数の第3引数として指定すれば、テキストは縦に分割されます。 TEXTSPLIT関数とTOCOL関数を併用する方法もあります。 手順 区切り文字を第3引数として指定する TOCOL関数と併用する 手順区切り文字を第3引数として指定する 画像では、B3セルに入力されているテキスト中の「☆」を区切り文字として、縦方向のセルに分割しています。 D3セル =T…

…ることも可能です。 TEXTSPLIT関数とINDEX関数を組み合わせて、カンマ区切り文字列のn番目の項目を求められます。 INDEX関数の代わりにCHOOSECOLS関数を使えば、後ろからn番目を求めることもできます。 手順 TEXTSPLIT/INDEX関数を使う方法 TEXTSPLIT/CHOOSECOLS関数を使う方法 後ろからn番目を求める 連続するカンマや先頭・末尾のカンマを無視して数える場合 手順TEXTSPLIT/INDEX関数を使う方法 B3:B6の範囲に4…

…された末に導入されたTEXTSPLIT関数の基本的な使い方についてです。 注意点や若干の応用についても併せて紹介します。 TEXTSPLIT関数を使って、文字列をその文字列中の区切りの文字（カンマ、コロンなど。2文字以上可）に基づき分割することができます。 機能と構文 基本的な使用例 第4引数（空白を無視）の効果と使用例 第5引数（大・小文字の区別）の効果と使用例 第6引数（パディング）の効果と使用例 注意点 複数のテキストに対するスピルの結果が不自然 応用例 セル内改行で区…

…ることができます。 TEXTSPLIT関数等を駆使すれば複数抽出も可能です。 1つだけ抽出する場合 TEXTBEFORE/TEXTAFTER関数を使う方法 注意点 TEXTSPLIT/INDEX関数を使う方法 複数抽出する場合 TEXTSPLIT/TEXTBEFORE関数を使う方法 SCAN/LAMBDA/TEXTSPLIT関数等を使う方法 1つだけ抽出する場合TEXTBEFORE/TEXTAFTER関数を使う方法 文字列中の指定した文字（列）より前の部分を取得するTEXTB…

…す。 ExcelにもTEXTSPLIT関数が導入されましたが、機能には若干の違いがあります。 SPLIT関数を使うことで、文字列を（その文字列中に含まれる）区切り文字により区切り、複数のセルに分割することができます。 機能と構文 基本的な使用例 第3引数（各文字での分割）の効果と使用例 第4引数（空のテキストを削除）の効果と使用例 応用例 縦に分割する セル内改行を区切り文字として分割する 分割したn番目の値だけを取り出す 機能と構文 SPLIT関数の機能は「区切り文字により…

ExcelのTEXTSPLIT関数は強力ですが、複数のセル（配列）を対象とした場合に自然にスピルした結果が得られません。その現象と対策についてです。 TEXTSPLIT関数の第1引数として複数のセルを与えた場合、それぞれのセルの文字列を区切り文字で分割した最初の項目しか返ってきません。 対策としては文字列を一旦TEXTJOIN関数で連結する方法があります。簡潔ですが合計文字数が長すぎるとエラーになります。 REDUCE/LAMBDA関数を使う方法もありますが若干式が複雑です。…

…2022年秋）であるTEXTSPLIT関数で、複数種類の区切り文字（文字列）を指定する方法です。 TEXTSPLIT関数の引数で区切り文字を「{"☆","〇"}」のように指定すれば、それら（☆と〇）のすべてを区切り文字（文字列）として区切った結果が得られます。手順 画像では、B列に分割する対象の文字列が入力されています。 D3セルに次のような式を入力し下方にコピーすることで、「☆」と「〇」の両方を区切り文字として文字列を分割した結果を得ています。 D3セル =TEXTSPLI…

…方法についてです。 TEXTSPLIT関数等を使用してIPアドレスを2進数表記に変換することができます。手順 B列に10進数表記のIPアドレスが入力されています。 C3セルに式を入力し、下方にコピーすることで2進数表記に変換しています。 C3セル =TEXTJOIN(".",,DEC2BIN(TEXTSPLIT(B3,"."),8)) TEXTSPLIT関数で4つのオクテットに分割し、DEC2BIN関数でそれぞれを8桁の2進数に変換し、最後にTEXTJOIN関数で再度ドットを…

…字列を区切りますが、TEXTSPLIT関数を使おうとすると（第1引数に配列を与えたときに）最初のオクテットしか返ってきません。 そこでTEXTBEFORE/TEXTAFTERで代用しています。「"."&B3:B14&"."」により文字列の前後に「.」を加えておき、さらに「SEQUENCE(1,4)」をnと定義することで、「n（1～4）番目の『.』の後にある文字列のうち最初の『.』より前の部分」つまり各オクテットをそれぞれ取得しています。また、ソートの際には各オクテットを数値と…

…表示させています。 TEXTSPLIT関数が使えれば店名と月名の分割がさらに簡単になりそうですが、TEXTSPLITの第1引数を配列にすると、つまり「TEXTSPLIT(x,"_")」とすると店名しか返ってこないのでTEXTBEFORE/TEXTAFTERで代用しています。 参考ですが、「B3:B5&"_"&C2:F2」だけを実行するとこのようになります（C8セル）。 縦横の見出しを連結した文字列からなる3行4列の行列が生成されているのがわかります。 数値の並びも同じく3行4…

…ル =LET( x,TEXTSPLIT(TEXTJOIN("_",,MID(B3,SEQUENCE(LEN(B3)),1)),"_",CHAR(10),TRUE,,""), y,WRAPROWS(TOCOL(EXPAND(x,,CEILING(COLUMNS(x),9),"")),9), FILTER(y,BYROW(y,LAMBDA(z,CONCAT(z)))<>"") ) かなり長いです。LET関数を使い配列をx→y→FILTER(y,～)と段階的に加工して出力しています…

…法を試してみます。 TEXTSPLIT関数を使って、文字列中に指定した文字列がいくつ含まれているか数えることができます。複数種の文字列を数えることもできます。手順 ここでは、それぞれの行でB列の文字列にC列の文字列がいくつ含まれているかをカウントするものとします。 そこでD3セルに次のような式を入力して下方にコピーしています。 D3セル =COLUMNS(TEXTSPLIT(B3,C3))-1 TEXTSPLIT関数を使い、C3セルの文字列を区切り文字とみなしてB3セルの文字…

…celの新関数であるTEXTSPLIT関数を使うことで、文字列を区切り文字に基づいて分割することができます。 これにより分割した項目のうち最後の項目を取得する方法についてです。 「CHOOSECOLS(TEXTSPLIT(文字列,区切り文字),-1)」という式により、分割した文字列のうち最後のものだけを取得することができます。 手順 TEXTAFTER関数との比較 手順 B列にカンマで区切られたいくつかの文字列が入力されています。 これに対しC3セルに次のような式を入力し、B…

…OL関数の結果に対しTEXTSPLIT関数が直接使えれば話は早いのですが、TEXTSPLIT関数の第1引数に配列を与えた場合に最初の項目の列（この例では県名の列）しか返ってこないという仕様のためうまくいきません。 なお、「B3:B5&"_"&TRANSPOSE(C3:C6)」の部分だけ実行すると次のようになります。 これをTOCOL関数で縦1列に並べてさらに「_」の前後に分割し、2列に表示している、というわけです。REDUCE/LAMBDA関数等を使う方法 REDUCE/LA…

…更で対応できます。 TEXTSPLIT関数を使って苗字と名前を分割することができます。 「区切り位置」メニューやフラッシュフィルを使って分割することもできます。 手順 TEXTSPLIT関数を使う方法 「区切り位置」メニューを使う方法 フラッシュフィルを使う方法 手順TEXTSPLIT関数を使う方法 B3:B6の範囲に氏名が入力されています。苗字と名前が1つの全角スペースで区切られているところがポイントです。 これに対しC3セルに次のような式を入力し、さらに下方にコピーするこ…

…名を取得できます。 TEXTSPLIT/CHOOSECOLS関数を併用する方法も考えられます。 手順 TEXTAFTER/TEXTBEFORE関数を併用する方法 TEXTSPLIT/CHOOSECOLS関数を併用する方法 備考 手順TEXTAFTER/TEXTBEFORE関数を併用する方法 画像は「テストファイル.xlsx」という名前のExcelファイルです（最上部の緑色の部分で確認できます）。 B3セルに式を入力して「テストファイル.xlsx」という文字列を取得しています。…

…ろに利用可能となったTEXTSPLIT関数を使用しています。 TEXTSPLIT関数を使って改行（CHAR(10)）を区切り文字として文字列を分割し、TEXTJOIN関数を使って再度結合することにより、セル中の改行だけの行を削除することができます。 改行を表示に反映させる（「折り返して全体を表示する」にチェックを入れておく）設定が必要です。手順※各セルの文字列は上寄せで表示するよう設定されています。B3セルの文字列がそうでないように見えるのは先頭に改行があるからです。 対象と…

…方法です。 新関数のTEXTSPLIT関数を使う方法と、FILTERXMLを使う少し変わった方法を紹介します。 TEXTSPLIT/TEXTJOIN関数等を使ってIPアドレスのゼロ埋めと余分なゼロの削除をすることができます。 FILTERXML/TEXTJOIN関数等を使う方法もあります。 手順 ゼロ埋めをする場合 TEXTSPLIT/TEXTJOIN関数を使う方法 FILTERXML関数を使う方法 余分なゼロを削除する場合 TEXTSPLIT/TEXTJOIN関数を使う方法…

…※その後ExcelにTEXTSPLIT関数が導入されました。本記事の内容は旧バージョンでは使える機会があるかもしれませんが、基本的に不要となりました。 TEXTSPLIT関数については次の記事で紹介しています。www.officeisyours.com FILTERXML関数などを使って、区切り文字による文字列の分割を行うことができます。 ただし「手順 画像ではB3セルに対象となる文字列が入力されています。 B3セルの文字列を「☆」で区切った結果を得るため、C3セルに次のよう…