Alternative when you hit the substitute nesting limit

[koltregaskes]

I am trying to make user friendly names into database friendly names. I have a list of 2000+ field names that I've been trying to cut down to a format I can use for our database. For example:

"Drawings Issued" >>> "DWG_ISSUED"
"Quote Received" >>> "QUOTE_RECEIVED"
"Project Manager" >>> "PM"
"Radio Planner" >>> "TXPLANNER"
"Detailed Design Drawings Uploaded" >>> "DDDWG_UPLOADED"
"Job Number Build" >>> "JOBNUM_BLD"
"Revisions Required" >>> "REV_REQUIRED"

I've been able to use SUBSTITUTE up until now but I've just hit the 64 nested limit. Is there an alternative to using the nested SUBSTITUTE formula? I was thinking I could use a VLOOKUP to lookup up the user friendly name and what the DB friendly substitute. So I could have another table where column A lists the user friendly text and column B lists the DB friendly text. For example:

Column A >>> Column B
Drawings >>> DWG
Issued >>> _ISSUED
Build >>> BLD
Number >>> NUM
Radio >>> TX

Is this possible? Please let me know if you need any more details from me?

 

[AlphaFrog]

I could use a VLOOKUP to lookup up the user friendly name and what the DB friendly substitute. So I could have another table where column A lists the user friendly text and column B lists the DB friendly text. For example:

Column A >>> Column B
Drawings >>> DWG
Issued >>> _ISSUED
Build >>> BLD
Number >>> NUM
Radio >>> TX

Is this possible?

That's very possible and a good solution. What do you need help with?