# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Is it possible to give cell text a numeric value?

## billybob013

Hello all,

Is it possible for text in a given cell to reference a number in order for it to be sorted by the referenced numeric value? 

For example, if I were to sort: Freshman, Sophomore, Junior, Senior strictly by text, I would receive the following.

Freshman
Junior
Senior
Sophomore

Is there anyway I can create each instance as a numeric value? So that Freshman = 1, Sophomore =2, Junior = 3, Senior =4. I could then sort by from smallest to largest and receive the results I am looking for.

Thanks,

Billybob

----------


## Portuga

Considering you have those values in a1 In B1 put the following formula and drag down:

=if(A1="Freshman";1;if(A1="Sophomore";2;if(A1="Junior";3;if(A1="Senior";4;""))))

----------


## billybob013

thanks for your response, but i don't exactly want to create another row of data. i want to be able to create a table and search from smallest to largest. I'm just wondering if you can assign numeric values to specific text values?

thanks

----------


## rylo

Hi

Try this.

Tools, options, custom lists, select new list in custom lists, and in your list entries type
Freshman, Sophomore, Junior, Senior
then Add, OK.

Select your data and go Data, Sort, Options, select your new list from the First key sort order dropdown, Ok, OK

and you should be sorted according to your new requirement.

HTH

rylo

----------


## billybob013

thanks for the help rylo,

i'm actually using excel 2007 and lists do not exist anymore, but are incorporated within tables. i was actually able to create a custom list through the following steps:

1) i clicked on the drop-down menu on my header named "year"
2)i then positioned my mouse over "sort by color" and then selected "custom sort"
3)from there i could add a sort level and create a custom list within the drop-down menu "order"


thanks for all the help!

Billybob

----------


## Claymation

Another option that you can probably used (assuming that I understand your post correctly) would be to use the LOOKUP function and have a numerical value returned based on the text in a cell.

In this example you type in Freshman, Sophomore, Junior, or Senior in A1, and it will return 1,2,3,4 respectively.

=LOOKUP(A1,{"Freshman","Sophomore","Junior","Senior"},{"1","2","3","4"})

Hope that helps.

----------


## natezivin

I have a simmelar question relating to the previous one. 

What if I have the word candle in A1, and i want the number 5.50 to come up in B1, But I want it to be when ever I type in the word candle into any row in the A column, I want the number 5.50 to show up next to it in the B column?

Plese Help!!!!

Thanks.

----------


## vlady

you'll have your answer if you start/create your own thread.. :Smilie:

----------


## natezivin

how do i do that?

----------


## vlady

click you back button once..
then bellow the screen you'll find 
"Post New Thread"

and btw, welcome to the forum.. :Smilie:

----------


## nmhung49

> how do i do that?



I think you should use Vlookup in this case, Can you Post file?

----------


## arindam2013

Hi billybob013,

You can achieve it by doing custom format as shown below. There is a drawback to this - You can only do it for upto two values.
[=1]"Freshman";[=2]"Junior"

Hope this helps you.  :Roll Eyes (Sarcastic):

----------


## am37

But what about if you want to do it for 5 different values?  I am trying to do this for a survey where Very Satisfied = 5, Satisfied = 4, Neutral = 3, Dissatisfied = 2, and Very Dissatisfied = 1.

How do I do this?

----------

