How to create a macro that can find, copy, amend and paste cells. Plus add another cell next to that, that contains a place.


Ok I have a spreadsheet that contains uk phone number information. I will attach it below, it lists multiple area codes with info about the phone companies that can use the numbers.

Basically the standard format of an area code is 5 digits starting with a 0 so 0XXXX. eg 01200.  However in column D the area codes are missing the leading 0. eg 1200 etc

I have created a new column B to show the correct area code value including the 0.

So for the start of the macro, I need it to go through the spreadsheet and copy each area code from column D to column B including adding the missing 0.

One point to note is that when I click on an area code in column D, it shows a ' in front of the are codes, which isn't visible unless looking at the cell in the formula bar. So not sure if this could effect the marco.

Next is that each area code corresponds to a location. I have listed these below, so I also want the marco to add the location to column a for each area code to. I have attached a spreadsheet with the are codes and places in each column.

01200 Clitheroe
01202 Bournemouth
01204 Bolton
01205 Boston
01206 Colchester
01207 Consett
01208 Bodmin
01209 Redruth, Cornwall
01223 Cambridge
01224 Aberdeen
01225 Bath
01226 Barnsley
01227 Canterbury
01228 Carlisle
01229 Barrow-in-Furness
01233 Ashford, Kent
01234 Bedford
01235 Abingdon
01236 Coatbridge
01237 Bideford
01239 Cardigan
01241 Arbroath
01242 Cheltenham
01243 Chichester, West Sussex
01244 Chester
01245 Chelmsford
01246 Chesterfield
01248 Bangor, Gwynedd
01249 Chippenham
01250 Blairgowrie
01252 Aldershot
01253 Blackpool
01254 Blackburn
01255 Clacton-on-Sea
01256 Basingstoke
01257 Coppull, Chorley
01258 Blandford
01259 Alloa
01260 Congleton
01261 Banff
01262 Bridlington
01263 Cromer
01264 Andover
01267 Carmarthen
01268 Basildon
01269 Ammanford
01270 Crewe
01271 Barnstaple
01273 Brighton
01274 Bradford
01275 Clevedon
01276 Camberley
01277 Brentwood
01278 Bridgwater
01279 Bishop's Stortford
01280 Buckingham
01282 Burnley
01283 Burton upon Trent
01284 Bury St Edmunds
01285 Cirencester
01286 Caernarfon
01287 Guisborough, Cleveland
01288 Bude
01289 Berwick-upon-Tweed
01290 Cumnock, Ayrshire
01291 Chepstow
01292 Ayr
01293 Crawley
01294 Ardrossan, Ayrshire
01295 Banbury
01296 Aylesbury
01297 Axminster
01298 Buxton
01299 Bewdley
01300 Cerne Abbas, Dorset
01301 Arrochar
01302 Doncaster
01303 Folkestone
01304 Dover
01305 Dorchester
01306 Dorking
01307 Forfar
01308 Bridport, Dorset
01309 Forres
01320 Fort Augustus
01322 Dartford
01323 Eastbourne
01324 Falkirk
01325 Darlington
01326 Falmouth
01327 Daventry
01328 Fakenham
01329 Fareham
01330 Banchory, Deeside
01332 Derby
01333 Peat Inn, Fife
01334 St Andrews, Fife
01335 Ashbourne, Derbyshire
01337 Ladybank, Fife
01339 Aboyne
01340 Craigellachie, Elgin
01341 Barmouth, Dolgellau
01342 East Grinstead
01343 Elgin
01344 Bracknell, Easthampstead
01346 Fraserburgh
01347 Easingwold
01348 Fishguard
01349 Dingwall
01350 Dunkeld
01352 Mold, Flint
01353 Ely
01354 Chatteris, March, Cambridgeshire, Fenland
01355 East Kilbride
01356 Brechin, Edzell
01357 Strathaven, East Kilbride
01358 Ellon
01359 Pakenham, Elmswell
01360 Killearn, Drymen
01361 Duns
01362 Dereham
01363 Crediton
01364 Ashburton, Devon
01366 Downham Market
01367 Faringdon
01368 Dunbar
01369 Dunoon
01371 Great Dunmow, Essex
01372 Esher
01373 Frome
01375 Grays Thurrock, Essex
01376 Braintree, Essex
01377 Driffield
01379 Diss
01380 Devizes
01381 Fortrose
01382 Dundee
01383 Dunfermline
01384 Dudley
01386 Evesham
01388 Bishop Auckland, Durham
01389 Dumbarton
01392 Exeter
01394 Felixstowe
01395 Budleigh Salterton, Exmouth
01397 Fort William
01398 Dulverton, Exmoor


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please find attached.

The ' is Excel's indication that the cell contains something which it treats as text, instead of something which it treats as a number. That's why it's not visible in the cell itself, merely when you are editing it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
By the way, none of this was a macro - it was two formulas instead.
andygenAuthor Commented:
Hi Phillip

Great thanks, just what I needed :)

Thanks for the help.

andygenAuthor Commented:
Quick solution to my problems, great.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.