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
|