separate string

[Rick]

I have a column of characcters which are part of an address. The format is
"General" and it looks as follows. "Calgary, T2E 6P2". Can I build a formula
that will separate the city from the postal code and put everything into 2
new columns. (The "T2E 6P2" is the postal code).

 

[FSt1]

hi
this might work for you...
for calgary...
=LEFT(B7,FIND(",",B7,1)-1)
for the postal code
=RIGHT(B7,FIND(",",B7,1)-1)

adjust the cell refererce as needed.
the formula assume that the city and postal code are seperated by a comma.
if that is not true each time then adjustment may have to be made.

Regards
FSt1

 

[Rick]

WOW oh WOW; THANKS COOL. I have 1300 rows to treat this way and it works.
Thank you.

 

[FSt1]

thanks for the feedback
Regards
FSt1

WOW oh WOW; THANKS COOL. I have 1300 rows to treat this way and it works.
Thank you.

 

[Rick]

oops spoke too soon. Some times it works great. The "LEFT" formula works fine.
Looks like the "RIGHT" (depending on the length of the city name) either
carries some of the city name plus the coma with the postal code into the new
cell, or leaves some of the postal code out?

 

[Fred Smith]

As a postal code is always 7 characters, you can use:
=right(b7,7)

If you want Rick's formula fixed, it would look like this:
=RIGHT(B7,LEN(B7)-FIND(",",B7,1)-1)

Regards,
Fred.

 

[Rick]

Where do you all learn all this stuff? thanks this formulla works from top to
bottom. Many thanks again. I'd like to buy you lunch!

 

[Shane Devenshire]

Hi,

Of course if your data looks like you one sample you could select all the
data in that column and choose Data, Text to Columns, Delimited,
click Next and choose Comma as the delimiter
click Next and choose a destination cell and then click Finish.

 

[Chris Bode]

Let us suppose that we have the data in column A, now we want to put th
city name in column B and the postal code in column C. For achievin
this task,
1.Select cell B1 and paste following formula
=MID(A1,1,FIND(",",A1,1)-1)
2.Select cell C1 and paste following formula
=MID(A1,FIND(",",A1)+1,LEN(A1))

Hope this works for you

Have a nice time

Chris