Postcode problem better explained

[Mary]

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

 

[Don Guillett]

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.

 

[RB Smissaert]

Try it with this:

MsgBox Trim(Mid(Cells(1), InStrRev(Cells(1), ",", -1, vbTextCompare) + 1))

RBS

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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

 

[Mary]

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

 

[Tim Williams]

This is a perfect application for RegularExpressions. I'm no expert
but this appears to do the job for your possible formats

Tim.

************************************************
Function FindUKPostcode(ByVal TextIn As String)

Static regex As Object, matches As Object
Dim x As Integer
Dim sRet As String

If regex Is Nothing Then
Set regex = CreateObject("vbscript.regexp")
'two letters followed by one or two numbers
'then (optional)space + one or two numbers + one or two letters
regex.Pattern = "[A-Z]{2}[0-9]{1,2}(\s?[0-9]{1,2}[A-Z]{1,2})?"
regex.Global = True
regex.ignorecase = True
End If

TextIn = UCase(TextIn)

sRet = ""
Set matches = regex.Execute(TextIn)
If matches.Count > 0 Then
For x = 0 To matches.Count - 1
sRet = sRet & IIf(sRet = "", "", Chr(10)) & matches(x).Value
Next x
End If

FindUKPostcode = sRet

End Function


Tim.

 

[Tim Williams]

forgot to mention you should add a reference to "Microsoft scripting
runtime" in your project

tim

This is a perfect application for RegularExpressions. I'm no expert
but this appears to do the job for your possible formats

Tim.

************************************************
Function FindUKPostcode(ByVal TextIn As String)

Static regex As Object, matches As Object
Dim x As Integer
Dim sRet As String

If regex Is Nothing Then
Set regex = CreateObject("vbscript.regexp")
'two letters followed by one or two numbers
'then (optional)space + one or two numbers + one or two letters
regex.Pattern = "[A-Z]{2}[0-9]{1,2}(\s?[0-9]{1,2}[A-Z]{1,2})?"
regex.Global = True
regex.ignorecase = True
End If

TextIn = UCase(TextIn)

sRet = ""
Set matches = regex.Execute(TextIn)
If matches.Count > 0 Then
For x = 0 To matches.Count - 1
sRet = sRet & IIf(sRet = "", "", Chr(10)) & matches(x).Value
Next x
End If

FindUKPostcode = sRet

End Function


Tim.

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

 

[Bob Phillips]

Not when you code it as late binding you don't.

--

HTH

RP
(remove nothere from the email address if mailing direct)

forgot to mention you should add a reference to "Microsoft scripting
runtime" in your project

tim

This is a perfect application for RegularExpressions. I'm no expert
but this appears to do the job for your possible formats

Tim.

************************************************
Function FindUKPostcode(ByVal TextIn As String)

Static regex As Object, matches As Object
Dim x As Integer
Dim sRet As String

If regex Is Nothing Then
Set regex = CreateObject("vbscript.regexp")
'two letters followed by one or two numbers
'then (optional)space + one or two numbers + one or two letters
regex.Pattern = "[A-Z]{2}[0-9]{1,2}(\s?[0-9]{1,2}[A-Z]{1,2})?"
regex.Global = True
regex.ignorecase = True
End If

TextIn = UCase(TextIn)

sRet = ""
Set matches = regex.Execute(TextIn)
If matches.Count > 0 Then
For x = 0 To matches.Count - 1
sRet = sRet & IIf(sRet = "", "", Chr(10)) & matches(x).Value
Next x
End If

FindUKPostcode = sRet

End Function


Tim.

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
.