Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

What Excel formula to use?

  • 08-12-2009 1:43pm
    #1
    Closed Accounts Posts: 200 ✭✭


    I've looked on Microsoft's website but can't find an answer, so hoping someone here can help.

    I have a spreadsheet with a list of addresses. I want to add a column with codes for the counties. So, if an address is in Co. Kerry, I want a formula that will put KY in the next column, CE for Clare, D for Dublin etc. Anyone know what formula I'd use here?


Comments

  • Registered Users, Registered Users 2 Posts: 5,150 ✭✭✭homer911


    Are the counties spelt correctly in a column/cell of their own, or part of a larger text field

    If in a field of their own, you could use vlookup to a reference table. Otherwise you will have to use some fuzzy logic


  • Registered Users, Registered Users 2 Posts: 86,729 ✭✭✭✭Overheal


    The only way I know to do that is to write a very large operator to the tune of if E3 contains "Co. Clare" then this cell = "CE", etc.

    Excel is not really built for operating on complex Strings (adresses) you should consider porting the data over to MS Access and manipulate the data from there.


  • Closed Accounts Posts: 200 ✭✭Tawfee


    Homer911, the counties are in a column of their own. I had an idea that vlookup would work, but it's been so long since I was shown how to do one that I don't remember anymore:confused:.


  • Moderators, Recreation & Hobbies Moderators, Science, Health & Environment Moderators, Technology & Internet Moderators Posts: 93,599 Mod ✭✭✭✭Capt'n Midnight


    most counties it's the first and last letter so
    =left(a1,1)&right(a1,1)
    will catch some


    vlookup is the one to use though


  • Registered Users, Registered Users 2 Posts: 5,150 ✭✭✭homer911


    Tawfee wrote: »
    Homer911, the counties are in a column of their own. I had an idea that vlookup would work, but it's been so long since I was shown how to do one that I don't remember anymore:confused:.

    The Excel Help is very useful, particularly if you hit the Fx button beside the formula space

    for a simple vlookup you create a column of source values (the county names, equal to column 1) and a column to the right of it holding the abbreviation (column 2). Its preferable to sort these in ascending order of column 1.

    Your formula will then look like =vlookup(<cell to convert>,<range of lookup values (columns 1 and 2)>,2,FALSE)

    The two is to return the values in the second column and the false to only permit an exact match. The range of lookup values should be set to absolute (Press F4 on each cell reference) so that when you copy it down to the rest of your data it will still work


  • Advertisement
  • Moderators, Science, Health & Environment Moderators Posts: 21,693 Mod ✭✭✭✭helimachoptor


    Vlookup is probably the way to go here


  • Closed Accounts Posts: 200 ✭✭Tawfee


    homer911 wrote: »
    The Excel Help is very useful, particularly if you hit the Fx button beside the formula space

    for a simple vlookup you create a column of source values (the county names, equal to column 1) and a column to the right of it holding the abbreviation (column 2). Its preferable to sort these in ascending order of column 1.

    Your formula will then look like =vlookup(<cell to convert>,<range of lookup values (columns 1 and 2)>,2,FALSE)

    The two is to return the values in the second column and the false to only permit an exact match. The range of lookup values should be set to absolute (Press F4 on each cell reference) so that when you copy it down to the rest of your data it will still work

    Thanks Homer911, i'll have a go at that today & see how it goes.


  • Closed Accounts Posts: 2,980 ✭✭✭Kevster


    Im sure that there is an easier way, but it wouldn't be too much effort to just use the nested 'if' statements which was already mentioned. It would be a long statement, but once the first one's done, you just copy and paste.


Advertisement