Entering a word will result in a number?

October 13, 2010 at 13:39:07
Specs: Windows Vista
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!


See More: Entering a word will result in a number?

Report •

#1
October 13, 2010 at 16:59:53
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

http://www.skeptic.com/


Report •

#2
October 14, 2010 at 09:28:43
thanks!

Report •

#3
October 14, 2010 at 11:31:28
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 Dix in 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)


Report •

Related Solutions

#4
October 14, 2010 at 11:43:34
thanks! it looks complicated. i guess i'll just abandon the idea. thanks for your time !

Report •

#5
October 14, 2010 at 12:21:34
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 here

Then in cell B13 enter the formula:
=VLOOKUP(A13,A1:B11,2,FALSE)

Then as long as you enter the complete phrase or words in cell A13 you should get the corresponding number.
But the words or phrase in A13 must be the same as in your list.
So just entering the word Fort will get you an error message, but entering fort ord should get you a match.

MIKE

http://www.skeptic.com/


Report •

#6
October 14, 2010 at 12:48:50
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

http://www.skeptic.com/


Report •

#7
October 14, 2010 at 13:42:42
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.


Report •

#8
October 14, 2010 at 13:49:57
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?

Report •

#9
October 14, 2010 at 14:32:33
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 OK

In Cell B9 enter the formula:
=VLOOKUP(A9,A2:B7,2,FALSE)

MIKE

http://www.skeptic.com/


Report •

#10
October 14, 2010 at 14:45:24
Like put the formula on sheet1 and then the list on sheet2?
If you use just the =VLOOKUP() formula without the 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

http://www.skeptic.com/


Report •

#11
October 14, 2010 at 15:40:55
thank you so much for your help !

Report •

#12
October 14, 2010 at 15:41:46
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 word phrase to return a single number.

I've edited my posts to avoid confusion should someone stumble across this thread in the future.


Report •

#13
October 14, 2010 at 16:07:39
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

http://www.skeptic.com/


Report •

#14
October 14, 2010 at 16:21:36
If I give the list a Name and then refer to the Name in the Validation, it works,
If I manually input 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

http://www.skeptic.com/


Report •

#15
October 14, 2010 at 16:51:30
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 select any 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.


Report •

#16
October 14, 2010 at 17:39:00
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

http://www.skeptic.com/


Report •

#17
October 15, 2010 at 07:31:41
my work excel is 2007. thanks!

Report •

#18
October 15, 2010 at 08:59:55
Glad we could help, even learned a bit myself on this one.

MIKE

http://www.skeptic.com/


Report •

Ask Question