On 18 Apr 2007 18:40:18 -0700,
wrote:
What's the best formula to use to do the following:
Change a series of numbers into letters.
For Ex:
0123
and change it to
OBSA
Any help, would be GREATLY appreciated it!
How simple it is depends on the relationship between the numbers and the
letters. If there is a mathematically describable relationship between the
alphabetical sequence and the numerical sequence, then a simple formula could
do the job.
If the relationship is random, then you the solution becomes more difficult.
For a random relationship, one solution would be to download and install
Longre's free (and easily distributable) morefunc.xll add-in from
http://xcell05.free.fr/ and then use this Regular Expression formula:
=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(10)","[1=B,2=S,3=A,4=Z,5=X,6=T,7=Y,8=H,9=L,10=O]")
This assumes your number series is in A1.
You can change the last argument:
"[1=B,2=S,3=A,4=Z,5=X,6=T,7=Y,8=H,9=L,10=O]"
to reflect whatever substitution you wish.
Note that your number string will need to be entered as text if it includes a
leading zero; or the text function could be incorporated into the first
argument if the strings are all of the same length.
--ron