Forum Discussion

DataMiner21146's avatar
DataMiner21146
Copper Contributor
May 16, 2023
Solved

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

bosinander's avatar
bosinander
Iron 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