Need help! Chances are it's a simple formula I'm overlooking

[s2wtroll]

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!

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[s2wtroll]

On 18 Apr 2007 18:40:18 -0700, [email protected] wrote:

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?

 

[Ron Rosenfeld]

On 18 Apr 2007 18:40:18 -0700, [email protected] wrote:

What's the best formula to use to do the following:

Change a series of numbers into letters.

For Ex:

and change it to

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

 

[T. Valko]

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

@nospam.org>
wrote:





On 18 Apr 2007 18:40:18 -0700, [email protected] 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

 

[Ron Rosenfeld]

Those "-"'s come from Google Groups.

Ah -- I see they creep in at the spot where Google forces a line break. I
never noticed that before.
--ron