Excel 2007 : Address help

[blair9]

Hi,

Im working with 000s of cells that have addresses that include the company name, I have an adjacent cell which states the company name, is it possible to remove the company name with a formula or vba code?

Also would like to split the address to show Address one (Street number and road) and Address two (suburb) address 3 (city) and Address four (post code) Is this possible? All of the information is crammed into the one cell.

Thanks guys

[vlady]

Hello
Welcome to the Forum
U can use Text-column

[blair9]

Thanks for the welcome and response - Text-column? Please explain

[vlady]

On the main menu click DATA

find the

Text
to
Column

on the ribbon

click on it

then choose delimeted ---> follow then the wizard..

[Azam Ali]

Use substitute function

the splitting of address may also be possible. it is better to upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

[blair9]

Unfortunately their aren't commas so the delimited wont work

[blair9]

Have uploaded it with details of how it is and wanted

[Azam Ali]

Hi blair9
use
=TRIM(SUBSTITUTE(C2,A2,""))

it will remove the company name

The further breaking of address is possible if you have any logic/criteria becouse the excel functions works on some logic/criteria and without any logic/criteria it is not possible to break