What Excel formula to use?

Tawfee

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?

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

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.

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.

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

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.

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

helimachoptor

Vlookup is probably the way to go here

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.

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.