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

Mariam - Jan 30, 2017 at 05:04 AM - Latest reply:  Mariam
- Feb 1, 2017 at 06:56 AM
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
See more 

4 replies

TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Updated by TrowaD on 30/01/17 at 12:15 PM
0
Thank you
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....
TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Jan 31, 2017 at 11:21 AM
0
Thank you
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.