Formula

[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.

 

[Gord Dibben]

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

 

[KO]

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

 

[Gord Dibben]

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


Gord

 

[KO]

Thank you so much for your help.

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


Gord