Microsoft Office Tutorials and References
In Depth Information
USE A SELF-REFERENCING FORMULA
Keep pressing F9. When you have 0 and all of the characters, press F9 one
last time, and the 0 is removed.
To start over, go to B1, press F2 to put the formula in Edit mode, and press
Breaking It Down: Let’s say you put AbCdWxYz in cell A1. When the formula
starts, the value of B1 is 0 . The LEN of A1 is 8, and the LEN of B1 is 0 , so the
formula takes whatever is in B1 and concatenates it with the MID of A1. The
MID function says to start at the character that is the LEN of A1 minus the
LEN of B1 + 1. In the ﬁ rst calculation, this appends the starting 0 with the ﬁ nal
character, and you will have 0z in cell B1 (Figure 59).
Figure 59. Initially, you get a 0 and the last character.
As the result in B1 gets longer, the formula keeps appending characters further
from the end of A1. After you press F9 a few more times, you have 0zY in B1.
The LEN of A1 is still 8. The LEN of B1 is 3. You are using 8+1–3, so you are
now asking for the sixth character to be appended to B1, and you now get
0zYx in B1, as shown in Figure 60.
Figure 60. With each press of the F9 key, you get an additional character.
You eventually get to the point where you have a 0 and the complete text from
A1, in reverse, as shown in Figure 61.
Figure 61. At er gathering all the characters, you still have a starting 0 .