Text to Columns

[balfour71]

Hi,

I have the following address text in a single column, which I need to strip out into 3 columns for database import. The text string example is:

12th floor TD Centre 201 Portage Avenue PO Box 6600 Winnipeg, MB R3C 3A7 Canada

I can strip out the "Canada" and the State/postcode: "R3C 3A7" by using several =right commands, but I need to separate out the City "Winnipeg" separately and then everything else to the left of the "," as a separate single column. This comma is the only indicator to break out the address data. Therefore it should look like this: (in for separate columns)

12th floor TD Centre 201 Portage Avenue PO Box 6600 | Winnipeg | MB | R3C3A7 | Canada

Hoping someone can help. Thanks.

[SamuelT]

Hi,

If the province is always Winnipeg, and the full address is in A1, then the following should give you everything before "Winnipeg":

=LEFT(A1,FIND("Winnipeg",A1,1)-1)

HTH,

SamuelT