In Excel 2016, Microsoft introduced two new text functions: CONCAT and TEXTJOIN. Users of versions of Excel prior to Excel 2016 can use the Real Statistics TEXT_JOIN function which provides functionality equivalent to TEXTJOIN.
You can obtain the functionality in CONCAT(txt) via TEXT_JOIN(“”, FALSE, txt) or TEXT_JOIN(“”, TRUE, txt).
In addition, Real Statistics provides a few other text string functions as described below.
Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack contains the following functions.
TEXT_JOIN(delim, ignore_empty, txt): returns a text string that concatenates all the text elements in txt inserting the delimiter delim between each of the text elements
TEXT_REVERSE(s): returns a text string with the characters in text string s in reverse order
CharCount(s, c): returns the number of times character c appears in text string s
DUPECHAR(c, n): returns a text string consisting of the character c repeated n times
txt is a list of cell ranges and/or text strings (separated by commas). If ignore_empty = TRUE then any empty cells in txt are ignored. The delimiter delim can be any text string.
Examples
Examples: Figure 1 shows how to use these functions. Note that the formula in cell D11 is equivalent to the Excel 2016 formula =CONCAT(A3,A10:B10).
Figure 1 – Text string examples
Score worksheet functions
Real Statistics Functions: The Real Statistics Resource Pack contains the following letter score functions.
GRADEVAL(grade, pm): returns a numeric value between 0 and 4.5 where grade A corresponds to 4, B to 3, C to 2, D to 1, and F to 0. pm (plus or minus) takes a value between 0 and .5, where a plus symbol adds the pm value and a minus symbol subtracts the pm value (default 1/3).
VALGRADE(grade, pm, skinny): returns a letter grade (A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D-, F) based on the numeric score specified by grade (which usually takes a value between 0 and 4.5). pm (default 1/3) is used to determine how the plus and minus symbols are assigned to the letter grade. If skinny = TRUE (default FALSE) then the A+ and D- grades are not used (replaced by A and D).
Examples: Figure 2 shows some examples of how to use these grade conversion functions when pm = .2.
Figure 2 – Grade function examples
References
Microsoft (2019) CONCAT function
https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2
ExcelJet (2021) Excel TEXTJOIN function
https://exceljet.net/excel-functions/excel-textjoin-function