Convert number to assigned names and joined with another names  [Solved/Closed]

Report
-
 Mariam -
Hello,

Please anyone can assist me on the below issue.


that if I want to have a result as per the below

if 1=Biju , 2=Daisy, 3=Mahmoud....

Cell A - If I will enter 123
cell B - the result that I am looking for is = Biju, Daisy, Mahmoud

thank you in advance

2 replies

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Mariam,

Put this custom function in a standard module (Alt+F11 > top menu of new window > insert > module):

Function NumberToName(MyNumber As Integer)
Dim x, y As Integer

x = Len(MyNumber) + 1

Do
    y = y + 1
    NtN = Mid(MyNumber, y, 1)
    Select Case NtN
        Case 1
            NumberToName = NumberToName & "Biju, "
        Case 2
            NumberToName = NumberToName & "Daisy, "
        Case 3
            NumberToName = NumberToName & "Mahmoud, "
    End Select
    
Loop Until x = y

NumberToName = Left(NumberToName, Len(NumberToName) - 2)
End Function


In Excel use:
=NumberToName(A2)

To add more names to numbers, then add to the cases in the code.

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Hi TrowaD,

Thank you so much for you supper help. Just if you can help me more since using numbers will only allow me to have 9 names. How can I add more than 9 names? as I tried to used 1.1, 1.2, 1.3 and so on but only it takes the number 1. Please help me more....
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Mariam,

Instead of using "1.1, 1.2, 1.3", use "1A,1B,1C" for the following function to work (this way you will have room for 234 names):
Function NumberToName(MyNumber As String)
Dim x, y As Integer
Dim NtN As String

x = Len(MyNumber) - 1
y = -2

Do
    y = y + 3
    NtN = Mid(MyNumber, y, 2)
    Select Case NtN
        Case "1A"
            NumberToName = NumberToName & "Biju, "
        Case "1B"
            NumberToName = NumberToName & "Daisy, "
        Case "1C"
            NumberToName = NumberToName & "Mahmoud, "
    End Select
    
Loop Until x = y

NumberToName = Left(NumberToName, Len(NumberToName) - 2)
End Function


Best regards,
Trowa
Dear Trowa,

Thank you so much for your help. it was indeed a great solution.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!