```
Sub addFormulaArray()
Cells.Clear
[A1] = "MECH~CDA-CUP-PF~1 - CUP0915.2XL - Copper Reducer (P)"
Range("B1:K1").FormulaArray = "=IFERROR(MID($A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1))),FIND(""~"",$A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1))))-FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1)))),"""")"
' or see 2nd line
Range("B2:D2").FormulaArray = "=LEFT(A1, FIND(""-"",A1&""-"")-1)"
Range("B2").Replace What:="A1", Replacement:="IFERROR(MID($A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1))),FIND(""~"",$A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1))))-FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1)))),"""")", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="A1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
```