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

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

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

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

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

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
.