number to character and vice-versa

N

noname

Hi,

I have a table consisting of 2 columns:

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


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 formulae and not both.
but for understanding purpose, If someone can demonstate both the
formulae (taking into consideration comma-separated multiple letters/
values), it would be a great help.
 
P

Pete_UK

1. With a letter in the range A to L in A1, you can use this in B1:

=CODE(UPPER(A1))-64

2. If you have a number in the range 1 to 12 in A2, put this in B2:

=CHAR(A59+64)

Hope this helps.

Pete
 
N

noname

1. With a letter in the range A to L in A1, you can use this in B1:

=CODE(UPPER(A1))-64

2. If you have a number in the range 1 to 12 in A2, put this in B2:

=CHAR(A59+64)

Hope this helps.

Pete

Hi,
Click to expand...
I have a table consisting of 2 columns:
Click to expand...
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
Click to expand...
Now, i have an interesting situation. I need a formula for the below
scenarios:
Click to expand...
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).
Click to expand...
please note i would be using either one of the formulae and not both.
but for understanding purpose, If someone can demonstate both the
formulae (taking into consideration comma-separated multiple letters/
values), it would be a great help.
Click to expand...
Click to expand...

Hi Pete,

Yes i had tried that.

But i am faced with the dilemma with the 3rd scenario. i.e. if i have
letters or values separated by "," then how do i get the letters /
values ?

e.g. If i type C, D in row 3, then its Cell# should be 3, 4.
and similarly if i take the opp scenario, i.e. if i type 3, 4 then it
should give me C, D.

Any ideas on how to handle the comma?
 
R

Ron Rosenfeld

Hi,

I have a table consisting of 2 columns:

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


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 formulae and not both.
but for understanding purpose, If someone can demonstate both the
formulae (taking into consideration comma-separated multiple letters/
values), it would be a great help.
Click to expand...

If you want to be able to go back and forth, depending on into which column you type your character, then this is easily done with an event-triggered VBA macro.

I have restricted the contents of "Letter" to be actual letters [A-Za-z] but they get converted to upper case for this exercise.
I have restricted the contents of "#" similarly.

If you want to convert a wider range, you'll need to provide some parameters for non-ambiguous translations.

I have defined the Letter and # column as being that with the appropriate label in Row 1, but you could hard code or define them differently.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

===================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rLetter As Range
Dim rNumber As Range
Dim v As Variant
Dim i As Long
Dim res As String

Application.EnableEvents = False

Set rLetter = Range("1:1").Find(what:="Letter", _
LookIn:=xlValues, lookat:=xlWhole)
Set rNumber = Range("1:1").Find(what:="#")

If Not Intersect(Target, rLetter.EntireColumn) Is Nothing Then
v = Split(UCase(Target), ",")
For i = 0 To UBound(v)
v(i) = Asc(v(i)) - 64
If v(i) < 1 Or v(i) > 26 Then v(i) = ""
Next i
If IsEmpty(v) Then v = ""
rNumber(RowIndex:=Target.Row, columnindex:=1).Value = Join(v, ",")
End If

If Not Intersect(Target, rNumber.EntireColumn) Is Nothing Then
v = Split(UCase(Target), ",")
For i = 0 To UBound(v)
If IsNumeric(v(i)) Then v(i) = Chr(v(i) + 64)
If v(i) < "A" Or v(i) > "Z" Then v(i) = ""
Next i
If IsEmpty(v) Then v = ""
rLetter(RowIndex:=Target.Row, columnindex:=1).Value = Join(v, ",")
End If

Application.EnableEvents = True

End Sub
===============================
 
N

noname

Hi,
Click to expand...
I have a table consisting of 2 columns:
Click to expand...
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
Click to expand...
Now, i have an interesting situation. I need a formula for the below
scenarios:
Click to expand...
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).
Click to expand...
please note i would be using either one of the formulae and not both.
but for understanding purpose, If someone can demonstate both the
formulae (taking into consideration comma-separated multiple letters/
values), it would be a great help.
Click to expand...

