View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Need help! Chances are it's a simple formula I'm overlooking

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