カンマ区切りのテキストフィールドから数式フィールドを作る。

[Answersで回答する時に使う情報のまとめに戻る](https://qiita.com/Keiji_otsubo/items/9e6e0e946826133eddc7)

こんなのは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

これを別々の数式フィールドに表示する。

結果はできたよ。面倒だけど...

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/328136/c50945d3-e2ed-cb73-c1f6-05809118bb43.png)


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](https://trailblazers.salesforce.com/answers?id=9064S000000Ds6VQAS)

# 更に、もうひとつ追加の悪乗り

数式の大きさから考えるとこれが限界かもしれませんね。4600超えた。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/328136/f6260ea8-574b-2d2f-00b5-08945a107946.png)


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](https://trailblazers.salesforce.com/answers?id=9064S000000DsM9QAK)


# 文字列を抜き出す数式2
[Formula to extract string between characters](https://trailhead.salesforce.com/en/trailblazer-community/feed/0D54S00000JyQpISAV)

本当解決したのに完全に無視ですね。

さて、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/328136/ec3fec4e-7009-c078-4a23-8ec32c8198e5.png)

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)
```


# スペースで区切られた文字列の最初の２つの単語を切り出す。　ただし、スペースなし（単語１つ）、スペース１つ（単語２つ）の場合もある

+ [Help with Flow Opportunity naming formula](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007akHBz)

```
IF(NOT(CONTAINS({!$Record.Account.Name}," ")), {!$Record.Account.Name} ,

IF(NOT(CONTAINS(RIGHT( {!$Record.Account.Name} ,LEN({!$Record.Account.Name}) - FIND(" ", {!$Record.Account.Name} )) ," ")), {!$Record.Account.Name} ,
LEFT({!$Record.Account.Name}, FIND(" ",{!$Record.Account.Name})) & " " & LEFT(TRIM(SUBSTITUTE({!$Record.Account.Name}, LEFT({!$Record.Account.Name}, FIND(" ",{!$Record.Account.Name})) ,"")), FIND(" ",TRIM(SUBSTITUTE({!$Record.Account.Name}, LEFT({!$Record.Account.Name}, FIND(" ",{!$Record.Account.Name})) ,"")))) 
)) 
& " | " & 
TEXT({!$Record.CloseDate})& " | " & {!Get_Products.Product2.Name}

```

#  ;  区切りも同じ

+ [I am trying to create a formula that will return the number after the " ; " in the following example strings:](https://trailhead.salesforce.com/ja/trailblazer-community/feed/0D54V00007eql6x)

```
RIGHT( field__c ,LEN(field__c) - FIND(';', field__c ))
```