We’ve been working on a large project now for a good 3 or 4 months for one of our customers, and the final stage of the project is to add a load of free listings to their new directory style website.
We have the bulk data in a spreadsheet and i have spent a good few days sorting the content out so that each column contains the right data (such as ensuring the street from each entry is in the “Street” column etc.
I needed to duplicate the postcode column and remove the last part of it so that i had a “Postcode district” column so the import would place the listings in the right part of the directory (the last category level is “Postcode district”)
However, there are 1776 entries!
I spoke to an Excel specialist who solved my problems 🙂
Stage 1 – Create a new column next to the one you want to edit
Stage 2 – Insert magic formula
First you start with an = then write the word LEFT (Here is some details on the LEFT function) – open brackets, then click on the cell to the left (i.e. the first cell in the column of text you wish to trim – in this case its Y2)
Add a comer, then write 4 (as in this case we want to remove the last 3 characters from the postcode and the space, which =Â 4 characters) close brackets and press enter!
Stage 3 – Result.. Kinda!
We now find we have the desired result in place of our ninja formula
The next stage is to duplicate this formula down the column so that we have the same desired effect, we do this by clicking the cell that currently contains the shortend data, then click the square in bottom right corner of the cell and drag down till you reach the end of the column.
Once you get to the bottom, let go and POW you have edited shortened postcodes in the whole column!
Stage 4 – Plain text not formulas
Now if this was a visual requirement, it would be a done deal – however i need this spreadsheet to house all the data in plain text – no ninja formulas!
What we need to do is select from the first cell to the last cell, then right click and click Copy.
Then in the column next to it, select the same amount of rows, right click in the top one and choose “Paste Special”
Select the “Values” radio box, then click OK.
Now you can remove the column that contains the formula based data, and you are left with a text only colu full of the shortened postcode areas so we can successfully import all the data to the right areas on the new site!
Hope this helps! 😀