| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting Text | Excel Worksheet Functions | |||
| Extracting Text only | Excel Worksheet Functions | |||
| Extracting Text from the right | Excel Worksheet Functions | |||
| EXTRACTING TEXT | Excel Discussion (Misc queries) | |||
| extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming | |||
Linear Mode
