表示されている文字列を取得するエクセル関数

質問

例えば、
A1セル「平成28年1月5日」（シリアル値で入力）
A2セル「H28.1.5」（シリアル値で入力）
A3セル「12345」（数値）

と入っている場合に、

関数を使って、
B1セル「平成28年1月5日」（文字列）
B2セル「H28.1.5」（文字列）
B3セル「12345」（文字列）

と表示させることはできるでしょうか？

形式を選択して貼り付け、とか、VBAでできるのはわかるのですが、
関数だけで（しかも、できれば汎用的な形の関数で）
実現できるのかが知りたいです。

回答No.2

chie65536（@chie65535） · ベストアンサー

質問者さんはNo.1の回答のような

=TEXT(A1,"ggge""年""ｍ""月""ｄ""日""")
=TEXT(A2,"ge.m.d")
=TEXT(A3,"@")

みたいな答えは望んでいないと思います。

「元のセルの表示形式が変更されたら、自動的に、関数が返す値も変えたい」のだと思います。

ですが、こういう事は「簡単には出来ない」です。

一応

Function GetDisplayString(ByRef target As Range) As String
GetDisplayString = target.Text
End Function

という自作関数を作れば、セルに

=GetDisplayString(A1)

と書いて自作関数を呼び出せば「表示されたままの文字列」が返って来ます。

が、この関数には問題があって「元のセルの表示形式を変更しても、再計算されない」のです。

そこで、ちょっと細工をします。

自作関数を

Function GetDisplayString(ByRef target As Range,Byval dummy As Variant) As String
GetDisplayString = target.Text
End Function

に変更し、呼び出しているセルの式を

=GetDisplayString(A1,RAND())

に変えます。

RAND()関数は「毎回、常に再評価される」ので「シート内のどこかのセルに何らかの変更が加えられた瞬間に値が反映される」ようになります。

但し「シート内のどこかのセルに何らかの変更が加えられた」ですから「A1の表示書式を変えただけ」では再評価されません。「A1の表示書式を変えたあと、どこかのセルに何らかの変更が加えられた瞬間」に再評価されます。

お礼

ありがとうございます。

全部、
同一の計算式で処理したい、と思ったのですが、
やっぱり、VBAを使わないと無理ですよね・・。

回答No.4

Chiquilin

名前の定義で GET.CELL関数を使えばできますけど マクロ付きファイル
としてしか保存できなくなるので使う意味がありません。

名前の定義で 名前を「表示形式」として
参照範囲に「=GET.CELL(1^NOW()+6,A1)」と設定
B1に「=TEXT(A1,表示形式)」と入れる。
以上

素直に ユーザー定義関数を作るか CSVなどで保存するかした方がいいん
じゃないですかね。

お礼

ありがとうございます。

やはり、何らかの形でVBAに頼らないとダメそうですね。

回答No.3

chie65536（@chie65535）

なお

Sub MyTimer1()
Application.OnTime Now + TimeValue("00:00:01"), "MyTimer1", TimeValue("00:00:01")
Calculate
End Sub

というマクロを作成し、このマクロを呼び出すと「１秒に一回、エクセルのブック全体を、強制的に再計算し続ける」ので「値を変更せずとも、表示形式を変更しただけで、最低でも１秒後に関数の結果が更新」されます。

が、このマクロは「パソコン（EXCEL）に大きな負荷をかける」ので、乱用は避けて下さい。場合によっては「砂時計がでたままハングアップ」します。

例えば「ブック全体の再計算に１秒以上かかる場合」には「常に再計算処理をし続ける」事になるので、砂時計のまま何も出来なくなってしまいます。

お礼

ありがとうございます

回答No.1

fmxBeem

いずれもtext関数で可能です。

B1セル→=text(A1,"平成e年m月d日")
B2セル→=text(A2,"ge.m.d")
B3セル→=text(A3,"@")

お礼

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

できれば、
全部、同一の計算式で処理できると助かるのです。