Solved Find and Correct country names!

Microsoft Excel 2013 32/64-bit - license...
November 10, 2017 at 14:52:24
Specs: Windows 7
I have data for multiple countries. The problem is names of countries are messed up. For instance, 'US' could be 'United States', 'United sta', 'USA'. Then 'Brazil' could be misspelled as 'Brasil', 'Br' etc. and goes on for over 20 different countries!
I need to find out and correct all the countries to their proper names! Help, please.

See More: Find and Correct country names!

Reply ↓  Report •

#1
November 10, 2017 at 15:10:14
You could start by using the Spell Checker:

On the ribbon select the Review Tab
Then select Spelling

Have it check the entire document.
That will get some of the misspellings, as for the rest
you will probably need to do a Search and Replace.

IE Search for: U.S. replace with: United States,

If you use the Find All option, it will give you a list of
entries that match your search, might make it a bit easier.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
November 10, 2017 at 15:27:18
Spellcheck won't allow me to enter the correct word. I would have to choose from one that excel suggest which won't help me with my issue. For instance, for 'United Sta' spell check will suggest everything else but 'state' as the correction, let alone other country names.
Then, as for the replacing U.S. with united states, I might have over 10 wrongly spelled name for each country and I have over 25 country name. That means I would have to find out each wrongly spelled name and manually correct it. This process would take a lot of time. I would love to have something automated. Even with the macro this process would be very manual. Any suggestions?

message edited by adiityaa1


Reply ↓  Report •

#3
November 10, 2017 at 16:01:57
✔ Best Answer
The spellcheck will only catch those words that are close to correct, like Brasil
You will have to use Find & Replace for most of the others.

See here for a suggestion that might help:

http://www.excelarticles.com/Excel_...

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
November 10, 2017 at 20:19:50
This macro might work for some of your data, assuming that your definition of "messed up" is the same as Excel's definition of "misspelled".

In other words, just because you think that USA is a "messed up" spelling of United States, Excel does not. At least this will let you fix actual spelling errors by allowing you to enter what you want:

Sub MySpellChk()
 For Each cell In UsedRange
   If Application.CheckSpelling(word:=cell.Value) = False Then
     mySpell = Application.InputBox(cell.Value & " Is Spelt Incorrectly." _
                                   & vbCrLf & vbCrLf _
                                   & "Please Enter The Correct Spelling:")
     cell.Value = mySpell
   End If
 Next
End Sub

The main problem I see with your "automated" desire is that there is no way to tell Excel that U.S., United Sts, USA, Unit States, etc. are all misspelled versions of United States without manually entering each possible misspelling. If you did that, then you could run a macro to match each version of the misspelling to the correct country name.

If you had to perform these corrections time after time after time, like with multiple data sets, then it might be worth creating that list. If it's a one time exercise, then creating the list might be just as much work as simply doing the corrections manually.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
November 10, 2017 at 20:54:28
I haven't written it yet, but what about a macro that selected the misspelled word, created a temporary Drop-Down list in the cell next to that misspelling and allowed you to chose your country from that list? Once the correct country was selected, that Drop Down list would be deleted, the next misspelled word would be selected and a temporary Drop-Down list would be created in the cell next to that misspelling, etc. That would eliminate the need to manually type in the correct name.

As before, the "messed up" word would need to be an actual misspelling according to Excel.

(If I was any good at creating and using Forms, the Drop Down list might actually be a "dialog box" of sorts with your country list and radial buttons. Unfortunately, that's a skill I never developed, so a Drop Down list is the best that I can offer.)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
November 11, 2017 at 04:58:54
adiityaa1,

Branching off from DerbyDad's observations...

About how many times do country names appear in total?

Are they all in one column? Can they be in any column?

Can country names appear more than once in a cell?

About how many names do you think are corrupted?

Is this a continuing problem that you will need to deal
with repeatedly, maybe every month for years?


Ha! Someone on a business program on MSNBC, just now
while I am typing this, mentioned this same problem with
databases having info in different forms. The example she
gave was addresses, some spelling out the word "street" and
others abbreviating it "St.". I'm not sure why, but she implied
that it is a problem.

-- Jeff, in Minneapolis


Reply ↓  Report •

#7
November 11, 2017 at 05:17:22
I'm no expert with Excel, but one thing I'm trying to
determine with those questions is whether it would be
more efficient to search through the entire database
for a possible misspelling, then search through the
entire database for the next possible misspelling, and
so forth, or to examine each cell in turn for any possible
misspelling, going through the database just once.
The latter is certainly the way to do it manually!

-- Jeff, in Minneapolis


Reply ↓  Report •

#8
November 11, 2017 at 12:00:13
Here is an Excel Add-In that may be of some help.
I have never used it, but looks like it may be what your looking for.

https://www.microsoft.com/en-us/dow...

Here is a page on how to use the add-in:

http://www.k2e.com/tech-update/tips...

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#9
November 11, 2017 at 20:57:50
Thanks man, I think this could help. I will try some adjustment and see if I can deal with the issue. Appreciated.

Reply ↓  Report •

#10
November 11, 2017 at 21:02:26
Thanks buddy I appreciate the help. 'US' for 'United States', as I said, was there just as an example of 'messed up' data. May be a poor example. But I did mention 'Brasil' instead of 'Brazil', in case you missed it. Hope you understand! Thanks again for the input, looks like it'll be a great help.

message edited by adiityaa1


Reply ↓  Report •

#11
November 11, 2017 at 21:25:47
Hello Jeff, So there are a few million records. Country names are definitely in a single column. The country name appears only once per column and I would have to assume that the maximum number of entries are entered incorrectly. Also, I think this could be a one-time thing only but I have other data that carries same kind of problem hence I am going for more elaborated but possibly automated process. Hoping to apply same line of procedure to other misspelled names like street or suite!

Reply ↓  Report •

#12
November 11, 2017 at 21:59:18
Besides the Pivot Table example in reply #3,
the only other idea I can think of is to simply Sort your data on the Country column.
This should put all the possible spellings next to each other,
which should make correcting the wrong ones a bit easier.

Good luck

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#13
November 11, 2017 at 23:18:36
Thanks mmcconaghy, #3 looks like the best approach for now. I am trying DerbyDad03's macro, hopefully that would give me some better ideas and with some luck I would be able to make this whole process a bit less tedious.

message edited by adiityaa1


Reply ↓  Report •

#14
November 12, 2017 at 06:42:25
Again, my experience with Excel is limited, but mmcconaghy's
suggestion in reply #12 sounds really good. You just need to look
for the transitions from one country to the next. With 20 countries
in the database, after you have corrected all the unwanted spellings
there will be only 19 transitions -- easy to double-check manually.

-- Jeff, in Minneapolis

message edited by Jeff Root


Reply ↓  Report •

Ask Question