Long formula "Compile error: Syntax error"

[Muralikrishnan]

Hi There,
I am applying a formula in excel and it is working fine. I am recording the macro with the formula and I am getting Syntax error. The formula is "=IF(RIGHT(TRIM(LEFT(F2,IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(FIND(".",F2,1),FIND(" @",F2,1)),FIND(" 0",F2,1)),FIND(" 1",F2,1)),FIND(" 2",F2,1)),FIND(" 3",F2,1)),FIND(" 4",F2,1)),FIND(" 5",F2,1)),FIND(" 6",F2,1)),FIND(" 7",F2,1)),FIND(" 8",F2,1)),FIND(" 9",F2,1)))),1)=".",LEFT(TRIM(LEFT(F2,IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(FIND(".",F2,1),FIND(" @",F2,1)),FIND(" 0",F2,1)),FIND(" 1",F2,1)),FIND(" 2",F2,1)),FIND(" 3",F2,1)),FIND(" 4",F2,1)),FIND(" 5",F2,1)),FIND(" 6",F2,1)),FIND(" 7",F2,1)),FIND(" 8",F2,1)),FIND(" 9",F2,1)))),(LEN(TRIM(LEFT(F2,IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(FIND(".",F2,1),FIND(" @",F2,1)),FIND(" 0",F2,1)),FIND(" 1",F2,1)),FIND(" 2",F2,1)),FIND(" 3",F2,1)),FIND(" 4",F2,1)),FIND(" 5",F2,1)),FIND(" 6",F2,1)),FIND(" 7",F2,1)),FIND(" 8",F2,1)),FIND(" 9",F2,1)))))-1)),TRIM(LEFT(F2,IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(FIND(".",F2,1),FIND(" @",F2,1)),FIND(" 0",F2,1)),FIND(" 1",F2,1)),FIND(" 2",F2,1)),FIND(" 3",F2,1)),FIND(" 4",F2,1)),FIND(" 5",F2,1)),FIND(" 6",F2,1)),FIND(" 7",F2,1)),FIND(" 8",F2,1)),FIND(" 9",F2,1)))))"

Appreciate someone's help to fix the issue in VBA.
Thanks in advance,
Muralikrishnan V

 

[Michael M]

All of the single quotes need to be replaced with double quotes when using it in VBA

eg...

FIND(".",F2,1

should be

FIND([color=red]"".""[/color],F2,1

 

[Muralikrishnan]

Thank you Michael. I replaced with two double quotes. Still I m getting the same error

 

[Michael M]

What are you actually trying to do with the formula...maybe if you posted a SMALL sample of before and after data someone could provide a more useful solution !!!

 

[Peter_SSs]

Before you get to the vba implementation ..

1. If you are using Excel 2010 or later, does this formula used directly in the worksheet, give you the results you expect? If not, please give an example of the F2 text and the expected result for that text.

=LEFT(F2,AGGREGATE(15,6,(FIND(" "&{".","@",0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(F2,"."," ."))),1)-1)


2. If you are using Excel 2007 or earlier, does this formula used directly in the worksheet, give you the results you expect? If not, please give an example of the F2 text and the expected result for that text.

=LEFT(F2,MIN(FIND(" "&{".","@",0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(F2,"."," .")&" . @ 0 1 2 3 4 5 6 7 8 9"))-1)

 

[Muralikrishnan]

Thank you Michael and Peter

 

[Peter_SSs]

Thank you Michael and Peter

You are welcome.

Out of interst did you
- fix the problem with the long formula, or
- use one the short ones I suggested, or
- something else?