TEXT関数を使って文字列に変換して利用する例 より深い文字列の関数 の使い方:Excel関数

TEXT関数を使って文字列に変換して利用する例

日付のシリアル値から曜日を取り出す例。時刻のシリアル値から分数を取り出す例。

ここの例では文字列の間に曜日や時刻を挿入して表示する例を書いています。

表示形式　aaaa　[m]　を引数内に使っているのがポイントです。

数値を書式設定した文字列に変換します より深い文字列の関数 の使い方:Excel関数

数値を書式設定した文字列に変換します　　　

　テキスト

=TEXT(値,表示形式)

数値は書式設定された文字列に変換され、その計算結果は数値として計算に利用できなくなります。

TEXT関数では表示形式を引数に使います。表示形式(ユーザー定義)の設定方法や日付の表示形式を参考にしてください。

なお、書式→セルの表示形式で表示を変更するとセルの表示が変わるだけで、文字列には変換されません。計算に利用できます。

office2021 文字列中の指定された文字数の文字を別の文字に置き換えます 例3

【例3】B列に入力された文字種を判別する例です。

C2セルに　=IF(AND(CODE(B4)>=48,CODE(B4)<=57),"数字です",

C3セルに　=IF(AND(CODE(B4)>=65,CODE(B4)<=90),"大文字の英字です",

C4セルに　=IF(AND(CODE(B4)>=97,CODE(B4)<=122),"小文字の英字です")))

(参考)【ASCIIコード表】の一部

SPはスペースです。

office2021 文字列中の指定された文字数の文字を別の文字に置き換えます 例2

【例2】アルファベットA～ZをCHAR関数を使って入力することができます。セル内改行の例です。

大文字の「A」の10進数コードは65、「Z」は90と順番に並んでいます。

B,D列に連続数値を入力して、　=CHAR(B4) とします。

文字列の先頭文字に対応する ASCIIまたはJISコードを返します　　

　コード

=CODE(文字列）

CHARと逆の関係になります。

ASCIIコード表の10進数を参照してください。CODE関数では10進数のコード番号が返されているのが分かります。

office2021 文字列中の指定された文字数の文字を別の文字に置き換えます セル内改行の例

【例1】セル内改行の例です。

セル内改行のAlt+EnterはCHAR(10)になりますので、C2セルに　=B2&CHAR(10)&B3　と入力します。

セルの表示形式で、「配置」の「折り返して全体を表示する」にチェックを入れます。

【参考】

セル内改行のAlt+EnterはCHAR(10)になります。

(参考：VBAの定数)

セル内改行（LF・ラインフィード) =CHAR(10) vbLf

office2021 文字列中の指定された文字数の文字を別の文字に置き換えます

文字列中の指定された文字数の文字を別の文字に置き換えます　　　

　リプレース

=REPLACE(文字列,開始位置,文字数,置換文字列)

置き換える文字の位置が決まっている場合に使用できます。

【問題】B3:B5セルにはよけいな数字が紛れ込んでいます。REPLACE関数を使って取り除きなさい。

【解答例】


=REPLACE(B3,1,1,) と「""」は省略することもできます。

指定した数値をASCIIまたはJISコードの番号と見なし、それに対応する文字を返します　　topへ

　キャラクター

