Search code examples
exceldata-extraction

Pull An Address Apart From Single Cell In Excel

I have 2002 addresses which have all been compiled into a single cell during the download process from my server; in most cases, the hash (#) symbol is used to separate fields (such as Line 1, Line 2, City, Postcode).

I have spent a lot of time trying combinations of LEFT, MID and other functions, but to no avail; the problem is that as there are so many addresses, and not all of them have the same number of characters for each field (such as Postcode - some will have 6 characters (including blank space), where some others will have five or more/fewer), there doesn't appear to be a one-size-fits-all solution that I can enter once and then use Excel's auto-fill handle/feature to complete the process for all records.

Here is a sample of my data (which has been anonymised):

44A THE ADDRESS#EALING#LONDON#W1 1WW#
541 PARSON PLACE#HENDON#LONDON#NW4 4WN#
SOMEBODY PRACTICE CHALKHILL PCC THE WELFORD CTR#11B CHALKHILL AVENUE#WIMBLEDONE MIDDX#HH9 9HH#
THE SEBELMONT MEDICAL CLINIC 18 EASTERN ROAD#SOUTHALL#MIDDLESEX#UN1 1NU#
130 FINGOVER COURT#REDBUS STREET#CAMBERWELL#SE5 5ES#
KING'S ELBOW MEDICAL CENTRE 17F STAGLAND LANE#KINGSBURY#MIDDX#NW9 9WN#
10 LADYFOOT ROAD RUISLIP#MIDDLESEX#HA4 4AH#

I want to be able to extract everything between the hash symbols (excluding/omitting the hash symbols themselves) and I am dedicating four columns to store this data: Address Line 1, AL2, AL3, Postcode.

Going by the first example (44A THE ADDRESS#EALING#LONDON#W1 1WW#) which resides in a single cell, I hope to achieve something like the following outcome:

AL1                     AL2            AL3        POSTCODE
44A THE ADDRESS         EALING         LONDON     W1 1WW

It doesn't matter if some of the address sections appear under the wrong column - I can very easily rectify this and can even add another column; I simply want to be able to extract the data from the single cell.

Solution

  • If you import the data as a text file, you can normally select the delimiter.

    File->open

<select the file from the dialogue box>

    Dialogue for importing a file

    This dialogue box should appear, after clicking next, it will appear as above, at which point, you can select a hash as a delimiter- instant self data sorting!