When we want to create a star rating in Excel, there is at least one possible solution.
How to create a star rating in Excel in 3 minutes
We want to use stars from the Unichars, Unicode. We just need to use the UNICHAR function and a proper unicode (Fig. 1).
Fig. 1 UNICHAR function
In older versions of Excel, copying those stars from the Internet should also work. We just have to have the signs we want to repeat. As we can see, we already have number rating. The biggest number is five, and the lowest is zero and there is a proper number of stars next to them. Let’s delete them however, and start creating a proper formula from the beginning. We start with the REPT function. We write G4 because our text string is there, then press the F4 key to lock it. When we want to add the number of times we want to repeat, we write C2, which is in the Rating column. Then, we close the formula (Fig. 2).
=REPT($G$4,C2)
Fig. 2 REPT function
We can copy it down and we have our black stars (Fig. 3).
Fig. 3 Start rating
However, we assumed that we always want to have five stars. It means that if the rating is lower than five, we have to add some white stars. In such a case, we just have to combine one REPT function with another REPT function using an ampersand. In the second REPT function, we have to write G5 and press the F4 key to lock it. In the place on the number of repeats, we have to write five minusC2 - a cell from the Rating column.
=REPT($G$4,C2) &REPT($G$5,5-C2)
Fig. 4 Two REPT functions combined with an ampersand
When we tell the REPT function to repeat zero times, it won’t repeat anything, i.e. it will return an empty text string. In the case of a 5‑star rating, we have five stars. However, then it comes to a cell with zero, the REPT function treats it the same as an empty cell in most situations (Fig. 5).
Today, we are going to talk about creating charts like data bars in cells using formulas. We want to create something like this (Fig. 1)
Data bars in a cell using character repeatFig. 1 Charts from data bars
Let’s start with creating a proper formula. We have to use the REPT (repeat) function. It will repeat a text, one or more chars, as many times as we write in the second argument. Let’s start with a pipe (a vertical line). We want to repeat it as many times as there are sunny days in a given month. Our formula looks like this (Fig. 2).
=REPT(“|”,B2)
Fig. 2 REPT formula with a pipe
After entering and copying down, we have data bars. However, the distance between individual signs is too big (Fig. 3)
Fig. 3 Data bars with too long distances
We have to narrow it by using, e.g. a different font that works with this sign. We are selecting our data bars and changing the font name to Script (Fig. 4). Now, it looks much better.
Fig. 4 Font change
Let’s try, however, a different font. With Britannica Bolt, the signs look like real data bars (Fig. 5).
Fig. 5 Font change again
Let’s take the last example, which is the Haettenschweiler font. In my opinion, it looks the best, so I’m staying with this one (Fig. 6)
Fig. 6 The best font
Apart from repeating a pipe, we can repeat other signs. If they are wider than the pipe and if the number of repeats is too big, we may have a problem (Fig. 7)
=REPT(“*”,B2)
Fig. 7 A formula with an asterisk
We can see, that the asterisk string is too long (Fig. 8)
Fig. 8 Too long
The solution is dividing the number, and the REPT function will only look at the integer part of the number. Let’s divide our numbers safely by two (Fig. 9).
=REPT(“*”,B2/2)
Fig. 9 Number division
When there is a smaller number of repeats, it looks quite good (Fig. 10)
Fig. 10 Shorter strings
We have to remember that an asterisk is a simple sign, and we can use whatever sign we like. Let’s take, for example, a star (Fig. 11)
=REPT(“★”,B2/2)
Fig. 11 A formula with a star
We can see that the strings are very long (Fig. 12). We can divide them by even a greater number, e.g. 3.
=REPT(“★”,B2/3)
Fig. 12 Dividing by 3
After putting the formula, it looks good (Fig. 13).
Fig. 13 Shorter strings
In our last example, we are going to repeat the letter n (Fig. 14)
=REPT(“n”,B2/3)
Fig. 14 Repeating n
Now, we can see many ns (Fig. 15)
Fig. 15 N string
However, we can change our font into Windings, and the letter n changes into squares, which looks quite good as data bars (Fig. 16)
Fig. 16 N into squares
Summing, up, when we use different signs and different number of repeats, we can create quite good data bars using a formula.
Today, we are going to talk about extracting first names, middle names and last names from full names. We define the first name as the first word from the left, the last name as the first word from the right, and the middle name, as everything between those two. Middle names are a bit complicated, as they can may consist of one word, a space, an abbreviation, or two or more words (Fig. 1)
Extract First Last and Middle Name with FormulasFig. 1 A list of names
Let’s start with extracting the first name. We just have to use the LEFT function, which extracts signs from the left. We have to insert the FIND function there, so that it finds the first space, and write the space in double quotes (Fig. 2)
=LEFT(A2,FIND(“ “,A2))
Fig. 2 Extracting the first name
Now, we have the first name (Fig. 3).
Fig. 3 The first name
But, we have to remember that the FIND function will return the position of the sign we’re looking for, which means that it finds the 7th position, which is a space (Fig. 4).
Fig. 4 7th position
The first name , however, consists of only 6 letters, which is what we want. It means that we want to extract less letters. So, we have write the formula once again, but this time we subtract one sign (Fig. 5)
=LEFT(A2,FIND(“ “,A2)-1)
Fig. 5 Subtracting one sign
Now, we have only the first name without unnecessary signs. After dragging it down, the whole column is full (Fig. 6)
Fig. 6 Full column
Let’s go to last names. This case is a bit more complicated. We don’t know whether we’re looking for the first, the second, the third or even a more distant space (Fig. 7)
Fig. 7 Too many possible spaces
There’s a trick to solve it. We have to repeat spaces. Within our text, we have to substitute a single space into many spaces, let’s say 29. We use the REPT function (Fig. 8)
=SUBSTITUTE(A2,” “,REPT(“ “,29))
Fig. 8 Repeating spaces
After accepting our function, we have our changed names with many spaces between them (Fig. 9)
Fig. 9 Names with many spaces
Now, we can start extracting names from the right side. We’re extracting not only all signs from our name, but also the remaining 29 signs (Fig. 10).
=RIGHT(SUBSTITUTE(A2,” “,REPT(“ “,29)),29)
Fig. 10 Extracting name and 29 signs
Now, we have the last name and many, many spaces before it (Fig. 11)
Fig. 11 Name with spaces
We have to delete them using the TRIM function. The TRIM function removes all spaces from a text string except for a single space between words. So, each space at the beginning and at the end of the string will be removed (Fig. 12)
=TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(“ “,29)),29))
Fig. 12 Trimming unnecessary spaces
After accepting it and copying it down, we have our last names (Fig. 13).
Fig. 13 Last names
Now, we have to find the thing between the first and the last name. We will use the SUBSTITUTE function. In the place of the first name, we will place nothing, i.e. an empty text string (Fig. 14).
=SUBSTITUTE(A2,B2,“”)
Fig. 14 Finding the middle name
Now, we have a shorter version of our name (Fig. 15).
Fig. 15 Shorter version
Let’s remove the last name now. We want to substitute the last name with an empty text string (Fig. 16)
=SUBSTITUTE(SUBSTITUTE(A2,B2,“”),D2,“”)
Fig. 16 Removing the last name
After entering and copying down, we have everything between the first name and the last name (Fig. 17). As you can see, we have also extracted spaces which we don’t need. So, let’s use the TRIM function to delete unnecessary spaces.
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,B2,“”),D2,“”))
Fig. 17 Unneeded spaces
Because we are editing whole column we can press Ctrl + Enter.
That’s how we extract first names, last names and everything in between with appropriate formulas.
