Microsoft Office Tutorials and References

In Depth Information

**USE A SELF-REFERENCING FORMULA**

6.

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

Ctrl+Shift+Enter.

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).

7.

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
.