MrExcel.com - Your One Stop for Excel Tips & Solutions

Substitution limits?

Posted by A Miller on August 30, 2001 5:14 AM

Is there a limit to how many Substitutions that you can enter into one cell? I can enter 8 substitutions but on the 9th one I get an error message saying there is something incorrect in my formula. Does anyone know a way around this? The formula I am using is as follows:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!I1,"Interior Nonbearing Partition","INBP"),"Floor/Ceiling Assembly","F/C Assembly"),"Interior Bearing Wall", "IBW"), "Interior Wall", "IW"), "Exterior Bearing Wall", "EBW"), "Exterior Nonbearing Wall", "ENBW"), "Exterior Wall", "EW"), "Floor", "Floor"), "Roof", "Roof"), "Roof/Ceiling Assembly", "R/C Assembly"), "Party/Fire Wall", "P/F Wall"), "Column", "Column"), "Beam", "Beam"), "Girder", "Girder"), "Truss", "Truss"), "Arch", "Arch")

Everything is fine until I get to the "Roof" entry at which point is gives me an error message.

Posted by Aladin Akyurek on August 30, 2001 6:09 AM

You don't have to substitute "Girder" for "Girder". I suggest that you eliminate first these type of (superfluous) substitutions.

If it's still not working although the formula is correct, use an additional column to store an intermediate result.

Aladin