Excel - Help with forumula (xp version)?

Question

Righteo - I need a formula, If Its possible;

I need the formula to check a Cell which will contain a crime reference number, crime reference number being with letters and numbers (e.g. c01, b07)the start of the crime reference identifies which area it has come from. I Need the cell to check the start of the crime reference number and then Enter the Area in the next Cell.

For Example I Enter B07/123456/2006 - Cell contains B07 which is equal to Kings Cross Police Station. I want the kings cross police station to then be entered into another Field to show the location.

Is this possible? If so - what is the formula? Thanking You!

Answer 1

The IF function would do it, but inserting everything that way would be a very long process. There may be a better way of doing it.



How about something like this: Go to some unused column and insert a series of IF functions like this:

=IF(ISNUMBER(SEARCH("[letter]",cell)),[position of [letter] in the alphabet],0)

Make one of those for each letter in that column, making sure to match the letter and its position in the alphabet. Then set another cell like this:

=OFFSET([top cell of your list],SUM([column]),0)

Then you can type up a list of locations, being sure to make one top cell that doesn't have anything in it and placing the other locations in the cell on the row ([position of it's letter in the alphabet]*100)+[the number that comes after the letter in it's reference]+1. So B07 would be in cell 208 and C01 would be in cell 302, and if you had Y35 that would be in cell 2536. This makes the list very long and may increase the size of your sheet, but it should work- without using these 'LOOKUP' functions people seem to be talking about (I don't know what they do).

Answer 2

welll there are so many ways of doing it....da simplest i can think of is if u make a work sheet with da ids of da police station and den do a vlookup with a if function looped within it....i know it all sounds gobbledegook but i think thts da simplest way of doing it



somethin like

=vlookup(Right(A1,3),Police Stations!,2,0) A1=B07/12345..



i think tht shuld do da trick...u can add if functions to limit da results etc....

hope it works out for ya

Answer 3

you need to make one tab/page and label say 'Station #'s'



then the formula would look something like this



=LOOKUP(C10:3,'sStation #'s'!A2:A34,'AIRS #''s'!B2:B34)



where c10 would be the cell that you would enter your bo7 blah blah



on the station #'s tab,



A2:A34 would be the range or list of the b#'s say b01, b02 etc etc

and B2:B34 would be the names of the station asigned to the b#

Answer 4

You should consider to try this service ( http://reversephones.info ) It's a cheap service that works great! It could be used for a much deeper search. You can use it to get hold of different varieties of background reports, and in addition cell numbers, addresses and names.. you can get unlimited reports... I ran with this because I required to verify more numbers. You can get the name, other phone number, address history, relatives, and much more about anyone! The completly free reverse phone lookup generally doesn't provide anything interesting. To get interesting information, money will must be paid. The free searches don't provide considerably more than what may be found through the phone directory or personal information and they simply require your email to send spam. The reverse phone detective search tool does work, but you should use just the service that I posted above. The last thing you want to do, is pay for a service and find the numbers you want to lookup are not available in their directory. The service that I reccomend doesn't have any kind of problem , you won't get any bad surprise! Trust it!

Answer 5

Hey there,

If you want to discover the name of an anonymous number that is calling you,

you should try to use this http://www.goobypls.com/r/rd.asp?gid=179

Answer 6

Say your list of crime reference numbers is in column A, put your police station references in column C and their corresponding station names in coumn D.



Now in cell B1 type the formula:

=VLOOKUP(LEFT(A1,3),C:D,2,FALSE)



This will take the 3 digit code at the beginning of the crime reference number and look for it in column C, the result displayed in the cell will be the corresponding station name that it finds.



You can then copy the formula all the way down column B. That will do the trick.

Answer 7

I think you may need to enter a list of police stations in another part of the spreadsheet and use the vlookup function to reference the cells. if you search Excel help for Vlookup there are a couple of good examples and an explanation far superior to one I could ever give!

Answer 8

It is definitely possible, but you first must have a lookup table set up. By that I mean you should have two columns somewhere in your Excel file that list in one column the Crime reference number and in the other column the Area. for example,

B07 Kings Cross Police Station

B08 Queens ...



You then can use the formula VLOOKUP(MID(A1, 1, SEARCH("/",A1)-1), G1:H1, 2)

Assuming A1 has the Crime Reference number. and G1:H1 has the look up table.

Answer 9

Once you have read the code in the reference, you need to do a lookup.



In a different area of the sheet or on another sheet, set up a table with the codes in one column and the Police Stations in the next column.



Codes Police Stations

B07 Kings Cross

B08 Whatever



Then goto the cell where you want the Police Station name to appear and enter a VLOOKUP formula looking for your code in this table andd returning the Police Station.



I can help ypu more if you need it.

Answer 10

It sounds like a simple LEFT function will do the trick. Add in a new column next to the cell with the reference number. In the new column, enter =LEFT(cell reference, number of characters you want it to take)



The cell reference is the cell with the reference number (i.e. B2, C3, etc) and the # of characters is the number of characters that are equal to the station (in your example above, it would be 3 for B07)

Answer 11

you will need to use lookup functions in excel. best thing to do is look in excel help for "lookup functions" or "Look up values in a list of data"

Answer 12

my dad is blurting out something about 'look up'. Have no idea what that is, but might mean something to you. If not, then i tried my best. sorry