Invalid type for funcation call 'Substitute'

[Zoe Meyer 2]

I have the following Service Address

8 TOWERBY STREET TOWERBY GAUTENG SOUTH AFRICA

I need to split this into two columns

Column 1 : 8 TOWERBY STREET

Column 2 : GAUTENG SOUTH AFRICA

The District: TOWERBY is the word i use to split the sentence. But as you can see the word appears twice in the Address.

I created a calculation which looks at the second character and splits it, that works well in Excel but when trying it out in Spotfire i get the error above.

This is my complete code I am using.

MID([service Address],FIND([service Address],SUBSTITUTE([service Address],[District],[service Address],((LEN([service Address])-LEN(SUBSTITUTE([service Address],[District],""))))/LEN([District])))+LEN([District]),LEN([service Address])-FIND([service Address],SUBSTITUTE([service Address],[District],[service Address],((LEN([service Address])-LEN(SUBSTITUTE([service Address],[District],""))))/LEN([District])))+1)

The Error i get is from this point the first line.

FIND([service Address],SUBSTITUTE([service Address],[District],[service Address],((LEN([service Address])-LEN(SUBSTITUTE([service Address],[District],""))))/LEN([District])))

 

I dont know what else to use in Spotfire.