If you want to be able to go back and forth, depending on into which column you type your character, then this is easily done with an event-triggered VBA macro.

I have restricted the contents of "Letter" to be actual letters [A-Za-z]  but they get converted to upper case for this exercise.
I have restricted the contents of "#" similarly.

If you want to convert a wider range, you'll need to provide some parameters for non-ambiguous translations.

I have defined the Letter and # column as being that with the appropriatelabel in Row 1, but you could hard code or define them differently.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

===================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rLetter As Range
    Dim rNumber As Range
    Dim v As Variant
    Dim i As Long
    Dim res As String

Application.EnableEvents = False

Set rLetter = Range("1:1").Find(what:="Letter", _
    LookIn:=xlValues, lookat:=xlWhole)
Set rNumber = Range("1:1").Find(what:="#")

If Not Intersect(Target, rLetter.EntireColumn) Is Nothing Then
    v = Split(UCase(Target), ",")
    For i = 0 To UBound(v)
        v(i) = Asc(v(i)) - 64
        If v(i) < 1 Or v(i) > 26 Then v(i) = ""
    Next i
    If IsEmpty(v) Then v = ""
    rNumber(RowIndex:=Target.Row, columnindex:=1).Value = Join(v, ",")
End If

If Not Intersect(Target, rNumber.EntireColumn) Is Nothing Then
    v = Split(UCase(Target), ",")
    For i = 0 To UBound(v)
        If IsNumeric(v(i)) Then v(i) = Chr(v(i) + 64)
        If v(i) < "A" Or v(i) > "Z" Then v(i) = ""
    Next i
    If IsEmpty(v) Then v = ""
    rLetter(RowIndex:=Target.Row, columnindex:=1).Value = Join(v, ",")
End If

Application.EnableEvents = True

End Sub
===============================
Click to expand...

Thanks a lot Ron, :)

this does the work for me only if i change the lookat:xlwhole to
lookat:xlpart in Find.

Is there a workaround if the no of letters exceed 26? i.e. for double
letters viz AA, AB, CD, CF?
 
R

Ron Rosenfeld

Thanks a lot Ron, :)

this does the work for me only if i change the lookat:xlwhole to
lookat:xlpart in Find.
Click to expand...

Then "Letter" and/or "#" is not the only thing in your column header. It would be more robust to change the What: to reflect the entire contents of the cell. Or to hard-code the location.
Is there a workaround if the no of letters exceed 26? i.e. for double
letters viz AA, AB, CD, CF?
Click to expand...

Perhaps if you specify what you want for output in those instances ...
 
R

Rick Rothstein

Is there a workaround if the no of letters exceed 26? i.e.
for double letters viz AA, AB, CD, CF?
Click to expand...

The first listed item in your original post started by saying " If i enter a
letter in Cell Letter..." where the word Letter is singular... your column
header was shown as being "Cell Letter", again, singular. Are you now saying
AA, AB, CD, etc. are valid entries? If so, can we assume these are column
headers? If so, this is an important detail that should have been mentioned
initially as it opens up a new avenue for a solution. So, if we are talking
about column headers, then consider this event code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim V As Variant, R As Range, Answer As String, Parts() As String
Const LettersColumn As String = "A"
Const NumbersColumn As String = "B"
If Intersect(Target, Union(Columns(LettersColumn),
Columns(NumbersColumn))) Is Nothing Then Exit Sub
On Error GoTo BadEntry
For Each R In Target
Parts = Split(R.Value, ",")
If Target.Column = Columns(LettersColumn).Column Then
For Each V In Parts
Answer = Answer & ", " & Columns(Trim(V)).Column
Next
Application.EnableEvents = False
Cells(Target.Row, NumbersColumn).Value = Mid(Answer, 3)
Application.EnableEvents = True
Else
For Each V In Parts
Answer = Answer & ", " & Split(Columns(--Trim(V)).Address(0, 0),
":")(0)
Next
Application.EnableEvents = False
Cells(Target.Row, LettersColumn).Value = Mid(Answer, 3)
Application.EnableEvents = True
End If
Next
Exit Sub
BadEntry:
MsgBox "That entry is not valid!", vbCritical
Target.Select
Application.EnableEvents = True
End Sub

