|
extracting certain text
Hi there,
I have a long list of town names with the first 3 digits of the postal code at the end of it. I need to extract the postal code data from the field and show it in a separate column. I have tried using text to columns but it doesn't work correctly because some town names have more than one word. I have also tried using the letter V as a column separator (all my postal codes begin with V) but that doesn't work because some town names begin with V. Here's a sample of what I have: KELOWNA KELOWNA STN OKANAGAN DC V1Z PENTICTON PENTICTON STN MAIN V2A KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2B KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2C KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2E WILLIAMS LAKE WILLIAMS LAKE STN MAIN V2G KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2H QUESNEL QUESNEL STN MAIN V2J PRINCE GEORGE PRINCE GEORGE STN B V2K PRINCE GEORGE PRINCE GEORGE STN A V2L PRINCE GEORGE PRINCE GEORGE STN B V2M PRINCE GEORGE PRINCE GEORGE STN B V2N CHILLIWACK CHILLIWACK STN MAIN V2P Only the last three digits of each cell need to be pulled out. Any ideas? Please & Thanks! |
extracting certain text
Hi
=RIGHT(A1,3) -- Regards Frank Kabel Frankfurt, Germany "lost!!" schrieb im Newsbeitrag ... Hi there, I have a long list of town names with the first 3 digits of the postal code at the end of it. I need to extract the postal code data from the field and show it in a separate column. I have tried using text to columns but it doesn't work correctly because some town names have more than one word. I have also tried using the letter V as a column separator (all my postal codes begin with V) but that doesn't work because some town names begin with V. Here's a sample of what I have: KELOWNA KELOWNA STN OKANAGAN DC V1Z PENTICTON PENTICTON STN MAIN V2A KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2B KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2C KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2E WILLIAMS LAKE WILLIAMS LAKE STN MAIN V2G KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2H QUESNEL QUESNEL STN MAIN V2J PRINCE GEORGE PRINCE GEORGE STN B V2K PRINCE GEORGE PRINCE GEORGE STN A V2L PRINCE GEORGE PRINCE GEORGE STN B V2M PRINCE GEORGE PRINCE GEORGE STN B V2N CHILLIWACK CHILLIWACK STN MAIN V2P Only the last three digits of each cell need to be pulled out. Any ideas? Please & Thanks! |
extracting certain text
THANKS!!!
"Frank Kabel" wrote: Hi =RIGHT(A1,3) -- Regards Frank Kabel Frankfurt, Germany "lost!!" schrieb im Newsbeitrag ... Hi there, I have a long list of town names with the first 3 digits of the postal code at the end of it. I need to extract the postal code data from the field and show it in a separate column. I have tried using text to columns but it doesn't work correctly because some town names have more than one word. I have also tried using the letter V as a column separator (all my postal codes begin with V) but that doesn't work because some town names begin with V. Here's a sample of what I have: KELOWNA KELOWNA STN OKANAGAN DC V1Z PENTICTON PENTICTON STN MAIN V2A KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2B KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2C KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2E WILLIAMS LAKE WILLIAMS LAKE STN MAIN V2G KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2H QUESNEL QUESNEL STN MAIN V2J PRINCE GEORGE PRINCE GEORGE STN B V2K PRINCE GEORGE PRINCE GEORGE STN A V2L PRINCE GEORGE PRINCE GEORGE STN B V2M PRINCE GEORGE PRINCE GEORGE STN B V2N CHILLIWACK CHILLIWACK STN MAIN V2P Only the last three digits of each cell need to be pulled out. Any ideas? Please & Thanks! |
extracting certain text
Try this in column B1 and copy down
=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),99) -- HTH RP (remove nothere from the email address if mailing direct) "lost!!" wrote in message ... Hi there, I have a long list of town names with the first 3 digits of the postal code at the end of it. I need to extract the postal code data from the field and show it in a separate column. I have tried using text to columns but it doesn't work correctly because some town names have more than one word. I have also tried using the letter V as a column separator (all my postal codes begin with V) but that doesn't work because some town names begin with V. Here's a sample of what I have: KELOWNA KELOWNA STN OKANAGAN DC V1Z PENTICTON PENTICTON STN MAIN V2A KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2B KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2C KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2E WILLIAMS LAKE WILLIAMS LAKE STN MAIN V2G KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2H QUESNEL QUESNEL STN MAIN V2J PRINCE GEORGE PRINCE GEORGE STN B V2K PRINCE GEORGE PRINCE GEORGE STN A V2L PRINCE GEORGE PRINCE GEORGE STN B V2M PRINCE GEORGE PRINCE GEORGE STN B V2N CHILLIWACK CHILLIWACK STN MAIN V2P Only the last three digits of each cell need to be pulled out. Any ideas? Please & Thanks! |
extracting certain text
oops - missing the obvious
-- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Try this in column B1 and copy down =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),99) -- HTH RP (remove nothere from the email address if mailing direct) "lost!!" wrote in message ... Hi there, I have a long list of town names with the first 3 digits of the postal code at the end of it. I need to extract the postal code data from the field and show it in a separate column. I have tried using text to columns but it doesn't work correctly because some town names have more than one word. I have also tried using the letter V as a column separator (all my postal codes begin with V) but that doesn't work because some town names begin with V. Here's a sample of what I have: KELOWNA KELOWNA STN OKANAGAN DC V1Z PENTICTON PENTICTON STN MAIN V2A KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2B KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2C KAMLOOPS KAMLOOPS STN SOUTH DEL CTR V2E WILLIAMS LAKE WILLIAMS LAKE STN MAIN V2G KAMLOOPS KAMLOOPS STN NORTH DEL CTR V2H QUESNEL QUESNEL STN MAIN V2J PRINCE GEORGE PRINCE GEORGE STN B V2K PRINCE GEORGE PRINCE GEORGE STN A V2L PRINCE GEORGE PRINCE GEORGE STN B V2M PRINCE GEORGE PRINCE GEORGE STN B V2N CHILLIWACK CHILLIWACK STN MAIN V2P Only the last three digits of each cell need to be pulled out. Any ideas? Please & Thanks! |
| All times are GMT +1. The time now is 12:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com