I have a list of text and Numbers for eg:

1,2,3, A, B,C

and I called it into a cell (A1) using Data validation>List.

Problem is that the value called in A1 auto formats to 'text' but I want to keep Numbers as Number and text as text only since I have Vlookup linked to A1,if A1 is Number it Vlookups one table and for text it Vlookups another table)

can any one help me to keep the orignal format of the input from the list.

regards

• Moved by Lanlan HuangMicrosoft contingent staff Friday, June 12, 2015 2:26 AM

Be sure to format the cell to General.  If we then apply a list as DV:

Sub InternalString()
    Range("B2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="alpha,beta,gamma,delta,1,2,3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub