#Excel Short and Sweet Tip #28: Jazzing Up a Star Rating by Using the TEXTJOIN by David Hager

 

It is relatively simple to construct a star rating string (in this case 8 out of 10) by using the REPT function in the following formula.

=REPT(UNICHAR(9733),8)&REPT(UNICHAR(9734),2)

gives ★★★★★★★★☆☆

However, by using the TEXTJOIN function and an array of the characters from the previous formula using the MID function, the following array formula can insert spaces between the stars.

=TEXTJOIN(” “,,MID(REPT(UNICHAR(9733),8)&REPT(UNICHAR(9734),2),ROW(INDIRECT(“1:10”)),1))

gives ★ ★ ★ ★ ★ ★ ★ ★ ☆ ☆

Of course, any character (a dash) can be be used as the “spacer” in this formula construction.

=TEXTJOIN(“-“,,MID(REPT(UNICHAR(9733),8)&REPT(UNICHAR(9734),2),ROW(INDIRECT(“1:10”)),1))

gives ★-★-★-★-★-★-★-★-☆-☆

This technique is not limited to stars, so I am sure that you will find other great uses for this!