How do I shift text values...

[Stargrove]

I have a table of data and I need to export that data to a CSV text file so it will be usable by a different program. However, I need to transform one of the fields that contains a unique six digit number that I generated in Excel. The transformation on each digit is always the same. For example: 1=>, 2=?, 3=@, 4=A, 5=B, 6=C, 7=D, 8=E, 9=F. The number 536854 would come out to be B@CEBA. I currently have a list of about 288 of these that need to be "transformed" for export.

Unfortunately, I don't know where to start and figured someone here could help. Thanks.

James

 

[SkipVought]

Hi,

Paste this function into a VB Module.

Use in your query like any other function.

BTW, is there a conversion for ZERO...

Function TransCode(sVal As String) As String
'1=>, 2=?, 3=@, 4=A, 5=B, 6=C, 7=D, 8=E, 9=F
    Dim i As Integer, s As String
    For i = 1 To Len(sVal)
        s = Mid(sVal, i, 1)
        Select Case s
            Case 0
                'is there a conversion here?
            Case 1
                TransCode = TransCode & ","
            Case 2
                TransCode = TransCode & "?"
            Case 3
                TransCode = TransCode & "@"
            Case 4 To 9
                TransCode = TransCode & Chr(s + 61)
        End Select
    Next
End Function

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[TheAceMan1]

How are ya Stargrove . . .

Another variation:

[blue]Public Function ConvCVS(OldVal As String) As String
   Dim x As Integer, ans As String, Dig As Integer
   
   For x = 1 To Len(OldVal)
      Dig = Val(Mid(OldVal, x, 1)) + 1
      ans = ans & Choose(Dig, "~", ">", "?", "@", "A", "B", "C", "D", "E", "F")
   Next
   
   ConvCVS = ans

End Function[/blue]

See Ya! . . . . . .

 

[Darrylles]

Hi Star,

It seems to me that this can be improved upon, even moreso if most of your list of codes is sequential (as in the ASCII table).

i.e. for code values between 4 and 30, then the ASCII character is: asc(int(code value)+ 61).

e.g.

IF ((code value >=4) AND (code value <=30) THEN
ANS = asc(code value + 61)
ENDIF

So a code value of 4 would be ASCII value of 4 + 61 which = 'A'.

ATB

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk