| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
| separate text string | Excel Worksheet Functions | |||
| separate numbers out of string | Excel Worksheet Functions | |||
| Separate a String of #'s | Excel Worksheet Functions | |||
| Separate characters in a string | Excel Discussion (Misc queries) | |||
Hybrid Mode
