My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?

A VLOOKUP table would probably do the trick.

See Excel help on VLOOKUP or go to Debra Dalgleish's site for "real" help.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 15:42:02 -0700, CarlG
wrote:

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?

How would that work on a digit-by-digit basis, to convert, for example, "256"
to something like "CFI"? (Assuming that A B C D E F G F I and J stood for
0-9)

"Gord Dibben" wrote:

A VLOOKUP table would probably do the trick.

See Excel help on VLOOKUP or go to Debra Dalgleish's site for "real" help.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 15:42:02 -0700, CarlG
wrote:

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?

That would be tough to do using a formula, at least for me. For the given
example, this would work (note it is CFG not CFI)
=CHAR(LEFT(D15,1)+65)&CHAR(MID(D15,2,1)+65)&CHAR(R IGHT(D15,1)+65)
However, since your originaly example was 2 characters and, presumably the
number of characters could vary, this would quickly fail. A short UDF could
work (note, there is no error checking)

Function CharDigit(Cell As String) As String
Dim l As Integer, i As Integer
Dim sTemp As String
sTemp = ""
l = Len(Cell)
For i = 1 To l
sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
Next i
CharDigit = sTemp
End Function

here is some sample output:
0123456789 ABCDEFGHIJ
3210 DCBA
256 CFG


--
Kevin Vaughn


"CarlG" wrote:

How would that work on a digit-by-digit basis, to convert, for example, "256"
to something like "CFI"? (Assuming that A B C D E F G F I and J stood for
0-9)

"Gord Dibben" wrote:

A VLOOKUP table would probably do the trick.

See Excel help on VLOOKUP or go to Debra Dalgleish's site for "real" help.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 15:42:02 -0700, CarlG
wrote:

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?

Looks PERFECT! The "UDF", that is. Now, how do I use it. If my "numbers" are
column A and I want the converted "text" in column B, how do I apply the
formula?
Thanks!

"Kevin Vaughn" wrote:

That would be tough to do using a formula, at least for me. For the given
example, this would work (note it is CFG not CFI)
=CHAR(LEFT(D15,1)+65)&CHAR(MID(D15,2,1)+65)&CHAR(R IGHT(D15,1)+65)
However, since your originaly example was 2 characters and, presumably the
number of characters could vary, this would quickly fail. A short UDF could
work (note, there is no error checking)

Function CharDigit(Cell As String) As String
Dim l As Integer, i As Integer
Dim sTemp As String
sTemp = ""
l = Len(Cell)
For i = 1 To l
sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
Next i
CharDigit = sTemp
End Function

here is some sample output:
0123456789 ABCDEFGHIJ
3210 DCBA
256 CFG


--
Kevin Vaughn


"CarlG" wrote:

How would that work on a digit-by-digit basis, to convert, for example, "256"
to something like "CFI"? (Assuming that A B C D E F G F I and J stood for
0-9)

"Gord Dibben" wrote:

A VLOOKUP table would probably do the trick.

See Excel help on VLOOKUP or go to Debra Dalgleish's site for "real" help.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 15:42:02 -0700, CarlG
wrote:

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?

Carl

Have you copied the UDF to a General module in your workbook?

If so, just enter in B1 =CharDigit(A1) and double-click on B1 fill handle to
copy down column A

Just a note he if you have a space in the string, the UDF will error.


Gord Dibben MS Excel MVP

On Fri, 11 Aug 2006 16:39:01 -0700, CarlG
wrote:

Looks PERFECT! The "UDF", that is. Now, how do I use it. If my "numbers" are
column A and I want the converted "text" in column B, how do I apply the
formula?
Thanks!

"Kevin Vaughn" wrote:

That would be tough to do using a formula, at least for me. For the given
example, this would work (note it is CFG not CFI)
=CHAR(LEFT(D15,1)+65)&CHAR(MID(D15,2,1)+65)&CHAR(R IGHT(D15,1)+65)
However, since your originaly example was 2 characters and, presumably the
number of characters could vary, this would quickly fail. A short UDF could
work (note, there is no error checking)

Function CharDigit(Cell As String) As String
Dim l As Integer, i As Integer
Dim sTemp As String
sTemp = ""
l = Len(Cell)
For i = 1 To l
sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
Next i
CharDigit = sTemp
End Function

here is some sample output:
0123456789 ABCDEFGHIJ
3210 DCBA
256 CFG


--
Kevin Vaughn


"CarlG" wrote:

How would that work on a digit-by-digit basis, to convert, for example, "256"
to something like "CFI"? (Assuming that A B C D E F G F I and J stood for
0-9)

"Gord Dibben" wrote:

A VLOOKUP table would probably do the trick.

See Excel help on VLOOKUP or go to Debra Dalgleish's site for "real" help.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 15:42:02 -0700, CarlG
wrote:

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?