Report

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

Ask a question Mariam - Last answered on Feb 1, 2017 at 06:56 AM by 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
See more 
Helpful
+0
plus moins
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
Mariam- Jan 30, 2017 at 03:32 PM
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....
Reply
Leave a comment
Helpful
+0
plus moins
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
Mariam- Feb 1, 2017 at 06:56 AM
Dear Trowa,

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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!