Convert string of abbreviations into full words

I am working with physical data names where all the names are abbreviated and separated by an underscore. e.g. "lab_tech_id". On the second tab of the spreadsheet I have the abbreviation in column A ...

Formulas and Functions

Macros and VBA

HansVogelaar
May 16, 2023
DataMiner21146

Name the list of abbreviations on the second sheet Abbr, and name the list of full terms in the second column Term.

With lab_tech_id in A2 on the first sheet, enter the following formula in B2:

=TEXTJOIN(" ", TRUE, XLOOKUP(TEXTSPLIT(A2, "_"), Abbr, Term, ""))

If you want to force the result to proper case:

=PROPER(TEXTJOIN(" ", TRUE, XLOOKUP(TEXTSPLIT(A2, "_"), Abbr, Term, "")))