Help to modify a SUBSTITUTE(MID(LEFT formula so it works as expected.

The Excel formula below looks at Cell A1 in a worksheet called FolderDataImport.

It searches the data in Cell A1 and locates the “-“ character, of which there is only ever one, and then looks for the “[“ character, again there will only ever be one, then it returns what ever data is between those two characters and is then copied down the column for as long as data exists in the FolderDataImport!A column.

Because there are multiple columns, each of which has different data to be processed, I use a VBA module to enter all of the formulas.

I am trying to modify the formula for use in a different column to do the following:

Look at FolderDataImport!A1, locate the “-“ character and the “[“ character and then check if the “(“ and “)” characters exist in-between them. If the do then return what ever is between the “(“ and “)” but not the “(“ and “)”, only the data between them. If the “(“ and “)” characters are NOT found then do nothing (so the cell appears blank).

So the modified would work thus:

AAAA – BBBB (1234)DDDD [0000]FFFF returns “1234”

But

AAAA – BBBB 1234 DDDD [0000]FFFF returns nothing

Dim SWs As Worksheet, TWs As Worksheet

Dim Lr As Long

Dim Answer

Set SWs = Worksheets("FolderDataImport")

Set TWs = Worksheets("Pack")

TWs.Range("D2:D" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""["",FolderDataImport!A1)-2),FIND(""-"",FolderDataImport!A1)+3,LEN(FolderDataImport!A1)),""_"","" ""))"

I would be grateful for any assistance as this is beyond my skills.

I tried to format the above as code but I am unsure if it worked.,

Thank you in advance.