CODEWITHSUNDEEP
vbafunctionexcel
AriKari
AriKari

Reputation: 323

Convert numeric characters to alphabetic characters

I am trying to get I/O as follows:

Input : 123490
Output : BCDEJA

Logic is simple:

if
strarr(i)=0,1,2,3,4,5,6,7,8,9
then
strarr(i) should be = A,B,C,D,E,F,G,H,I,J

code

str = .Cells(18, "B").Value
strarr() = Split(str) 
For i = LBound(strarr) To UBound(strarr)
  If strarr(i) = 0 Then
  .Cells(24, "B") = "A" & .Cells(24, "B")
  Else
  If strarr(i) = 1 Then
  .Cells(24, "C") = "B" & .Cells(24, "C")
  Else
  If strarr(i) = 2 Then
  .Cells(24, "C") = "C" & .Cells(24, "C")
  Else
  If strarr(i) = 3 Then
  .Cells(24, "D") = "D" & .Cells(24, "D")
  Else
  .
  .
  .

  If strarr(i) = 9 Then
  .Cells(24, "J") = "J" & .Cells(24, "J")
  Else

  End If x10 times
Next i

.Cells(24, "B") = .Cells(24, "B") & .Cells(24, "C") & .Cells(24, "D") & .Cells(24, "E") & .Cells(24, "F") & .Cells(24, "G") & .Cells(24, "H") & .Cells(24, "I") & .Cells(24, "I") & .Cells(24, "J")

.Cells(18, "D").Value = .Cells(24, "B")

Worksheets("Functions").Rows(24).ClearContents
End With

Can anyone help me out where I am wrong?

Upvotes: 7

Views: 1088

Answers (5)

brettdj
brettdj

Reputation: 55672

I liked Jeeped's answer.

The version below works on this with some tweaks to play with the speed:

  • Mid as a LHS operator (as concatenation in VBA is slower)
  • use of Mid$ and ChrW$

On my testing reduced run-time by ~40% (see edits below)

 Function NumChr(strIn As String) As String
        Dim strTemp As String
        Dim lngChar As Long

        For lngChar = 1 To Len(strIn)
            Mid$(strIn, lngChar, 1) = ChrW$(65 + Mid$(strIn, lngChar, 1))
        Next
        NumChr = strTemp
    End Function

EDIT: Add tests

  1. 3.21 seconds for initial code
  2. 1.98 seconds for second code

high level reconciliation

  • Using Mid$ in first code rather than Mid took code from 3.21 to 2.77 seconds.
  • Using ChrW$ rather than Chr took it from 2.77 to 2.43 seconds.
  • Using Mid$ on the LHS took it to 1.98 seconds

prior code 

Function num_alpha(str As String)
    Dim sTMP As String, d As Long

    For d = 1 To Len(str)
        sTMP = sTMP & Chr(65 + Mid(str, d, 1))
    Next d

    num_alpha = sTMP

End Function

new code 

Function NumChr(strIn As String) As String
    Dim lngChar As Long

    For lngChar = 1 To Len(strIn)
        Mid$(strIn, lngChar, 1) = ChrW$(65 + Mid$(strIn, lngChar, 1))
    Next
    NumChr = strIn
End Function

test timing

Sub Main()
Call Test
Call Test2
End Sub

Sub Test()
Dim dbTimer As Double
dbTimer = Timer()
For i = 1 To 1000000
    s = num_alpha("123490")
Next
Debug.Print Timer() - dbTimer
End Sub

Sub Test2()
Dim dbTimer As Double
dbTimer = Timer()
For i = 1 To 1000000
    s = NumChr("123490")
Next
Debug.Print Timer() - dbTimer
End Sub

Upvotes: 4

Robert J.
Robert J.

Reputation: 2701

This should get you started:

Public Function ConvertValue(iInput As Integer) As String
    ConvertValue = Chr(65 + iInput)
End Function

Please note that value of '65'stands for capital A, lowercase letters start from '97

Upvotes: 2

Ashwith Ullal
Ashwith Ullal

Reputation: 263

=CHAR(64 + 1)
will Give "A"
=CHAR(64 + 2)
will Give "B" 
=CHAR(64 + 3)
will Give "C" 

so on.....

Upvotes: 2

user4039065
user4039065

Reputation:

Make use of the ASCII character numbers (...?) and adjust them by the digits you are converting. A capitol A is ASCII 0×41 or 65 dec.

Function num_alpha(str As String)
    Dim sTMP As String, d As Long

    For d = 1 To Len(str)
        sTMP = sTMP & Chr(65 + Mid(str, d, 1))
    Next d

    num_alpha = sTMP

End Function

Use like any native worksheet function. In D18 as,

=num_alpha(B18)

      Numbers to Characters

Upvotes: 10

Avishay Cohen
Avishay Cohen

Reputation: 2208

As Jeeped said you can use the Chr function to convert a number to a letter, using ASCII.

on another note, when working with a single variable which can have multiple values, instead of using so much if's I would suggest using a case select model, use strarr(i) as the controller it would simplify your code and would be a lot more readable.

Also, instead of writing the different values to cells, I would have used a temporary variable to store the aggregated value, less hassle for you and a bit faster as you don't read/write to the sheet instead you're just working in the background

Upvotes: 3

Related Questions