セル内の文字列の分割はsearch関数やleft関数を使わずにできないのでしょうか？

質問

エクセルのA列に桁数が不規則な任意の数字が並んでいて、それぞれの数字には単位を表す"g"か"g/k"がついています。
B列にA列の数字を移し変えたいのですが、その際に、"g"がついているものはそのまま、"g/k"がついているものには70を掛けた後に"g"をくっつけて表記したいです。そこでB1セルに下記のような関数を使いました。

=IF(RIGHT(A1,1)="g",A1,LEFT(A1,SEARCH("g",A1,1)-1)*70&"g")

のですが、すごくダラダラしてて長いように感じます。もっと簡単に同様の操作をできないのでしょうか？例えば、文字列と文字列をくっつけるのに&を使うだけですんでしまうように、－を使ったら文字が引き離せるとか、そんな便利な機能はないでしょうか？

過去の質問を検索したところ、search関数とleft関数を用いた回答が一件ヒットしただけで他に見当たらなかったので質問しました。よろしくお願いします。

回答No.3

BLUEPIXY · ベストアンサー

=IF(RIGHT(A1,1)="g",A1,SUBSTITUTE(A1,"g/k","")*70&"g")
式の長さはあんまり変わりませんが、
文字列を引くというイメージに近いのはSUBSTITUTE ですかね。

お礼

なるほどー。こんな関数があったんですね。
僕の考えたものよりも使用する関数が一つ少ないだけすっきりしてますね。それに、search関数とleft関数の組み合せよりもBLUEPIXYさんに教えていただいたsubstitute関数の方が、なんていうか、個人的には感覚的に馴染みやすいです。
勉強になりました。ありがとうございます。

回答No.4

Wendy02

こんにちは。

ユーザー定義関数なら、Evaluate じゃないですね。
（以下は、本格的なものではありません。簡易型の関数です)

標準モジュールに貼り付けたら、

Function KEISAN(r As Range) As Variant
Dim buf As Variant
　If r.Value Like "#*g/k" Then
　　buf = CStr(Val(r.Value) * 70) & "g"
　ElseIf r.Value Like "#*g" Then
　　buf = CStr(Val(r.Value)) & "g"
　Else
　　buf = r.Value
　End If
　 KEISAN = buf
End Function

後は、VB Editor を、Alt + Q などで閉じて、

一般の関数と同じように使えます。

=KEISAN(A1)

とすれば、出てきます。

A1 が、12345g/k　なら、864150g

となります。

なお、一般関数も考えてみました。

どのみち、どうやろうとも、この程度だと思います。

=IF(RIGHT(A1,1)="k",REPLACE(A1,LEN(A1)-2,3,"")*70 &"g",A1)

その理由は、
『Excelの使い方のメモリ節約の方法』の原則(MS文書番号 : 401704)

5番目に、

表示形式の活用
『"10 個" というように数値に文字列をつけて表示したいときは、数値を表示している隣のセルに "個" と文字列を入力するのではなく、ユーザー定義の表示形式を作成する。』

というのがあります。つまり、その原則を外れた使い方は、Excelのワークシートでは画期的なものが用意されていないからなのですね。

お礼

やってみたら、うまくいきました。自分で関数を作ってるんですよね。いやーすごい。感動しました。今回はWendy02さんに作ってもらったものを意味もわからず貼り付けたのですが、いずれ、自分で作れるようになりたいものです。
大変勉強になりました。ありがとうございました。

回答No.2

imogasi

＞70を掛けた後に"g"をくっつけて表記したいです
で長くなるのはやむをえないでしょう。
ーー
例データ
Ａ列　　　Ｂ列
1234g 1234g
1g/k 70g/k
8231234g 8231234g
20g/k 1400g/k
Ｂ列Ｂ１の式
=IF(RIGHT(A1)="k",LEFT(A1,SEARCH("g*",A1)-1)*70&"g/k",A1)
で式をした方向告に複写します
結果
常時Ｂ列の通り。
質問とほぼ同じですね。
これより短い回答は関数式で、多分望めないでしょう。

お礼

ありがとうございます。エクセル初心者で、今までエクセルのすごい機能にただ驚いてばかりだったもので、今回も実はすごい裏技があるのでは・・・・とエクセル君に大きな期待を抱いていたのですが・・・
でも、逆に言えば自分のやり方が効率いいことの証明になったわけで、自信につながりました。ありがとうございました。

回答No.1

noname#16474

ユーザー定義関数を作ってはいかがでしょうか
http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_keisan/index.html#susiki-keisan

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_keisan/index.html#susiki-keisan

お礼

ありがとうございました。参考URLは他にもエクセルに関する情報がたくさんで、大変勉強になります。ただ、僕はマクロをほとんど使ったことがないためによくわかりません。
標準モジュールを開いて下記の式（？なんて表現するんでしょうかｗ？）をコピペしました。そこからどうすればいいのでしょうか？
「A列には文字列として数式が入力してあります。」というのは、データとして数値が入力されていれば、あとは僕は何もしなくていいのでしょうか。
「B1セルに定義した関数「keisan」を使って、『=keisan(A1)』と入力すると計算されます。」
多分、ここが一番重要だと思うのですが、よくわかりません。基本的な質問で恐縮なのですが教えていただけないでしょうか？

＞Alt+F11でVBEを開き、標準モジュールに
＞Function KEISAN(R As Range)
＞　　KEISAN = Evaluate(R.Value)
＞End Function
＞と入力します。
＞A列には文字列として数式が入力してあります。
＞B1セルに定義した関数「keisan」を使って、＞『=keisan(A1)』と入力すると計算されます。