VBA How to get county out of the Address block and put it under County's column?

[vbanewbie68]

Hi Sir/Madam

Please find attached.

I want to tell macro to get off "West Sussex" under Donor Address line 1's column ,the last end, and then paste it into the Donor County's column? and Also how to remove commas as well Please? I have a list of hundreds like that.

Thank you in advance

Best Regards

V

 

[Peter_SSs]

Would you be able to help me to build the new codes to support that?

To do what with an example like the blue one above?

Take the wrong county and wrong column D like row 15 below?.
or
Take whatever is after the last comma but highlight it if not in the provided list like row 16

vbanewbie68.xlsm
	D	E	F
15	76 Lane, Northumberlend		Essex
16	76 Essex Lane		Northumberlend
County

Also please clarify exactly what should appear in each column if there is
- no correct county name (eg "15 Wood Lane, Essx")
- more than one correct county name (eg 23 Essex-Kent Link Road, West Sussex")
- a correct county name but is just part of another word (eg 44 Kenthurst Road, Essx")

 

[vbanewbie68]

To do what with an example like the blue one above?

Take the wrong county and wrong column D like row 15 below?.
or
Take whatever is after the last comma but highlight it if not in the provided list like row 16

Also please clarify exactly what should appear in each column if there is
- no correct county name (eg "15 Wood Lane, Essx")
- more than one correct county name (eg 23 Essex-Kent Link Road, West Sussex")
- a correct county name but is just part of another word (eg 44 Kenthurst Road, Essx")

 

[vbanewbie68]

Aberdeenshire
Alderney
Anglesey
Angus
Argyll
Argyllshire
Avon
Ayrshire
Banffshire
Bath and North East Somerset
Bedfordshire
Berkshire
Berwickshire
Brecknockshire
Bridgend County
Bromley
Buckinghamshire
Buteshire
Caerphilly
Caithness
Cambridgeshire
Cardiganshire
Carmarthenshire
Ceredigion
Channel Islands
Cheshire
Clackmannanshire
Cleveland
Clwyd
Conwy
Cornwall
County Antrim
County Armagh
County Carlow
County Cavan
County Clare
County Cork
County Donegal
County Down
County Dublin
County Dun Laoghaire-Rathdown
County Durham
County Fermanagh
County Galway
County Kerry
County Kildare
County Kilkenny
County Laois
County Leitrim
County Limerick
County Londonderry
County Longford
County Louth
County Mayo
County Meath
County Monaghan
County Offaly
County Roscommon
County Sligo
County Tipperary
County Tyrone
County Waterford
County Westmeath
County Wexford
County Wicklow
Cromartyshire
Cumberland
Cumbria
Denbighshire
Derbyshire
Devon
Dorset
Dumfriesshire
Dunbartonshire
Durham
Dyfed
East Lothian
East Sussex
East Yorkshire
Essex
Fife
Flintshire
Glamorgan
Gloucestershire
Grampian
Greater Manchester
Guernsey
Gwent
Gwynedd
Hampshire
Herefordshire
Hertfordshire
Huntingdonshire
Inverclyde
Inverness-shire
Isle of Arran
Isle of Barra
Isle of Benbecula
Isle of Bute
Isle of Coll
Isle of Cumbrae
Isle of Harris
Isle of Islay
Isle of Lewis
Isle of Man
Isle of Mull
Isle of Scilly
Isle of Skye
Isle of South Uist
Isle of Tiree
Isle of Wight
Isles of Scilly
Jersey
Kent
Kincardineshire
Kinross-shire
Kirkcudbrightshire
Lanarkshire
Lancashire
Leicestershire
Lincolnshire
Merseyside
Mid Glamorgan
Middlesex
Midlothian
Monmouthshire
Montgomeryshire
Morayshire
Nairnshire
Norfolk
North Humberside
North Lincolnshire
North Somerset
North Yorkshire
Northamptonshire
Northumberland
Nottinghamshire
Orkney
Oxfordshire
Peeblesshire
Pembrokeshire
Perthshire
Powys
Radnorshire
Renfrewshire
Rhondda Cynon Taff
Ross-Shire
Roxburghshire
Rutland
Selkirkshire
Shetland
Shropshire
Somerset
South Ayrshire
South Glamorgan
South Gloucestershire
South Humberside
South Lanarkshire
South Yorkshire
Staffordshire
Stirlingshire
Strathclyde
Suffolk
Surrey
Sutherland
Torfaen
Tyne & Wear
Tyne and Wear
Vale of Glamorgan
Warwickshire
West Glamorgan
West Lothian
West Midlands
West Sussex
West Yorkshire
Wigtownshire
Wiltshire
Worcestershire
Yorkshire
Ynys Mon

Above is the list of counties and if match is found in the address block then extract it and move it to the County's column.

Hope it helps?

Regards

V

 

[Peter_SSs]

Above is the list of counties and if match is found in the address block then extract it and move it to the County's column.

I guess that answers

To do what with an example like the blue one above?

Take the wrong county and wrong column D like row 15 below?.

and

if there is
- no correct county name (eg "15 Wood Lane, Essx")

but it does not clarify ..

clarify exactly what should appear in each column if there is

- more than one correct county name (eg 23 Essex-Kent Link Road, West Sussex")
- a correct county name but is just part of another word (eg 44 Kenthurst Road, Essx")

Also, the long list provided would ideally need to be in a worksheet column somewhere, like in column L of the image in post #6. Would that be feasible for you?

 

[vbanewbie68]

I guess that answers

and


but it does not clarify ..


Also, the long list provided would ideally need to be in a worksheet column somewhere, like in column L of the image in post #6. Would that be feasible for you?

Would be great for a long list in a worksheet instead please?
V

 

[vbanewbie68]

It would be good to highlight in red if the match is found and then msg box either yes or no. If yes then move it to the county’s column. Could use a list of counties onto another workbook? Would that be another option?

Best regards

V

 

[Peter_SSs]

There are still two example texts that I asked for clarification about to which you have not responded.

 

[vbanewbie68]

Apologies for the confusion: Hope below is what you are looking for?

1. Also please clarify exactly what should appear in each column if there is
- no correct county name (eg "15 Wood Lane, Essx")
This should be in red to flag up a double check for us to decide and then correct the spelling manually if needed.

2. more than one correct county name (eg 23 Essex-Kent Link Road, West Sussex")
This is something we need a red flag up in the column F for us to decide and correct it manually?

3. a correct county name but is just part of another word (eg 44 Kenthurst Road, Essx")
That also need in the column F to flag up for us to decide. Then correct it manually if needed.

Best regards

 

[Peter_SSs]

Thanks, but I'm afraid I am still not entirely clear on exactly what you want and where.

Possibly the following might help at least partially. For the sample data below and using the full list of counties from post #13, could you manually prepare the expected results in columns D and F exactly as you would want them to appear, including any 'flags', and post those results?

vbanewbie68.xlsm
	D	E	F
1	Donor Address Line 1		Donor County
2	754, Ellenbri Way,West Sussex
3	123, Wood Lane,Northumberland
4	5, Smith St, Essex
5	26 Jordan Rd, Kent
6	76 Essex Lane, Northumberlend
7	15 Wood Lane, Essx
8	23 Essex-Kent Link Road, West Sussex
9	44 Kenthurst Road, Essx
10	1 Hill Road, Mod Glamorgan
County

 

[vbanewbie68]

Thanks, but I'm afraid I am still not entirely clear on exactly what you want and where.

Possibly the following might help at least partially. For the sample data below and using the full list of counties from post #13, could you manually prepare the expected results in columns D and F exactly as you would want them to appear, including any 'flags', and post those results?

vbanewbie68.xlsm
	D	E	F
1	Donor Address Line 1		Donor County
2	754, Ellenbri Way,West Sussex
3	123, Wood Lane,Northumberland
4	5, Smith St, Essex
5	26 Jordan Rd, Kent
6	76 Essex Lane, Northumberlend
7	15 Wood Lane, Essx
8	23 Essex-Kent Link Road, West Sussex
9	44 Kenthurst Road, Essx
10	1 Hill Road, Mod Glamorgan
County

Thanks for the message. Would it be possible to create a msgbox to tell us when there is a potential alert to link to the county. Then we decide Yes or no we can click the button? If yes, then we can extract it from the address block to the county column?

Regards

V