Convert string of abbreviations into full words

[DataMiner21146]

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 and the associated term in column B. e.g. lab:laboratory; tech:technician; id:identifier. I want to replace all the abbreviations (between the underscores) with their full term separated by spaces. So "Lab_Tech_id" in column A on the first Tab will have "Laboratory Technician Identifier" in column B. Any help is greatly appreciated!

[#2 · Accepted solution]

@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, "")))

[#3]

Hi @DataMiner21146 

Assuming Excel 365.

The attached file will convert to commas instead of swedish semicolon between parameters..

=LET(input;A2;
          abbreviation;   Sheet2!A:A;
          associatedTerm; Sheet2!B:B;
splitted;TEXTSPLIT(input;"_");
lookedup;XLOOKUP(splitted;abbreviation;associatedTerm;"#Missing");
proper;PROPER(lookedup);
joined;TEXTJOIN(" ";;proper);
output;joined;
output
)

[#4]

Perfect! And used the one with "Proper case"!