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

Advertisements

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

  1. Pingback: #Excel Super Links #124 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s