こんなのはApex トリガーで split関数を使えばすぐできる。
と回答したんだけど、Apexは使わないんだって。多いんですよね Salesforce界隈では...
私からすれば簡単にできるのに、わざわざ複雑な式でやりたがる人たちの方が不思議。
(また数式項目を避けたい理由に、検索できないこともあります)
基本形
123_456789 を 123 と 456789に切り分ける
LEFT(X20220428te__c,FIND('_', X20220428te__c )-1)
RIGHT( X20220428te__c ,LEN(X20220428te__c) - FIND('_', X20220428te__c ))
回答編
まぁ、一回真面目に式で書いてみました。
こんなテキストフィールドがあるとのこと
Street Name, City, State, ZIP
これを別々の数式フィールドに表示する。
結果はできたよ。面倒だけど...
St Name
LEFT(address__c,FIND(',', address__c )-1)
City
LEFT(MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )),FIND(',',MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )))-1)
State
LEFT(MID( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) , FIND(',', MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) ) + 1, LEN( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) )),FIND(',', MID( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) , FIND(',', MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) ) + 1, LEN( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) )) )-1)
Zip
MID( MID( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) , FIND(',', MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) ) + 1, LEN( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) )) , FIND(',', MID( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) , FIND(',', MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) ) + 1, LEN( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) )) ) + 1, LEN( MID( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) , FIND(',', MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) ) + 1, LEN( MID( address__c , FIND(',', address__c ) + 1, LEN( address__c )) )) ))
Formula field to get specific text from a longer text
更に、もうひとつ追加の悪乗り
数式の大きさから考えるとこれが限界かもしれませんね。4600超えた。
Street Name 1
LEFT(full_address__c,FIND(',',full_address__c)-1)
Street Name 2
LEFT(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)),FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))-1)
City
LEFT(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))),FIND(',',MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))))-1)
State
LEFT(MID(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))), FIND(',',MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))) + 1, LEN(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))))),FIND(',',MID(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))), FIND(',',MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))) + 1, LEN(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))))))-1)
ZIP
MID(MID(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))), FIND(',',MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))) + 1, LEN(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))))), FIND(',',MID(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))), FIND(',',MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))) + 1, LEN(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))))) + 1, LEN(MID(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)))), FIND(',',MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))) + 1, LEN(MID(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c)), FIND(',',MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))) + 1, LEN(MID(full_address__c, FIND(',',full_address__c) + 1, LEN(full_address__c))))))))
Get separate texts from 1 comma separated text
文字列を抜き出す数式2
Formula to extract string between characters
本当解決したのに完全に無視ですね。
さて、
Id
RIGHT(MID( I_20220913__c, FIND('Id: ', I_20220913__c ) ,22 ),18)
Origin
TRIM(MID( RIGHT(I_20220913__c, LEN(I_20220913__c) - FIND('Origin: ', I_20220913__c ) +1 ) ,9 ,FIND(' ',RIGHT(I_20220913__c, LEN(I_20220913__c) - FIND('Origin: ', I_20220913__c ) )) ) )
Name
RIGHT(RIGHT(I_20220913__c, LEN(I_20220913__c) - FIND('Name: ', I_20220913__c ) +1 ) ,LEN(RIGHT(I_20220913__c, LEN(I_20220913__c) - FIND('Name: ', I_20220913__c ) +1 )) - 6)