Excel Help!

[Nottingham Fox]

Dear Excel experts .....

I would like to know if something is possible.

I have a spreadsheet which contains approx 316 addresses. It's made up of 5 columns (Region, Store No, Surname, First Name & Address). The address column contains the whole address like this: 129 Unthank Road, Norwich, Norfolk, NR2 2PE

In order for me to import the spreadsheet in to a piece of software the address needs to be in separate fields. I.e Column 1: 129 Unthank Road / Column 2: Norwich / Column 3: Norfolk / Column 4: NR2 2PE

Is there a formula I can tell excel to use which will help achieve what I am trying to do?

Cheers

 

[Matt_B]

Dear Excel experts .....

I would like to know if something is possible.

I have a spreadsheet which contains approx 316 addresses. It's made up of 5 columns (Region, Store No, Surname, First Name & Address). The address column contains the whole address like this: 129 Unthank Road, Norwich, Norfolk, NR2 2PE

In order for me to import the spreadsheet in to a piece of software the address needs to be in separate fields. I.e Column 1: 129 Unthank Road / Column 2: Norwich / Column 3: Norfolk / Column 4: NR2 2PE

Is there a formula I can tell excel to use which will help achieve what I am trying to do?

Cheers

Are they all separated correctly by commas? If they are, you could copy and paste that column out into notepad and save it as a CSV file (so long as they are on a new line each). You could then open that in excel and then copy it back to your original sheet.

 

[Jeff]

Dear Excel experts .....

I would like to know if something is possible.

I have a spreadsheet which contains approx 316 addresses. It's made up of 5 columns (Region, Store No, Surname, First Name & Address). The address column contains the whole address like this: 129 Unthank Road, Norwich, Norfolk, NR2 2PE

In order for me to import the spreadsheet in to a piece of software the address needs to be in separate fields. I.e Column 1: 129 Unthank Road / Column 2: Norwich / Column 3: Norfolk / Column 4: NR2 2PE

Is there a formula I can tell excel to use which will help achieve what I am trying to do?

Cheers

What I would do is use the text to columns function. If all the address fields are separated by commas, and have the same number of lines in each, it's dead easy.
I suspect some addresses will have more fields than others though, so it will probably need some editing after the text to columns conversion.

 

[Jeff]

Are they all separated correctly by commas? If they are, you could copy and paste that column out into notepad and save it as a CSV file (so long as they are on a new line each). You could then open that in excel and then copy it back to your original sheet.

The text to columns function does that without the need to mess about with a separate file.

 

[Nottingham Fox]

Matt, Jeff .... thanks guys

 

[Matt_B]

The text to columns function does that without the need to mess about with a separate file.

Never used it myself, I don't think. Will bear it in mind though.