Apr 11 2021 09:57 AM
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.
Apr 11 2021 01:22 PM
SolutionTry
TWs.Range("D2:D" & Lr + 1).Formula = "=IF(ISNUMBER(FIND(""["",FolderDataImport!A1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)+1)),MID(FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)-FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)-1),"""")"
Apr 11 2021 04:02 PM
Apr 11 2021 01:22 PM
SolutionTry
TWs.Range("D2:D" & Lr + 1).Formula = "=IF(ISNUMBER(FIND(""["",FolderDataImport!A1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)+1)),MID(FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)-FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)-1),"""")"