=CHAR(数値）

VBAでは使うことがありますが、通常の数式で使うことはほとんど無いと思います。

CODEと逆の関係になります。

【Office シリーズ】より深い文字列の関数 の使い方:Excel関数 問題3

【問題3】

B2:B4に文字列が入力されています。

それぞれの文字列中に「b」が何文字含まれているか数えてC列に表示しなさい。

【解答例】

対象文字を空白に置き換えて(取り除いて)、文字列の長さを比較すれば、対象文字の数を求めることができます。

また、検索文字列の大文字と小文字は区別されます。

 LEN(SUBSTITUTE(B2,"b",))　と「""」を省略することもできます。

【Office シリーズ】より深い文字列の関数 の使い方:Excel関数 問題2

【問題2】
B2：B5に文字列が入力されています。

C列に表示されているように一部またはすべての大文字の「ABC」を小文字の「abc」に置き換えなさい。



【解答例】

SUBSTITUTE関数で置換対象を指定します。指定しないとすべてが置き換えられます。

【Office シリーズ】より深い文字列の関数 の使い方:Excel関数 問題1

【問題1】

西鹿児島駅はH16年九州新幹線の部分開業に伴い、鹿児島中央駅に改称されました。

下の表の西鹿児島駅を鹿児島中央駅に変更しなさい。

【解答例】

SUBSTITUTE関数を使って、文字列を置換します。



「西鹿児島」を「鹿児島中央」に置き換えても同じ結果が得られます。

(注)　同一セルで文字列を置き換えたい場合には、【編集】→【置換】を使います。

文字列を結合 【Office シリーズ】より深い文字列の関数 の使い方:Excel関数

文字列を結合します　　　

　コンカット

=CONCAT(セル範囲）

　Excel2019、Office365またはExcel Onlineで使用できる関数です

文字列が入力されているセル範囲を指定することができるようになりました。

セル範囲 B2:D2に文字列が入力されている場合、=CONCAT(B2:D2)　と数式を作成することができます。

複数行に場合は、下図のように数式を =CONCAT(B7:C9)　と作成することができます。

文字列中から検索文字列を探し、置換文字列に置き換えます　　

　サブスティチュート

=SUBSTITUTE(文字列，検索文字列，置換文字列，置換対象）

　　文字列中の特定の位置にある文字をほかの文字に置き換えるときは、REPLACE 関数を使用します。

　　置換対象：置換対象を指定した場合、文字列中の置換対象番目の検索文字列だけが置き換えられます。

　　省略した場合は、文字列中のすべての検索文字列が置き換えの対象となります。

文字列を結合します 【Office シリーズ】より深い文字列の関数 の使い方:Excel関数

文字列を結合します　　　

　アンド

　＆　(演算子です）

　コンカティネイト

=CONCATENATE(文字列1，文字列2・・・，文字列30）

【問題】

B2～C2セルに入力されている文字を結合しなさい。

F2セルには　「＆」　F3セルにはCONCATENATE関数を使いなさい。

【解答例】

文字列を比較します【Office シリーズ】より深い文字列の関数 の使い方:Excel関数

文字列を比較します　　　

　イグザクト

=EXACT(文字列1,文字列2)

　　　2つの文字列を比較して、まったく同じである場合は TRUE を、そうでない場合は FALSE を返します。

　　　英字の大文字と小文字は区別されます。

B列とC列の文字列が同一かどうかを調べます。

D2セルに　=EXACT(B2,C2)　と入力して、下方向へコピーします。

同じ文字列であれば「TRUE」、異なっていれば「FALSE」を返します。

フォントによっては見た目ではわかりにくいのですが、上記例の入力値には以下の違いがあります。

B3は半角文字、C3は全角文字です。

B4、C5とも全角大文字です。

B5は半角の小文字、C5は半角の大文字です。

文字列の比較を「=」で行うと・・・

EXACTでは英字の大文字と小文字は区別されますが、「=」(イコール)では区別されない例です。



(参考) IF関数と組み合わせて結果を日本語化した例です。

【Office シリーズ】より深い文字列の関数 の使い方:Excel関数

【問題】B2セルの文字列から余分な空白を取り除き、C2セルに表示しなさい。

【解答例】

TRIM関数を使います。

C2セルに　=TRIM(B2)　と入力します。


なお、文字間の空白も取り除きたい場合は、SUBSTITUTE関数を使います。

C2セルに　=SUBSTITUTE(B2,"　","")　と入力します。

空白が半角スペースと全角スペースとが混在している場合には、

=SUBSTITUTE(SUBSTITUTE(B2,"　","")," ","")

=SUBSTITUTE(SUBSTITUTE(B2,"全角スペース",""),"半角スペース","")

と、まず全角スペースを除き、更に半角スペースを除くように数式を組み立てます。

文字列から、印刷できない文字を削除【Office シリーズ】より深い文字列の関数 の使い方:Excel関数

文字列から、印刷できない文字を削除します　　　

　クリーン

=CLEAN(文字列）

　　　他のアプリケーションからデータを取り込んだ時など、

　　　データ ファイルの先頭や末尾に含まれている印刷できない制御コードを削除することができます。

B1セルには「鹿児島県」と「揖宿郡」が同一セル内で改行されています。

="鹿児島県"&CHAR(10)&"揖宿郡"　とします。

または、

「鹿児島県」 キーボードの【Alt】+【Enter】 「揖宿郡」と入力します。

(セルの表示形式の配置タブで「折り返して全体を表示する」にチェックを入れます。）

=CREAN(B1) で改行コードを取り除くことができます。


文字列から余分なスペースを取り除きます　　

　トリム

=TRIM(文字列)

　　　先頭の空白はすべて削除されます。文字間の空白は1個残します。
 

文字を置き換える関数(SUBSTITUTE関数、REPLACE関数)の使い方:Excel関数 問題7

【問題7】下表にように、REPLACE関数を使ってセル内に簡易グラフを作成しなさい。　　

【問題7の解答例】

REPT("|",100)　で"|"縦棒を100本並べて、この縦棒の後ろを空白に置換してグラフのようにします。

数式は　=REPLACE(REPT("|",100),C3+1,100-C3," ")　としました。

縦棒100本は5,6行目の数式のように実際に入力してもOKです。

数式は　=REPLACE("||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||",

C5+1,100-C6," ")

となります。

しかし、実用的には下図のように　=REPT("|",C3)　の方がスマートですね。

文字列中の開始位置から指定したバイト数の文字を置換文字列に置き換えます　　　

　リプレース

=REPLACEB(文字列,開始位置,バイト数,置換文字列)

半角文字(1バイト)の置換の例はわかりやすいのですが、全角文字(2バイト)が対象になるとちょっとわかりにくくなります。

C2:C4セルの数式は　すべて　=REPLACEB(B2,3,3,"X")　としています。

C2セルは　B2セルの　ABCDEF　が　ABXF　に変わっているので、3文字目から3バイトの文字が　X に変わっています。

C3セルは　あいCDEF　→　あXDEF　に変わっているので、3バイト目から3バイト分の文字(5バイト目まで)が　X に変わっています。

C4セルは　あいうえおか　→　あX えおか　に変わっているので、3バイト目から3バイト分の文字(5バイト目まで)が　X に変わっています。

ただし、 X の後ろに半角のスペースが入っています。

C5セルは　あいうえおか　→　あXえおか　に変わっているので、3バイト目から4バイト分の文字(6バイト目まで)が　X に変わっています。