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!

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

On Wed, 18 Apr 2007 22:33:10 -0400, Ron Rosenfeld
wrote:

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

Minor typo:

=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(0)","[1=B,2=S,3=A,4=Z,5=X,6=T,7=Y,8=H,9=L,0=O]")


--ron

On Apr 18, 10:40 pm, Ron Rosenfeld wrote:
On Wed, 18 Apr 2007 22:33:10 -0400, Ron Rosenfeld
wrote:





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

Minor typo:

=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(0)","[1=B,2=S,3=*A,4=Z,5=X,6=T,7=Y,8=H,9=L,0=O]")

--ron- Hide quoted text -

- Show quoted text -

Thanks Ron, I will give it a try. Is there any formula I can use
without downloading the software?

On 18 Apr 2007 20:12:07 -0700, wrote:

On Apr 18, 10:40 pm, Ron Rosenfeld wrote:
On Wed, 18 Apr 2007 22:33:10 -0400, Ron Rosenfeld
wrote:





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

Minor typo:

=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(0)","[1=B,2=S,3=*A,4=Z,5=X,6=T,7=Y,8=H,9=L,0=O]")

--ron- Hide quoted text -

- Show quoted text -

Thanks Ron, I will give it a try. Is there any formula I can use
without downloading the software?


If you have Excel 2007, you could try nested SUBSTITUTE's, but in prior
versions the nesting is limited to seven levels, and you require nine.

You could write a VBA UDF that would do the same thing, but why reinvent the
wheel?

By the way, in looking at your "quote" of my message, I see some extraneous
"-"'s. In the first, I see -3=A and in the second I see a 3=-A.

I do NOT see those extraneous dashes on my original, and they don't belong
there! Must be an issue with some kind of translation.

I'm going to post it again, but this time on two lines:

=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(0)",
"[1=B,2=S,3=A,4=Z,5=X,6=T,7=Y,8=H,9=L,0=O]")

If you quote it back, it'll be interesting to see if extra dashes sneak back
in.
--ron

By the way, in looking at your "quote" of my message, I see some extraneous
"-"'s. In the first, I see -3=A and in the second I see a 3=-A.

I do NOT see those extraneous dashes on my original, and they don't belong
there! Must be an issue with some kind of translation.

Those "-"'s come from Google Groups.

Biff

"Ron Rosenfeld" wrote in message
...
On 18 Apr 2007 20:12:07 -0700, wrote:

On Apr 18, 10:40 pm, Ron Rosenfeld wrote:
On Wed, 18 Apr 2007 22:33:10 -0400, Ron Rosenfeld

wrote:





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

Minor typo:

=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(0)","[1=B,2=S,3=*A,4=Z,5=X,6=T,7=Y,8=H,9=L,0=O]")

--ron- Hide quoted text -

- Show quoted text -

Thanks Ron, I will give it a try. Is there any formula I can use
without downloading the software?


If you have Excel 2007, you could try nested SUBSTITUTE's, but in prior
versions the nesting is limited to seven levels, and you require nine.

You could write a VBA UDF that would do the same thing, but why reinvent
the
wheel?

By the way, in looking at your "quote" of my message, I see some
extraneous
"-"'s. In the first, I see -3=A and in the second I see a 3=-A.

I do NOT see those extraneous dashes on my original, and they don't belong
there! Must be an issue with some kind of translation.

I'm going to post it again, but this time on two lines:

=REGEX.SUBSTITUTE(A1,"(1)|(2)|(3)|(4)|(5)|(6)|(7)| (8)|(9)|(0)",
"[1=B,2=S,3=A,4=Z,5=X,6=T,7=Y,8=H,9=L,0=O]")

If you quote it back, it'll be interesting to see if extra dashes sneak
back
in.
--ron