Extract specific text in Cell A1 and display in A2

Good afternoon,

Can somebody please help me with this problem:

I Need to extract country codes/names from a paragraph of text from Cell A1 (Worksheet 1) and display in cell A2 (worksheet 2) in a specific format.

Each time one of our customers does a marketing campaign they fill in an Excel spreadsheet (layed out as a form). There is one cell where they can write a description of their marketing campaign.

E.g. "We are doing a campaign in France, Germany and possibly in UK"

From this I need to automatically extract France, Germany, UK and display it in cell A2 as "FR DE UK" ie without commas between country codes.

So if "France" is found in cell A1 then the country code "FR" needs to be displayed in cell A2. IF "FR" is found in cell A1 then "FR" is displayed in A2.

Text to find:

France= FR
Germany = DE
United Kingdom = UK
Spain= ES
Denmark = DK

There are more countries, but I can add them myself once I understand the basic code.

I tried IFERROR, OR and IF statements but this did not work.


Thanks

Dave32

this macro udates A2 when A1 is changed if the macro is installed correctly & macro security is not set to high

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Please Login or Register  to view this content.

or to run macro when you want to try

Please Login or Register  to view this content.

Or

Select the cells to check first

Please Login or Register  to view this content.

VBA Noob

See if this helps

VBA Noob

In my code I have an End If at the end when it should be End Sub