Formula
 

Data I receive shows initials of persons. I need those initials to change to
a number when I type in the initials. For instance I have the initials MJ
occur over and over and when I type those initials into a cell, I need the
cell to say 01, the initials HS need to say 02, the initials SE need to say
03, etc.

Any help is appreciated! Thank you.

Formula
 

How far does "etc." go?

For a few you could use event code which would change the values when you enter
them.

For many you would be better off using a helper cell with a lookup table.

Event code similar to this. Edit vals array, nums array and "A1:A100" to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
With rr
.Value = ival
.NumberFormat = "00"
End With
End If
Next
End Sub

This is sheet event code. Copy then right-click on sheet tab and "View Code".
Paste into that sheet module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote:

Data I receive shows initials of persons. I need those initials to change to
a number when I type in the initials. For instance I have the initials MJ
occur over and over and when I type those initials into a cell, I need the
cell to say 01, the initials HS need to say 02, the initials SE need to say
03, etc.

Any help is appreciated! Thank you.

Formula
 

I have 25 different persons with initials that need to change to number.
Thank you.

"Gord Dibben" wrote:

How far does "etc." go?

For a few you could use event code which would change the values when you enter
them.

For many you would be better off using a helper cell with a lookup table.

Event code similar to this. Edit vals array, nums array and "A1:A100" to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
With rr
.Value = ival
.NumberFormat = "00"
End With
End If
Next
End Sub

This is sheet event code. Copy then right-click on sheet tab and "View Code".
Paste into that sheet module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote:

Data I receive shows initials of persons. I need those initials to change to
a number when I type in the initials. For instance I have the initials MJ
occur over and over and when I type those initials into a cell, I need the
cell to say 01, the initials HS need to say 02, the initials SE need to say
03, etc.

Any help is appreciated! Thank you.

Formula
 

In that case you can expand the event code vals and nums arrays or go with a
helper cell and a VLOOKUP table.


Gord

On Wed, 4 Jun 2008 05:37:02 -0700, KO wrote:

I have 25 different persons with initials that need to change to number.
Thank you.

"Gord Dibben" wrote:

How far does "etc." go?

For a few you could use event code which would change the values when you enter
them.

For many you would be better off using a helper cell with a lookup table.

Event code similar to this. Edit vals array, nums array and "A1:A100" to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
With rr
.Value = ival
.NumberFormat = "00"
End With
End If
Next
End Sub

This is sheet event code. Copy then right-click on sheet tab and "View Code".
Paste into that sheet module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote:

Data I receive shows initials of persons. I need those initials to change to
a number when I type in the initials. For instance I have the initials MJ
occur over and over and when I type those initials into a cell, I need the
cell to say 01, the initials HS need to say 02, the initials SE need to say
03, etc.

Any help is appreciated! Thank you.

Formula
 

Thank you so much for your help.

"Gord Dibben" wrote:

In that case you can expand the event code vals and nums arrays or go with a
helper cell and a VLOOKUP table.


Gord

On Wed, 4 Jun 2008 05:37:02 -0700, KO wrote:

I have 25 different persons with initials that need to change to number.
Thank you.

"Gord Dibben" wrote:

How far does "etc." go?

For a few you could use event code which would change the values when you enter
them.

For many you would be better off using a helper cell with a lookup table.

Event code similar to this. Edit vals array, nums array and "A1:A100" to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
With rr
.Value = ival
.NumberFormat = "00"
End With
End If
Next
End Sub

This is sheet event code. Copy then right-click on sheet tab and "View Code".
Paste into that sheet module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote:

Data I receive shows initials of persons. I need those initials to change to
a number when I type in the initials. For instance I have the initials MJ
occur over and over and when I type those initials into a cell, I need the
cell to say 01, the initials HS need to say 02, the initials SE need to say
03, etc.

Any help is appreciated! Thank you.