Is there a way to input words in excel and it'll result in a number?

For example: If I type "dog" in A1, a number will appear in B2.

Also, if that's possible, how would I enter multiple entries.I.e. Enter in A1: "Fort Dix

Results in B1: 2,368

THanks!

It could be done with a list of words in one column and their corresponding numbers in another column, then use either a =VLOOKUP() or =HLOOKUP(). MIKE

thanks!

Please disregard this suggestion. I misunderstood the OP's requirements.

~~The "multiple entries" part could be problematic.~~

~~VLOOKUP can't easily be used for more than one word unless the complete phrase is in the~~lookup_array, so you would need to concatenate more than one VLOOKUP - one for each word in the phrase. In addition, you'd have to tell the formula where each word starts.

~~e.g. for~~Fort Dixin A1 and your list of words in A2:B3, the formula to return 1, 2 would look like the one I suggest below:~~A B 1 Fort Dix 2 Fort 1 3 Dix 2~~

~~=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),A2:B3,2,0) & ", " &~~

VLOOKUP(RIGHT(A1,LEN(A1)-FIND(" ",A1)),A2:B3,2,0)

~~Note: That will work for any phrase with 2 words because we can use the single space as a delimiter. If you want to return numbers for 3 words, it gets far more complicated because of the 2 spaces. I'm not even going to try a 4 word phrase.~~

~~The formula below will return 1, 2, 6 from this table:~~~~A B 1 Fort Dix PX 2 Fort 1 3 Dix 2 4 Tall 3 5 Tom 4 6 Is 5 7 PX 6~~

~~=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),A2:B7,2,0) & ", " &~~

VLOOKUP(MID(A1,FIND(" ",A1)+1,(FIND(" ",A1,FIND(" ",A1)+1))- (FIND(" ",A1)+1)),A2:B7,2,0)& ", " & VLOOKUP(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)),A2:B7,2,0)

thanks! it looks complicated. i guess i'll just abandon the idea. thanks for your time !

What is it your trying to accomplish? If you have a list of words or phrases in column A and numbers in column B, like this:

A B 1) Fort Dix 1 2) Dog 2 3) Cat 3 4) Mess Hall 4 5) Fort Ord 5 6) Fort Carson 6 7) Officers Club 7 8) NCO Club 8 9) Private 9 10) Sergeant 10 11) Captain 11 12) 13) Nco club 8 <<Enter formula hereThen in cell B13 enter the formula:

=VLOOKUP(A13,A1:B11,2,FALSE)Then as long as you enter the

completephrase or words in cell A13 you should get the corresponding number.

But the words or phrase in A13mustbe the same as in your list.

So just entering the wordFortwill get you an error message, but enteringfort ordshould get you a match.MIKE

Just a note: If you intent to use more than one word in your string,

then be aware that a space character is part of the string just like a letter.So if you enter the string:

Nco<space>club

you should get a match, but if you enter

<space>nco<space>club

or

nco<space><space>club

or

nco<space>club<space>you will get an error message.

Space characters count just like regular alphanumeric characters.

That is one of the reasons it is sometimes problematic to use more than one word.

MIKE

Here, we have a list of places with their corresponding miles.

I wanted to make it easier so that when we type a place, it'll return the miles.

Like let's say I want to find out how far away Nellis AFB from here.

So I'd type Nellis and somewhere or on the same cell, it'll give me the total miles.I know I'll have to input the numbers and all and I don't have a problem with that. I guess the other way I could do is just type out all the places with their corresponding distance and just use the search button or something.

I'm sorry if it's confusing.

I don't know if this is even doable in excel.

mmcconaghy this is what I wanted!! thanks!! but is it possible to list these in another spreadsheet and put the formula on another one?

Like put the formula on sheet1 and then the list on sheet2?

You could use data validation with the vlookup, which will force users to

select only from your list of locations.

Your data looks like this:A B 1) Location Distance 2) Ramstein AFB 6601 3) Thule AFB 4311 4) Nellis AFB 3342 5) Fort Ord 3906 6) Fort Carson 2397.8 7) King's Bay Submarine Base 991.1 8) 9) <Validation List Here> <VLookup Here>I'm using Excel 2007, if your using 2003 or 2000, yours direction will differ slightly.

In Cell A9,

on the Ribbon

select DATA

select Data Validation

on the drop down menu, select Data Validation

Select the Settings Tab

In the Allow box, select List

In the Source box, select your Location Data List,

which should be cells A2 through A7.

Click OKIn Cell B9 enter the formula:

=VLOOKUP(A9,A2:B7,2,FALSE)MIKE

Like put the formula on sheet1 and then the list on sheet2?

If you use just the =VLOOKUP() formulawithoutthe data validation,

then yes, you can have your list on sheet 2 and your formula on sheet1.If you use Data Validation, then no you can not.

Both list & formula must be on the same sheet, which simply means you have to hide the list somewhere like in Columns XY & XZ, or put the list in columns C & D and then Hide the columns.

MIKE

thank you so much for your help !

re: Like put the formula on sheet1 and then the list on sheet2?re:

If you use Data Validation, then no you can not.Why not? Maybe I'm missing something in your post, but I just did this:

In Sheet1!A1 I created a Drop Down whose source is =Sheet2!$A$2:$A$7

In Sheet2!B1 I entered this formula: =VLOOKUP(A1,Sheet2!A2:B7,2,0)Is there a reason you say that that can't be done, or is that not what you meant?

P.S. It looks like I misunderstood the OP's original post. I thought he was looking for 2 words to return 2 numbers, not a 2 wordphraseto return a single number.I've edited my posts to avoid confusion should someone stumble across this thread in the future.

Why not? Maybe I'm missing something in your post, but I just did this:I thought I could to, but when I tried, it would not let me select another sheet for my validation list, it would only let me select the list if it was on the current sheet.

MIKE

If I give the list a Name and then refer to the Name in the Validation, it works,

If Imanuallyinput the Source in the Validation, it works

but if I try to navigate to sheet 3, while using the Source Box in Validation it beeps and won't allow it.MIKE

As far as I can tell... 2003 requires a Named range if you want to use another sheet. You can't manually enter the sheet and range.

I can't test 2007, but it sounds like it accepts Named ranges and manual input.

2010 let's you use a Named range, manually enter a sheet and range or

selectany sheet and range.Kind of like they snuck up on giving you all three options. ;-)

Bottom line: In all versions the OP can put his table out of sight, on another sheet.

P.S. I shy away from suggesting that a table be hidden in a far off location on the same sheet, such as XY, because it screws up the scroll bar.

If you have relatively small range that you need to scroll through on a regular basis, and then add some data way off to the right, you end up with a short scroll bar which makes navigating around in the "main area" a bit cumbersome.

Unfortunately we don't know which version the OP is using, so a revision of my answer:

Like put the formula on sheet1 and then the list on sheet2?Yes you can, but how you do it is going to depend on which version of Excel your using.

MIKE

my work excel is 2007. thanks!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History