separate string
 

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

separate string
 

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

separate string
 

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

separate string
 

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

separate string
 

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

separate string
 

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

separate string
 

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

separate string
 

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

Let us suppose that we have the data in column A, now we want to put the city name in column B and the postal code in column C. For achieving 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
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com