Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Postcode problem better explained
Hi - I have explained this a bit better now -
If I have a cell and the contents are like this here are 3 examples - GREENGATES S.P, APPERLEY RD, GREENGATES, BRADFORD, BD10 0PU SILSDEN, CRINGLES, SILSDEN, KEIGHLEY, BD20 MIDDLESMOOR SERVICE RES, MIDDLESMOOR SERVICE RES, HARROGATE, NORTH YORKSHIRE, , HG3 How can I get the postcodes out of those cells? for now I just want them to go into the same row in the end column. any tutorials or similar or pages with similar stuff on would be great. Thanks Mary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Postcode problem better explained
Mary,
A suggestion if you want help. STAY in your original thread. And, Us Texans don't know what a "postcode" is. Is that like a zipcode? If so, in the US that is a 9 digit+4 number. -- Don Guillett SalesAid Software "Mary" wrote in message ... Hi - I have explained this a bit better now - If I have a cell and the contents are like this here are 3 examples - GREENGATES S.P, APPERLEY RD, GREENGATES, BRADFORD, BD10 0PU SILSDEN, CRINGLES, SILSDEN, KEIGHLEY, BD20 MIDDLESMOOR SERVICE RES, MIDDLESMOOR SERVICE RES, HARROGATE, NORTH YORKSHIRE, , HG3 How can I get the postcodes out of those cells? for now I just want them to go into the same row in the end column. any tutorials or similar or pages with similar stuff on would be great. Thanks Mary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Postcode problem better explained
Try it with this:
MsgBox Trim(Mid(Cells(1), InStrRev(Cells(1), ",", -1, vbTextCompare) + 1)) RBS "Mary" wrote in message ... Hi - I have explained this a bit better now - If I have a cell and the contents are like this here are 3 examples - GREENGATES S.P, APPERLEY RD, GREENGATES, BRADFORD, BD10 0PU SILSDEN, CRINGLES, SILSDEN, KEIGHLEY, BD20 MIDDLESMOOR SERVICE RES, MIDDLESMOOR SERVICE RES, HARROGATE, NORTH YORKSHIRE, , HG3 How can I get the postcodes out of those cells? for now I just want them to go into the same row in the end column. any tutorials or similar or pages with similar stuff on would be great. Thanks Mary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Postcode problem better explained
Mary,
You could use the formula =TRIM(MID(A1,FIND("~",SUBSTITUTE(A1,",","~",LEN(A1 )-LEN(SUBSTITUTE(A1,",","" ))))+1,99)) -- HTH RP (remove nothere from the email address if mailing direct) "Mary" wrote in message ... Hi - I have explained this a bit better now - If I have a cell and the contents are like this here are 3 examples - GREENGATES S.P, APPERLEY RD, GREENGATES, BRADFORD, BD10 0PU SILSDEN, CRINGLES, SILSDEN, KEIGHLEY, BD20 MIDDLESMOOR SERVICE RES, MIDDLESMOOR SERVICE RES, HARROGATE, NORTH YORKSHIRE, , HG3 How can I get the postcodes out of those cells? for now I just want them to go into the same row in the end column. any tutorials or similar or pages with similar stuff on would be great. Thanks Mary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Postcode problem better explained
On Sat, 26 Feb 2005 09:50:04 -0800, "Mary"
wrote: Hi - I have explained this a bit better now - If I have a cell and the contents are like this here are 3 examples - GREENGATES S.P, APPERLEY RD, GREENGATES, BRADFORD, BD10 0PU SILSDEN, CRINGLES, SILSDEN, KEIGHLEY, BD20 MIDDLESMOOR SERVICE RES, MIDDLESMOOR SERVICE RES, HARROGATE, NORTH YORKSHIRE, , HG3 How can I get the postcodes out of those cells? for now I just want them to go into the same row in the end column. any tutorials or similar or pages with similar stuff on would be great. Thanks Mary I'm not sure what a "post code" is. But looking at your data, it seems as if it is the last entry in your record and is preceded by a comma. Accordingly, you may use the Data/Text to Columns Wizard with Comma specified as the separator; or you may try this formula: =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,",", CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,",","")))),255)) --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
the formats
I think that the format all postcodes take is like this
LLNN NLL. But maybe we could specify to collect and write any of the formats below - LS (LL) LS2 (LLN) LS2 6 (LLN_N) LS2 6N (LLN_NL) LS2 6NL (LLN_NLL) LS26 (LLNN) LS26 6 (LLNN_N) LS26 6N (LLNN_NL) LS26 6NL (LLNN_NLL) (_ indicating a space, L = Letter, N = Number) This would solve the problem if there were a comma then some more information like a telephone number following it. Would your suggestions get it if this were so? Thanks -----Original Message----- Hi - I have explained this a bit better now - If I have a cell and the contents are like this here are 3 examples - GREENGATES S.P, APPERLEY RD, GREENGATES, BRADFORD, BD10 0PU SILSDEN, CRINGLES, SILSDEN, KEIGHLEY, BD20 MIDDLESMOOR SERVICE RES, MIDDLESMOOR SERVICE RES, HARROGATE, NORTH YORKSHIRE, , HG3 How can I get the postcodes out of those cells? for now I just want them to go into the same row in the end column. any tutorials or similar or pages with similar stuff on would be great. Thanks Mary . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
META Tags Explained And How To Use Them For Ranking | Excel Worksheet Functions | |||
How to locate the first digit in a cell, explained | Excel Worksheet Functions | |||
M25 postcode | Excel Worksheet Functions | |||
Counting Formulas -- Re-explained | Excel Worksheet Functions | |||
Postcode splitting problem | Excel Programming |