Another Syntax Error

[ashraf999]

When I enter the following formula, I receive the following error:

"the specified formula cannot be entered because it uses more levels of nesting"

Please Login or Register  to view this content.

How can I change this code to make it work?

[bebo021999]

In case of limitation of nesting, try to name sub-formula, i.e:
TEXT1 = LEFT(J$6,2)&TEXT(LEFT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")&"-"&TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")
Replace with TEXT1:
=IFERROR(INDEX(.....,MATCH($B$7&TEXT1,...)...)

[ashraf999]

In case of limitation of nesting, try to name sub-formula, i.e:
TEXT1 = LEFT(J$6,2)&TEXT(LEFT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")&"-"&TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")
Replace with TEXT1:
=IFERROR(INDEX(.....,MATCH($B$7&TEXT1,...)...)

I am not familiar with sub fromulas in excel. Is the whole code entered in one cell? or do you have to define them in separate cells?

[bebo021999]

I means you could use defined name by press Ctrl-F3, name: TEXT1; Refer to: =LEFT(......), OK
Then replace your origin formula with TEXT1 name

[ashraf999]

I means you could use defined name by press Ctrl-F3, name: TEXT1; Refer to: =LEFT(......), OK
Then replace your origin formula with TEXT1 name

I am not able to define names because the sheet is protected. Is there a workaround to do this without having to name a formula?

[bebo021999]

Try to use a helper cell :e.g B100=TEXT(LEFT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")&"-"&TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")

Then your formula will be:

Please Login or Register  to view this content.

[ashraf999]

Try to use a helper cell :e.g B100=TEXT(LEFT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")&"-"&TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(J$6,FIND(" ",J$6&" ")-1),LEFT(J$6,2),""),"-",REPT(" ",10)),10),"000")

Then your formula will be:

Please Login or Register  to view this content.

I think your using of "Text" function returns different results. But I tried your (post #2) formula on an unprotected sheet and that returned accurate results