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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rick" wrote:

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!

"Fred Smith" wrote:

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" wrote in message
...
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?

"FSt1" wrote:

thanks for the feedback
Regards
FSt1

"Rick" wrote:

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

"FSt1" wrote:

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" wrote:

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).