During the recent Microsoft Excel training program we delivered for the national Citizens Advice service we tailored the content to respond to relevant data issues. One of these, breaking down postcodes with Microsoft Excel to allow for better location analysis, we thought we whould share here:
UK PostCodes are a mix of letters and numbers – sometimes one letter followed by a number, sometimes one letter followed by two numbers, other times there can be two letters and one number or two letters and two numbers – always followed by a space then a number and two final letters.
Often we need to extract the “area” section from the first part of the postcode to breakdown location data for analysis.
Here are some examples:
Post Code Example 1
| Post Code | Area | Location |
| G1 0LT | G1 | 0LT |
| G2 4PP | G2 | 4PP |
| EH1 1SU | EH1 | 1SU |
| EH7 4GB | EH7 | 4GB |
| G82 4AS | G82 | 4AS |
| EH22 6BD | EH22 | 6BD |
| Formulae: | LEFT(A2,LEN(A2)-4) | RIGHT(A2,3) |
| LEN works out the number of characters in the postcode | RIGHT extracts characters from the right hand side of a cell | |
| LEN-4 removes from the length of the postcode the final 4 characters – the location code plus the space | because the last part of a postcode is always a number followed by 2 letters we can extract the 3 characters from the right of any post code | |
| LEFT extracts only the specified number of characters from the left of the postcode, ie everything before the space |
Post Code Example 2
| Post Code | Area | Location |
| G1 0LT | G1 | 0LT |
| G2 4PP | G2 | 4PP |
| EH1 1SU | EH1 | 1SU |
| EH7 4GB | EH7 | 4GB |
| G82 4AS | G82 | 4AS |
| EH22 6BD | EH22 | 6BD |
| Formulae: | LEFT(A2,FIND(” “,A2)-1) | RIGHT(A2,LEN(A2)-FIND(” “,A2)) |
| Find will find a space in a post code – a single space inside quotes “” | Find will find a space in a post code – a single space inside quotes “” | |
| FIND(” “,A2)-1 subtracts the space itself to reveal how many characters are to the left of the space | LEN(A2)-Find(” “,A2) calculates how many characters come after the space | |
| LEFT extracts only the specified number of characters from the left of the postcode, ie everything before the space | RIGHT extracts characters from the right hand side of a cell |
Explaining the Functions
For UK Postcode breakdown we can use a range of Microsoft Excel Text functions such as Right, Left, Len and Find.
The syntax for the ‘LEFT’ and ‘RIGHT’ functions are exactly the same, the difference being the direction they start from to retrieve data from a cell. LEN calculates the length of a text string while FIND will return the position of specific characters in a string.
The ‘LEFT’ function
LEFT(text, [num_chars])
There are two parameters within the function: text and num_chars.
- The text parameter simply refers to the actual string up for manipulation, most commonly just the cell reference that contains the string.
- The num_chars parameter refers to the number of characters from the left side of the string you want to extract.
For example, if you selected “2” for num_chars and your string was the word “excel”, your returned value would be “ex”.
The ‘Right” function
RIGHT(text, [num_chars])
The same syntax applies when using the ‘RIGHT’ function, however, we are now referring to a substring relative to the right-most section of the existing string.
Referring to the example above using the word “excel” as our existing string, if you selected “2” as num_chars in your ‘RIGHT’ function, the resulting substring would be “el”.
Using LEFT and RIGHT in Microsoft Excel
In the illustration below, we can see the results of using the ‘LEFT’ and ‘RIGHT’ functions in a worksheet.
| A | B | C | |
| 1 | String of Text | Sub-String | Formulae |
| 2 | excel | ex | =LEFT(A2,2) |
| 3 | excel | el | =RIGHT(A3,2) |
The first parameter is the text string you want to parse out your substring.
In this case for row 2 in the example, we are looking for the first 2 characters of the original string which is in cell A2.
Therefore, the first parameter (text) is “A2”.
The next parameter, num_chars, is the number of characters from the leftmost character in the string we would like to extract.
In this case, we want the first 2 characters of the original string so or formula will be “=LEFT(A2,2)” which results in “ex”.
The ‘RIGHT’ function works exactly the same as ‘LEFT’ syntax except now we are selecting the number of characters from the end of the string we want to extract .
The formula “=RIGHT(A3,2)” returns the substring “el” since it is the last two characters of the string “excel” found in cell A3.
The “LEN” fuinction
Basic Description
The Excel LEN function returns the length of a supplied text string.
Where the text argument is the text string that you want to find the length of. This can be supplied to the Len function in any of the following ways:
- directly;
- as a text string returned from another formula;
- as a reference to a cell containing a text string.
A typical use of the LEN function would be to calculate how many characters are in a piece text then use that information to figure out how much text you want to return. For example, say you had text on the worksheet that varied in number of characters but you know you always wanted to knock the last 2 characters off the text because you did not need them. Then you could use the LEN function to calculate the total number of characters and subtract 2 from that number and use it as the second argument to the LEFT function.
Syntax
LEN(text)
The LEN() function syntax has the following arguments:
- Text Required. The text whose length you want to find. Spaces count as characters.
Example: LEN
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
| A | B | C | |
| 1 | Data | ||
| 2 | Edinburgh, Scotland | ||
| 3 | One | ||
| 4 | Formula | Description | Result |
| 5 | =LEN(A2) | Length of the first string | 19 |
| 6 | =LEN(A3) | Length of the second string, which includes spaces on either side (5 before & 3 after) | 11 |
The “FIND” function
Description
The Microsoft Excel FIND function returns the location of a substring in a string.
The search is case-sensitive.
Syntax
The syntax for the FIND function in Microsoft Excel is:
FIND( substring, string, [start_position] )
Parameters or Arguments
substring – The substring that you want to find.
string – The string to search within.
start_position – Optional. It is the position in string where the search will start. The first position is 1.
| A | B | C | |
| 1 | Data | ||
| 2 | Miriam McGovern | ||
| 3 | Formula | Description | Result |
| 4 | =FIND(“M”,A2) | Position of the first “M” in cell A2 | 1 |
| 5 | =FIND(“a”,A2) | Position of the first “a” in cell A2 | 5 |
| 6 | =FIND(“M”,A2,3) | Position of the next “M” in cell A2, starting with the third character | 8 |