Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for case specific data
Hi,
I've been given aan Excel spreadsheet to "sort out"! In simple terms it's just a series of names, addresses & postcodes... HOWEVER, rather than use one column for address line #1, a second for Address line #2, & others for Post Town, County & Post Code, they've been mixed up! So, what I'd like to do is find a nice simple way of working through these 2000+ records & pull out the relevant data. As a start, I want to pull the Postcodes into a single column & I had originally thought of looking for a part of a text string of 3 characters using LEFT & Spaces, however, some postcodes are 4 characters for the first part & some counties have 4 characters followed by a space (e.g. West Yorkshire). So, I then thought... "ok... let's look for words that start with a capital letter followed by lower case" This will give me place names, rather than post codes, but it will help point the way... and that's where I came unstuck!! I can use PROPER, UPPER & LOWER to convert one to the other, but is there a way of searching for these? so that I can pull out all of the "non-postcode" lines? Alternatively, is there a different approach that could be suggested? Regards Colin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for case specific data
Hi, Colin-
Greetings from Virginia, USA, near Washington DC. This is always a tricky task, because finding a reliable pattern in the data is difficult, as you're finding out. When you isolate the post code out of the mix, you may find another pattern from there. Do you mind posting some examples? If the post code always appears at the end of the address, you might use and IF statement with a MID that looks for a space 4 or 5 characters from the end, and isolate the post code that way. If you post examples, for privacy reasons I suggest substituting fake data. Another technique that you may be able to use: you can use ASCII character codes to look for upper and lower cases. For instance, you can find the lower case "L" in "Colin" with this formula: =SEARCH(CHAR(108),"Colin",1) Character 108 is the ASCII representation of lower case L. One place to find ASCII codes is http://www.neurophys.wisc.edu/www/comp/docs/ascii.html Dave O |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for case specific data
Hi Dave,
Greetings back from Hyde, England :-) Part of the problem is that, for example, they've sometimes used Column F for Post Code (or ZipCode) & sometimes used it for the Post Town or County! Here's some examples... E F G 2 North Yorkshire HG4 1HJ 3 Rotherham South Yorkshire S63 7QQ 4 Beverley North Humberside HU17 8AZ 5 Sheffield South Yorkshire S10 2LN 6 South Yorkshire DN1 2DZ So ideally, I want the "North" & "South" Yorkshires in one column, Rotherham, Beverley & Sheffield in another & Postcodes in a third. But how to do this programatically is, I believe, a bit of a nightmare!! Regards Colin "Dave O" wrote: Hi, Colin- Greetings from Virginia, USA, near Washington DC. This is always a tricky task, because finding a reliable pattern in the data is difficult, as you're finding out. When you isolate the post code out of the mix, you may find another pattern from there. Do you mind posting some examples? If the post code always appears at the end of the address, you might use and IF statement with a MID that looks for a space 4 or 5 characters from the end, and isolate the post code that way. If you post examples, for privacy reasons I suggest substituting fake data. Another technique that you may be able to use: you can use ASCII character codes to look for upper and lower cases. For instance, you can find the lower case "L" in "Colin" with this formula: =SEARCH(CHAR(108),"Colin",1) Character 108 is the ASCII representation of lower case L. One place to find ASCII codes is http://www.neurophys.wisc.edu/www/comp/docs/ascii.html Dave O |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for case specific data
In trying to find a pattern in your example, it looks like column G
always contains the post code, although it may be blank, and column F should contain the county, although it might show the post code. Column E contains the city name. I have to display some of my American ignorance he is a post code always either 7 or 8 characters long? I see two examples: 3 or 4 alphanumeric characters, a space, then 3 alphanumeric chars. If that is universally true, you can use those to your advantage. I'll see if I can work up a solution- I know you're getting ready to leave for the day. Dave O |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching for case specific data
More info: I searched Royal Mail's website for information about the
post code, and learned its format is 2 or 3 or 4 alphanumeric characters, a space, and 3 alphanumeric characters. Then I searched a list of counties in England, Scotland, and Wales for a " " (space) in column n-4 (n minus 4) of each county name where n is the length of the name: there are no counties in the list like that. So in your example data we can search column F, which contains either the post code or the county, for a space in column n-4. I added two columns, H and I, to contain the county and postcode. I used this formula in H: =IF(MID(RIGHT(F2,4),1,1)=" ","",F2) ....and this in column I: =IF(MID(RIGHT(F2,4),1,1)=" ",F2,G2) The formula in H reviews the entry in column F to see if the text string contains a space in column n-4. If it does, column H is blank; if it does not, the formula pulls the county name from F. The formula in I acts similarly: if F contains a space in column n-4, the formula pulls the postcode from F; if the space is not in n-4, the formula pulls the postcode from column G. Does this work for the rest of your data? Dave O |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do formulas have to be case sensative when searching words | Excel Discussion (Misc queries) | |||
Searching two columns against a specific criteria | Excel Worksheet Functions | |||
Searching for the presence of specific words | Excel Worksheet Functions | |||
searching for specific text | Excel Discussion (Misc queries) | |||
searching for specific criteria and then doing an action based on | Excel Discussion (Misc queries) |