By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,470 Members | 949 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to replace number to Alphabet

100+
P: 110
VBA code that replaces any numeric value with alphabet (Excel column).

Expand|Select|Wrap|Line Numbers
  1. Function Convert_Number_to_Aplha(ByVal num As Long) As String
  2.     Dim buf As String
  3.     buf = Cells(1, num).Address(True, False)
  4.     Convert_Number_to_Aplha = Left(buf, InStr(buf, "$") - 1)
  5. End Function
Example
Expand|Select|Wrap|Line Numbers
  1. Sub sample1()
  2.     Dim S As String
  3.     Dim i As Integer
  4.     For i = 1 To 40
  5.         S = Convert_Number_to_Aplha(i)
  6.         Cells(1, i).Value = S
  7.     Next i
  8. End Sub
Feb 21 '20 #1
Share this Article
Share on Google+
5 Comments


100+
P: 110
This code determines if the argument is a number or an alphabet, returns the alphabet if it is a number, and returns the number if it is an alphabet.

Expand|Select|Wrap|Line Numbers
  1. Function Replace_NA_or_AN(ByVal val As Variant) As Variant
  2.     Dim buf As String
  3.     If IsNumeric(val) = True Then
  4.         buf = Cells(1, val).Address(True, False)
  5.         Replace_NA_or_AN = Left(buf, InStr(buf, "$") - 1)
  6.     Else
  7.         Replace_NA_or_AN = Range(val & "1").Column
  8.     End If
  9. End Function
Example

Expand|Select|Wrap|Line Numbers
  1. Sub sample2()
  2.     Dim S As Variant
  3.     Dim i As Integer
  4.     For i = 1 To 40
  5.         S = Replace_NA_or_AN(i)
  6.         Cells(1, i).Value = S
  7.     Next i
  8.     For i = 1 To 40
  9.         S = Cells(1, i).Value
  10.         Cells(2, i).Value = Replace_NA_or_AN(S)
  11.     Next i
  12. End Sub
Feb 21 '20 #2

gits
Expert Mod 5K+
P: 5,387
well - may i ask what realistic usecase could be behind that? So far i never encountered something where i would have had the need for such code in a real scenario?
Feb 21 '20 #3

100+
P: 110
Hi gits
In cell operations (make the cell active, assignment and reference),
I think that it is useful when it can be handled by renge () (multiple) or Cells () (single).
Feb 21 '20 #4

gits
Expert Mod 5K+
P: 5,387
ok thanks - i can see how it might help in demonstrating how to access/modify cells. i was just a bit confused why i would like to replace a number with a character or vice versa in reality. Just never had such a requirement in real life so far - so i just did ask to try to get a bit more context around the plain posted code.
Feb 21 '20 #5

Expert 100+
P: 202
I believe writing bit of theory/explanation related to the code can make it work better for the "articles/how to guides" category.
4 Weeks Ago #6