LinkBack URL
About LinkBacksMy 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?
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 <[email protected]>
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 <[email protected]>
> 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(RIGHT(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 <[email protected]>
> > 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(RIGHT(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 <[email protected]>
> > > 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 here: 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 <[email protected]>
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(RIGHT(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 <[email protected]>
>> > > 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?
>> > >
>> > >
Ah, so THAT's how you do it.
It's absolute magic. Except that I left out most important part: In our case
the "real" code is not as simple as 0-1 = A-J. In reality, 0-X, 1=I, 2=L,
3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P. I guess that's a whole 'nother thing,
huh?
You've gone way beyond the call of duty already, so if this is a biggy, I'd
be grateful even if you just pointed me in the right direction as to how to
figure this out.
Extreme thanks,
Carl Greene
"Gord Dibben" wrote:
> 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 here: 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 <[email protected]>
> 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(RIGHT(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 <[email protected]>
> >> > > 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?
> >> > >
> >> > >
>
>
Here is a start to your query. I'm sure you can figure out the missing pieces. Sorry it took so long. I lost this thread (doing a search on my name on the microsoft site did not show it.)
Function CharDigit(Cell As String) As String
Dim l As Integer, i As Integer
Dim sTemp As String, sChar As String
sTemp = ""
l = Len(Cell)
For i = 1 To l
' sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
' 0-X, 1=I, 2=L,
' 3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P.
sChar = Mid(Cell, i, 1)
Select Case sChar
Case "0"
sTemp = sTemp & "X"
Case "1"
sTemp = sTemp & "I"
Case "2"
sTemp = sTemp & "L"
Case "3"
sTemp = sTemp & "E"
Case "9"
sTemp = sTemp & "P"
Case Else
sTemp = sTemp & "! " & sChar & " is not in list!"
End Select
Next i
CharDigit = sTemp
End Function
Originally Posted by CarlG
Thanks ever so much. You've solved my problem perfectly and elegantly; I
REALLY appreciate your taking the time.
Carl Greene
"Kevin Vaughn" wrote:
>
> Here is a start to your query. I'm sure you can figure out the missing
> pieces. Sorry it took so long. I lost this thread (doing a search on
> my name on the microsoft site did not show it.)
>
> Function CharDigit(Cell As String) As String
> Dim l As Integer, i As Integer
> Dim sTemp As String, sChar As String
> sTemp = ""
> l = Len(Cell)
> For i = 1 To l
> ' sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
> ' 0-X, 1=I, 2=L,
> ' 3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P.
> sChar = Mid(Cell, i, 1)
> Select Case sChar
> Case "0"
> sTemp = sTemp & "X"
> Case "1"
> sTemp = sTemp & "I"
> Case "2"
> sTemp = sTemp & "L"
> Case "3"
> sTemp = sTemp & "E"
> Case "9"
> sTemp = sTemp & "P"
> Case Else
> sTemp = sTemp & "! " & sChar & " is not in list!"
> End Select
> Next i
> CharDigit = sTemp
> End Function
> CarlG Wrote:
> > Ah, so THAT's how you do it.
> >
> > It's absolute magic. Except that I left out most important part: In our
> > case
> > the "real" code is not as simple as 0-1 = A-J. In reality, 0-X, 1=I,
> > 2=L,
> > 3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P. I guess that's a whole 'nother
> > thing,
> > huh?
> >
> >
> >
> > You've gone way beyond the call of duty already, so if this is a biggy,
> > I'd
> > be grateful even if you just pointed me in the right direction as to
> > how to
> > figure this out.
> >
> > Extreme thanks,
> > Carl Greene
> >
> > "Gord Dibben" wrote:
> >
> > > 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 here: 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
> > <[email protected]>
> > > 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(RIGHT(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
> > <[email protected]>
> > > >> > > 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?
> > > >> > >
> > > >> > >
> > >
> > >
>
>
> --
> Kevin Vaughn
> ------------------------------------------------------------------------
> Kevin Vaughn's Profile: http://www.excelforum.com/member.php...o&userid=34857
> View this thread: http://www.excelforum.com/showthread...hreadid=570169
>
>
You're welcome. Thanks for the feedback.
--
Kevin Vaughn
"CarlG" wrote:
> Thanks ever so much. You've solved my problem perfectly and elegantly; I
> REALLY appreciate your taking the time.
>
> Carl Greene
>
> "Kevin Vaughn" wrote:
>
> >
> > Here is a start to your query. I'm sure you can figure out the missing
> > pieces. Sorry it took so long. I lost this thread (doing a search on
> > my name on the microsoft site did not show it.)
> >
> > Function CharDigit(Cell As String) As String
> > Dim l As Integer, i As Integer
> > Dim sTemp As String, sChar As String
> > sTemp = ""
> > l = Len(Cell)
> > For i = 1 To l
> > ' sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
> > ' 0-X, 1=I, 2=L,
> > ' 3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P.
> > sChar = Mid(Cell, i, 1)
> > Select Case sChar
> > Case "0"
> > sTemp = sTemp & "X"
> > Case "1"
> > sTemp = sTemp & "I"
> > Case "2"
> > sTemp = sTemp & "L"
> > Case "3"
> > sTemp = sTemp & "E"
> > Case "9"
> > sTemp = sTemp & "P"
> > Case Else
> > sTemp = sTemp & "! " & sChar & " is not in list!"
> > End Select
> > Next i
> > CharDigit = sTemp
> > End Function
> > CarlG Wrote:
> > > Ah, so THAT's how you do it.
> > >
> > > It's absolute magic. Except that I left out most important part: In our
> > > case
> > > the "real" code is not as simple as 0-1 = A-J. In reality, 0-X, 1=I,
> > > 2=L,
> > > 3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P. I guess that's a whole 'nother
> > > thing,
> > > huh?
> > >
> > >
> > >
> > > You've gone way beyond the call of duty already, so if this is a biggy,
> > > I'd
> > > be grateful even if you just pointed me in the right direction as to
> > > how to
> > > figure this out.
> > >
> > > Extreme thanks,
> > > Carl Greene
> > >
> > > "Gord Dibben" wrote:
> > >
> > > > 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 here: 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
> > > <[email protected]>
> > > > 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(RIGHT(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
> > > <[email protected]>
> > > > >> > > 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?
> > > > >> > >
> > > > >> > >
> > > >
> > > >
> >
> >
> > --
> > Kevin Vaughn
> > ------------------------------------------------------------------------
> > Kevin Vaughn's Profile: http://www.excelforum.com/member.php...o&userid=34857
> > View this thread: http://www.excelforum.com/showthread...hreadid=570169
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks