Forum Discussion
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 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!
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, "")))
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 )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, "")))
- Perfect! And used the one with "Proper case"!
