• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

number to character and vice-versa

sifar786

Member
Hi,


I have a table consisting of 2 columns:


Code:
blockquote


Cell Letter         Cell #

A                         1

B                         2

C, D                     3, 4

E, F, G                 5, 6, 7

H                          8

I                           9

J                          10

K                         11

L                          12


blockquote


Now, i have an interesting situation. I need a formula for the below

scenarios:


1] If i enter a letter in Cell Letter, its corresponding sequential

number should be shown in Cell# (see above table).

2] If i enter a number in Cell#, its corresponding sequential Letter

should be shown in Cell Letter (see above table).

3] If there are multiple comma-separated values, i need to handle to

take care of that too (see above table).


please note i would be using either one of the formula and not both.

but for understanding purpose, If someone can demonstrate both the

formulae (taking into consideration comma-separated multiple letters/

values), it would be a great help.


Any ideas if the same thing can be achieved in VBA?
 
I think this is much easier using VBA. Take your pick of which UDF you'd like to use. Note that the output of both of these functions is a string.


Code:
Function LetterToNumber(Letter As String) As String

i = 1

Do While i <= Len(Letter)

LetterToNumber = LetterToNumber & Asc(Mid(Letter, i, 1)) - 64 & ", "

i = i + 3

Loop

LetterToNumber = Left(LetterToNumber, Len(LetterToNumber) - 2)

End Function


Function NumberToLetter(xNumber As String) As String

i = 1

Do While i <= Len(xNumber)

NumberToLetter = NumberToLetter & Chr(Mid(xNumber, i, 1) + 64) & ", "

i = i + 3

Loop

NumberToLetter = Left(NumberToLetter, Len(NumberToLetter) - 2)

End Function
 
Hi Luke,


Could you explain me both these codes as its not commented? for e.g. why are you incrementing i = i + 3 in the Do Loop?


Thanking you in advance :)
 
Sure thing. Both codes are User defined-functions (UDF) that you would copy to a module in the VBE. Then, in your workbook, you would simply write the formula:

=LetterToNumber(A2)


How it works (letter to number):

To account for the possibility of having comma seperated values, the function looks at the 1st letter, and then every 3rd letter after that. The Asc function translates the letter into the asc code, A=65, B=66, etc. For your scneario, I just subtracted 64 to get the correct number (side note, this only works for upper case letters. Maybe insert the UCase operator to compensate, ala:

Code:
LetterToNumber = LetterToNumber & Asc(UCase(Mid(Letter, i, 1))) - 64 & ", "


The Resultant string is formed by taking that number, and then adding ", " to the end, in anticipation of another letter/number being found. At the end, I trim off the excess ", " to make it look clean.


NumberToLetter works very similar, just using the Chr function to translate numbers into letters (similar to before, adding 64 to get the correct value)


Hope that helps...any other questions?
 
Hi Luke,


Thanks for explaining in a simplistic manner :)


So, what i infer is that these functions won't work for letters like AA, AB, CD, CF etc?


And can i call these in a worksheet_Change event by passing them a cell range, so that user can type in letter/number in either column 1 (or column 2) and the corresponding column (column 1 or column 2) will show the letter/number?
 
Correct, it won't work on double letters. Any chance you can remove condition #3 from your original post? Then you could just do:

=COLUMN(INDIRECT(A2&"1"))


Yes, you can call it from the worksheet change event. Something like this maybe?


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

If Not (Intersect(Target, Range("A:A")) Is Nothing) Then

Target.Offset(0, 1) = LetterToNumber(Target.Value)

ElseIf Not (Intersect(Target, Range("B:B")) Is Nothing) Then

Target.Offset(0, -1) = NumberToLetter(Target.Value)

End If

Application.EnableEvents = True

End Sub
 
Excellent Luke, this is awesome!

Though a workaround for double-letters considering condition #3 would be better, this definitely does the works for singular letters.


Thanks and best regards. :)
 
Back
Top