For a VBA solution, maybe this. Adjust depending on how/where your company
ID data is stored. This assumes the structure as you have shown:

Public Function GetCompanyID(WhichCompany As String) As String
Dim CompIDData As Variant
Dim IDNum As Long

CompIDData = Range("rngCompIDs").Value

For IDNum = LBound(CompIDData, 1) To UBound(CompIDData, 1)
If (UCase(Left(WhichCompany, Len(CompIDData(IDNum, 2)))) =
CompIDData(IDNum, 2)) And (UCase(Left(WhichCompany, Len(CompIDData(IDNum,
3)))) <= CompIDData(IDNum, 3)) Then
GetCompanyID = CompIDData(IDNum, 1)
Exit Function
End If
Next

GetCompanyID = "#N/A"

End Function

However, you could use worksheets functions instead

NickHK

"jamesea" wrote in message
...
I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company
"care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z