Forum Discussion

DataMiner21146's avatar
DataMiner21146
Copper Contributor
May 16, 2023

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 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!

Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    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, "")))

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    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, "")))

  • bosinander's avatar
    bosinander
    Steel Contributor
    May 16, 2023

    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
)

     

Resources