Extracting UK post codes

[Derek Stephen]

Hi

How can I extract UK post codes from a non uniform text string into a separate row

Derek

 

[dispelthemyth]

Great Function Rick

Tony,
It might be worth reading the below article, its a great at explaining the like function

Like Operator (Visual Basic)

Nice one Rick!

It,s easy when you know how.
Thank you, that has opened my eyes to the wider possibilities of using Like.

 

[Rick Rothstein]

Great Function Rick

Thanks!

Tony,
It might be worth reading the below article, its a great at explaining the like function

Like Operator (Visual Basic)

This might be a better article refer people to as it is VBA specific... your link is for the Like operator in the "DotNet" version of Visual Basic...

Like Operator

 

[Rick Rothstein]

Nice one Rick!

Thanks!

Thank you, that has opened my eyes to the wider possibilities of using Like.

The Like operator is one my favorite features of VB, right up there with the Split, Join and Replace function... these VB functions and operator allow one to construct some remarkably complex programs.

 

[Derek Stephen]

Rick

Thank you so much for your help , I do have a problem in getting the formula to operate, see copy below.

UKPostCode		Post Codes
Church Farm House, 6 Church Farm Lane, Sidlesham, Chichester, PO20 7RE - F/C Tank Model Major, Flat Top Blowdown Vessel, Sample		#NAME?
.
Installed @ Glenmuir Limited, Weavers Walk Lanark, Scotland ML11 9AW
Project: C&N Chambers - Address not known
Wheatfield Prison, Dublin
End User Not Known
.
Not known
End User Not Known
End User Not Known
.
End User: Not known
.
Supplied 2003 -Installed @ Sketchleys, 41 Market Place, Reading, Berks RG1 2DE Branch Ref: 6524
Supplied 2003 - Installed @ Sketchleys, 849 Osmaston, Allenton, Derby, DE24 9ABAllenton, Derby. Branch Ref: 6171
Birchley House, Birchley Roundabout, Birchfield Lane, Oldbury, West Midlands B69 1DT
Trading Estate, PO Box 2246, Dubai, United Arab Emirates

<colgroup><col width="957" style="width: 718pt; mso-width-source: userset; mso-width-alt: 34998;"> <col width="64" style="width: 48pt;"> <col width="232" style="width: 174pt; mso-width-source: userset; mso-width-alt: 8484;"> <tbody> </tbody>

As you can see I am getting a message #NAME?

Any help would be appreciated.

Derek

 

[Snakehips]

Derek,

I suspect that you have copied Rick's functions to a sheet module rather than a code module.

If you are using Rick's final offering with the validation then you will need to copy both bits of code to the module.

Open the vb editor - F11 and click Insert - Module and paste the code into the code pane on the rhs.

The function UKPostCode will then be in the function drop down when you enter =U in a cell within your sheet.

Hope that helps.

 

[Rick Rothstein]

A #NAME? error is generated when Excel does not recognize something in the formula as a valid function name... my guess is you spelled my function name (UKPostCode) in the formula (perhaps you put an "s" on the end of it?). Another possibility is you installed the code in the wrong place (it should be in a general module) and Excel cannot find it.

 

[jcoronav]

Check this tool to solve your problem about extracting pstal codes from the cell: Boost MS-Excel capabilities to select, totalize, standardize and join data.
José

 

[Derek Stephen]

Rick / Tony

I have recopied the code and ensured that it is not copied to the Worksheet , named the module UKPostCode,and then saved it as a macro enabled xlsm and exited to worksheet.

All of the text strings are column " A " starting "A2". I then insert in "C2" =UKPostCode , and I get the error NAME? .

Help where am I going wrong.

Really do appreciate you Guys help

Derek

 

[Kyle123]

=UKPostcode(A2)

 

[Derek Stephen]

Yes sorry =UKPostCode(A2) was the entry I put in