Excel Formula String Creation For Multiple Uses
- or -
Post a project like this1759
£10(approx. $13)
- Posted:
- Proposals: 3
- Remote
- #2430942
- Awarded
Description
Experience Level: Entry
To create an excel formula that takes a full UK postcode, BB8 8LD for example, that strips out all the data except the important letters at the beginning that signify the city/town. In this case “BB” which happens to be “Blackburn”. However, in the database that are also postcodes that start only with “B” and then are followed by a number. This happens to signify Birmingham. But also, some postcodes will have two numbers after the letter or sometimes one number, but they are still the same city/town (see the examples below). This scenario might also apply for other postcodes in the database starting with other letters.
BB8 8LD – Blackburn postcode area
B9 4PY – Birmingham postcode area
B94 4LR – Birmingham postcode area
Objective 2
Below is an extract from the full postcode area list. Once objective 1 is completed, the formula must take the “stripped” postcode and assign the correct “Postcode Area” to the whole postcode. As you can see below, more than one postcode area has the same area number.
Postcode Town/City Postcode Area
L – Liverpool 1
WA – Warrington 1
WN – Wigan 1
BB – Blackburn 2
BL – Bolton 2
OL – Oldham 2
PR – Preston 2
FY – Blackpool 3
LA – Lancaster 3
M – Manchester 3
CH – Chester 4
CW – Crewe 4
LL – Llandudno 4
SY – Shrewsbury 4
BD – Bradford 5
HD – Huddersfield 5
HX – Halifax 5
LS – Leeds 5
Objective 3 – The final formula
The final formula is in column N and this is where the result is displayed. If the Postcode is invalid or missing, the result needs to say MISSING in text. The formula must work from column of data (from column k in this example). The formula must be robust so that it never delivers the wrong result. The formula will be copied and used on other postcode fields in other databases.
A separate excel spreadsheet is supplied that contains some sample data to work with and also the full list of postcode starting letters, postcode towns and associated postcode area.
BB8 8LD – Blackburn postcode area
B9 4PY – Birmingham postcode area
B94 4LR – Birmingham postcode area
Objective 2
Below is an extract from the full postcode area list. Once objective 1 is completed, the formula must take the “stripped” postcode and assign the correct “Postcode Area” to the whole postcode. As you can see below, more than one postcode area has the same area number.
Postcode Town/City Postcode Area
L – Liverpool 1
WA – Warrington 1
WN – Wigan 1
BB – Blackburn 2
BL – Bolton 2
OL – Oldham 2
PR – Preston 2
FY – Blackpool 3
LA – Lancaster 3
M – Manchester 3
CH – Chester 4
CW – Crewe 4
LL – Llandudno 4
SY – Shrewsbury 4
BD – Bradford 5
HD – Huddersfield 5
HX – Halifax 5
LS – Leeds 5
Objective 3 – The final formula
The final formula is in column N and this is where the result is displayed. If the Postcode is invalid or missing, the result needs to say MISSING in text. The formula must work from column of data (from column k in this example). The formula must be robust so that it never delivers the wrong result. The formula will be copied and used on other postcode fields in other databases.
A separate excel spreadsheet is supplied that contains some sample data to work with and also the full list of postcode starting letters, postcode towns and associated postcode area.
Chad M.
99% (18)Projects Completed
13
Freelancers worked with
15
Projects awarded
56%
Last project
16 Mar 2022
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Chad,
Is your fixed price flexible?Chad M.17 Jun 2019Hello. Thanks for your interest but the job has just been awarded. Best Regards. Chad.
817830
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies