Words to Number

%3CLINGO-SUB%20id%3D%22lingo-sub-2839067%22%20slang%3D%22en-US%22%3EWords%20to%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2839067%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20pretty%20inexperienced%20with%20Excel%20but%20what%20I%20need%20help%20with%20is%20I%20want%20to%20have%20users%20type%20in%20a%20cell%20and%20sort%20of%20like%20a%20dropdown%20list%20via%20Data%20Validation%20have%20that%20word%20change%20over%20to%20a%20number%20that's%20already%20set.%20For%20example%20they%20type%20in%20Buena%20Vista%20under%20Exception%20Department%20I%20want%20it%20to%20relate%20back%20to%20numbers.%20(Buena%20Vista%20%3D%20101555)%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%20Any%20help%20would%20be%20appreciated%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20on%20a%20Windows%20OS%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2839067%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2848016%22%20slang%3D%22en-US%22%3ERe%3A%20Words%20to%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2848016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1183475%22%20target%3D%22_blank%22%3E%40hsvalero123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20the%20same%20cell%3F%20Even%20if%20it%20were%20possible%20(which%20I%20doubt%2C%20outside%20of%20a%20VBA%20routine)%20it's%20a%20bad%20idea.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20a%20potential%20for%20bad%20data%20with%20the%20scenario%20you%20describe.%20How%20would%20you%20know%20%26nbsp%3Bif%20101555%20was%20there%20because%20they%20wanted%20to%20pick%20%3CEM%3EBuena%20Vista%3C%2FEM%3E%20vs%20their%20wanting%20to%20pick%20%3CEM%3EHastaLaVista%3C%2FEM%3E%20but%20making%20a%20mistake%3F%3C%2FP%3E%3CP%3EWhatever%20they%20typed%20is%20no%20longer%20there%20for%20validation%20or%20correction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'd%20be%20far%20better%20served%20by%20using%20an%20adjacent%20column%20for%20that%20drop%20down%2Fdata%20validation%20entry%2C%20with%20a%20table%20lookup%20to%20provide%20the%20number.%20%26nbsp%3BThat%20way%20you%20can%20always%20go.%20back%20(or%20the%20person%20making%20the%20entry%20can%20go%20back)%20and%20see%20that%20what%20you've%20got%20is%20what%20you%20wanted%20to%20get.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I am pretty inexperienced with Excel but what I need help with is I want to have users type in a cell and sort of like a dropdown list via Data Validation have that word change over to a number that's already set. For example they type in Buena Vista under Exception Department I want it to relate back to numbers. (Buena Vista = 101555)

Is this possible? Any help would be appreciated thanks!

 

I'm on a Windows OS

1 Reply

@hsvalero123 

Why the same cell? Even if it were possible (which I doubt, outside of a VBA routine) it's a bad idea.

 

There's a potential for bad data with the scenario you describe. How would you know  if 101555 was there because they wanted to pick Buena Vista vs their wanting to pick HastaLaVista but making a mistake?

Whatever they typed is no longer there for validation or correction.

 

You'd be far better served by using an adjacent column for that drop down/data validation entry, with a table lookup to provide the number.  That way you can always go. back (or the person making the entry can go back) and see that what you've got is what you wanted to get.