If you are not familiar with installing event code, just right-click the
name tab at the bottom of the worksheet you want to have this functionality,
select View Code from the popup menu that appears and then copy/paste the
above code into the code window that appears.

Rick Rothstein (MVP - Excel)
 
N

noname

The first listed item in your original post started by saying " If i enter a
letter in Cell Letter..." where the word Letter is singular... your column
header was shown as being "Cell Letter", again, singular. Are you now saying
AA, AB, CD, etc. are valid entries? If so, can we assume these are column
headers? If so, this is an important detail that should have been mentioned
initially as it opens up a new avenue for a solution. So, if we are talking
about column headers, then consider this event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim V As Variant, R As Range, Answer As String, Parts() As String
  Const LettersColumn As String = "A"
  Const NumbersColumn As String = "B"
  If Intersect(Target, Union(Columns(LettersColumn),
Columns(NumbersColumn))) Is Nothing Then Exit Sub
  On Error GoTo BadEntry
  For Each R In Target
    Parts = Split(R.Value, ",")
    If Target.Column = Columns(LettersColumn).Column Then
      For Each V In Parts
        Answer = Answer & ", " & Columns(Trim(V)).Column
      Next
      Application.EnableEvents = False
      Cells(Target.Row, NumbersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    Else
      For Each V In Parts
        Answer = Answer & ", " & Split(Columns(--Trim(V)).Address(0, 0),
":")(0)
      Next
      Application.EnableEvents = False
      Cells(Target.Row, LettersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    End If
  Next
  Exit Sub
BadEntry:
  MsgBox "That entry is not valid!", vbCritical
  Target.Select
  Application.EnableEvents = True
End Sub

If you are not familiar with installing event code, just right-click the
name tab at the bottom of the worksheet you want to have this functionality,
select View Code from the popup menu that appears and then copy/paste the
above code into the code window that appears.

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks both of you. i apologize i did not mention it until i received
a list y'day exceeding 26 letters. But this is brilliance :)

Thanks once again...Cheers n have a nice day!
 
N

noname

The first listed item in your original post started by saying " If i enter a
letter in Cell Letter..." where the word Letter is singular... your column
header was shown as being "Cell Letter", again, singular. Are you now saying
AA, AB, CD, etc. are valid entries? If so, can we assume these are column
headers? If so, this is an important detail that should have been mentioned
initially as it opens up a new avenue for a solution. So, if we are talking
about column headers, then consider this event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim V As Variant, R As Range, Answer As String, Parts() As String
  Const LettersColumn As String = "A"
  Const NumbersColumn As String = "B"
  If Intersect(Target, Union(Columns(LettersColumn),
Columns(NumbersColumn))) Is Nothing Then Exit Sub
  On Error GoTo BadEntry
  For Each R In Target
    Parts = Split(R.Value, ",")
    If Target.Column = Columns(LettersColumn).Column Then
      For Each V In Parts
        Answer = Answer & ", " & Columns(Trim(V)).Column
      Next
      Application.EnableEvents = False
      Cells(Target.Row, NumbersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    Else
      For Each V In Parts
        Answer = Answer & ", " & Split(Columns(--Trim(V)).Address(0, 0),
":")(0)
      Next
      Application.EnableEvents = False
      Cells(Target.Row, LettersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    End If
  Next
  Exit Sub
BadEntry:
  MsgBox "That entry is not valid!", vbCritical
  Target.Select
  Application.EnableEvents = True
End Sub

If you are not familiar with installing event code, just right-click the
name tab at the bottom of the worksheet you want to have this functionality,
select View Code from the popup menu that appears and then copy/paste the
above code into the code window that appears.

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks once more Rick,
This last code is just remarkable!!!

What do you eat, drink to become an MVP? ;)

BTW, is it possible for you to comment this code for us less fortunate
blokes?

Thanks and best regards.
 
N

noname

The first listed item in your original post started by saying " If i enter a
letter in Cell Letter..." where the word Letter is singular... your column
header was shown as being "Cell Letter", again, singular. Are you now saying
AA, AB, CD, etc. are valid entries? If so, can we assume these are column
headers? If so, this is an important detail that should have been mentioned
initially as it opens up a new avenue for a solution. So, if we are talking
about column headers, then consider this event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim V As Variant, R As Range, Answer As String, Parts() As String
  Const LettersColumn As String = "A"
  Const NumbersColumn As String = "B"
  If Intersect(Target, Union(Columns(LettersColumn),
Columns(NumbersColumn))) Is Nothing Then Exit Sub
  On Error GoTo BadEntry
  For Each R In Target
    Parts = Split(R.Value, ",")
    If Target.Column = Columns(LettersColumn).Column Then
      For Each V In Parts
        Answer = Answer & ", " & Columns(Trim(V)).Column
      Next
      Application.EnableEvents = False
      Cells(Target.Row, NumbersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    Else
      For Each V In Parts
        Answer = Answer & ", " & Split(Columns(--Trim(V)).Address(0, 0),
":")(0)
      Next
      Application.EnableEvents = False
      Cells(Target.Row, LettersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    End If
  Next
  Exit Sub
BadEntry:
  MsgBox "That entry is not valid!", vbCritical
  Target.Select
  Application.EnableEvents = True
End Sub

If you are not familiar with installing event code, just right-click the
name tab at the bottom of the worksheet you want to have this functionality,
select View Code from the popup menu that appears and then copy/paste the
above code into the code window that appears.

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks once more Rick,
This last code is just remarkable!!!

What do you eat, drink to become an MVP? ;)

BTW, is it possible for you to comment this code for us less fortunate
blokes?

Thanks and best regards.
 
N

noname

The first listed item in your original post started by saying " If i enter a
letter in Cell Letter..." where the word Letter is singular... your column
header was shown as being "Cell Letter", again, singular. Are you now saying
AA, AB, CD, etc. are valid entries? If so, can we assume these are column
headers? If so, this is an important detail that should have been mentioned
initially as it opens up a new avenue for a solution. So, if we are talking
about column headers, then consider this event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim V As Variant, R As Range, Answer As String, Parts() As String
  Const LettersColumn As String = "A"
  Const NumbersColumn As String = "B"
  If Intersect(Target, Union(Columns(LettersColumn),
Columns(NumbersColumn))) Is Nothing Then Exit Sub
  On Error GoTo BadEntry
  For Each R In Target
    Parts = Split(R.Value, ",")
    If Target.Column = Columns(LettersColumn).Column Then
      For Each V In Parts
        Answer = Answer & ", " & Columns(Trim(V)).Column
      Next
      Application.EnableEvents = False
      Cells(Target.Row, NumbersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    Else
      For Each V In Parts
        Answer = Answer & ", " & Split(Columns(--Trim(V)).Address(0, 0),
":")(0)
      Next
      Application.EnableEvents = False
      Cells(Target.Row, LettersColumn).Value = Mid(Answer, 3)
      Application.EnableEvents = True
    End If
  Next
  Exit Sub
BadEntry:
  MsgBox "That entry is not valid!", vbCritical
  Target.Select
  Application.EnableEvents = True
End Sub

If you are not familiar with installing event code, just right-click the
name tab at the bottom of the worksheet you want to have this functionality,
select View Code from the popup menu that appears and then copy/paste the
above code into the code window that appears.

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks once more Rick,
This last code is just remarkable!!!

What do you eat, drink to become an MVP? ;)

BTW, is it possible for you to comment this code for us less fortunate
blokes?

Thanks and best regards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel does not accept text format! 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Cumulative formula for word table 0
Menu Macro Not Executing - Need Help 1
Is this possible? 0
Counting one Character as a String 1
Crating User Form to Add Names 1
Custom Formating Letters to Numbers while Displaying Letters 2

Top