Formula

  • Thread starter Thread starter KO
  • Start date Start date
K

KO

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.
 
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
 
In that case you can expand the event code vals and nums arrays or go with a
helper cell and a VLOOKUP table.


Gord
 
Thank you so much for your help.

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


Gord
Click to expand...
 
Back
Top