putting relevant info into one column (Excel )

[EmEd1984]

Hi there everyone

I've googled this to death and used the help options but still haven't found the best solution!! This might be confusing to explain so please feel free to ask me what i'm on about...

I have an Excel spreadsheet of around 1,200 different business contact details, with names, telephone numbers and address details. I need to be able to filter each column, so I want to split the address out by:
* house name
* road
* town
* county
* postcode

some of the information uses all 5 address columns, but others obviously only use 3 and some even 6, so not all the similar information is falling into one column.

as I realise that a lot of the information is down to my interpretation, i'll have to manually move a lot of it but as all the postcodes are related in the way that they all have a space between the first and second part, is there a way I can 'find' all the postcodes on the page and copy (or cut) them into the correct column?

it may be that the only solution is 'inserting' a cell and moving the postcode right (but using F4 to repeat the last action to make it quicker) which is fine, i just hoped there'd be a quicker method as I'll be doing quite a few of these...!

any suggestions?
TIA x

[eash]

I'm not too sure what you mean and maybe Ethel or Chuck would be better to advise but I was thinking a filter? Under Data and filter?

[sandmaster]

I believe you mean you have entered each piece of data in separate columns, but instead of leaving blank cells when entering addresses with just a few lines you have entered the data as you went across the columns. The result is you will have post codes in, say, columns G or H, depending on the length of the address. You now want them all in the same column.
I have experimented with Insert and it moved the post codes to the right, but when I wanted to move anything else in the same row using Insert, the post codes moved to the right again.
I'm sorry to say but I think that Copy & Paste is the only way you are going to be able to tidy the spreadsheet.

[EmEd1984]

that's pretty much the jist of it - i've copied the contacts from thomson local so the details have fallen wherever excel let them. i thought that to be the case, but perhaps there was some secret solution i was missing out on :( thanks, though.

the best thing i've found so far is going down the left of the furthest right hand column (where i want the postcodes to be) and insert > shift cells right then using F4 after that. Then when that was columns finished, cutting it and moving it away, then repeating with the next furthest right column, etc...long winded, but obviously much quicker than me retyping it. I've recently bought an Excel tutorial book that I'm sure explains it somewhere (or doesn't, if thats the case).

...the floor's still open if anyone knows a better method!

[beso]

Use concatenated IF formulae. You will need to get your head around the syntax as it grows to huge proportions but here are some examples to get you started. Excel will prompt with the syntax in the later versions or you can look it up in the help for older versions.

Simple form of IF. This will return the content of cell A1if A1 equals 3 or "5" if it doesn't.

=IF(A1=3,A1,5)

Syntax is IF(Test, Value if True, Value if False)

You can substitue a cell reference for the number being tested. eg A1=B6

---------------
Contatenated it will can test a row of cells. This formula will find the first column with 3 as its content or zero if neither is equal to three.

=IF(A1=3,IF(B2=3,B2,0))

The True output from the first IF is another IF. Extend as required. If you are testing for an alphanumeric string put the test value inside quotes.

-----------
ISNUMBER will return TRUE or FALSE depending if A1 holds a number.

=ISNUMBER(A1)

---------------
This formula (concatenated as required) will choose the first number cell from a row of cells. Would work if your postcodes were numbers only. It returns zero is all fail.

=IF(ISNUMBER(A1),(A1),IF(ISNUMBER(B1),(B1),0))

----------------
To find a mixed postcode field you need to use Left, Right or Mid statements to parse the cells. These formulae extract the designated part of the string.

Extract the first three characters from cell A1.

=Left(A1,3)

------------
Extract the last two characters from A1

=Right(A1,2)

------------
Extract three characters of cell A1 starting with the second character.

=Mid(A1,2,3)

-------------
Also useful:

Return the number of characters in the cell.

11:23 Fri 20th Feb 2009

[beso]

It didn't all post:

Also useful:

Return the number of characters in the cell.

=LEN(A1)

----------------------

Obvious from their content

=ISTEXT(A1)

=ISNONTEXT(A1)
------------------

You may also find useful stuff in the Lookup group of functions.

I am sure if you combine these formulae in a column you should be able to find the postcode field.

The formula will be hideous but plausible. Good luck.