Excel formula to change foreign accented characters to regular non-accented characters

Need to change foreign accented characters to regular non-accented characters? I just encountered this problem today. I have a list of hundreds of thousands of city names from all over the world, and I needed to generate a list of these names without the foreign accented characters. Here’s how to get this done in just two seconds.

For example, you have the following the text in a cell “A1″:

“Montréal”

and you’d like to change “é” to simply “e”, so that it shows the following:

“Montreal”

You can use Excel’s SUBSTITUTE function to do this. However, Excel only allows up to 64 levels of nesting, so you need to split your formula into two.

First, enter this in cell “B1″:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"á","a"),"é","e"),"í","i"),"ó","o"),"ú","u"),"ã","a"),"ê","e"),"â","a"),"é","e"),"è","e"),"î","i"),"ï","i"),"ç","c"),"ä","a"),"ö","o"),"ü","u"),"ß","ss"),"ş","s"),"ı","i"),"ğ","g"),"ę","e"),"ł","l"),"ń","n"),"ś","s"),"ż","z"),"ã","a"),"ầ","a"),"à","a"),"ậ","a"),"đ","d"),"ế","e"),"ì","i"),"í","i"),"ổ","o"),"ô","o"),"ư","u"),"ả","a"),"ế","e"),"ĩ","i"),"ợ","o"),"ồ","o"),"ạ","a"),"ứ","u"),"ý","y"),"ạ","a"),"é","e"),"ỳ","y"),"ế","e"),"ể","e"),"ệ","e"),"ù","u"),"ë","e"),".",""),"Ġ","g"),"ø","o"),"ñ","n"),"'",""),"ō","o"),"--","-"),"ħ","h"),"å","a")

Then, enter this in cell “C1″:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D3,"æ","ae"),"Ġ","g"),"Č","c"),"ě","e"),"ň","n"),"š","s"),"ě","e"),"ň","n"),"ž","z"),"ř","r"),"č","c"),"ġ","g")

Cell “C1″ will display the altered name without any foreign accented characters.

The formula above takes into account foreign accents in the following languages:

  • Spanish
  • Italian
  • Portuguese
  • French
  • German
  • Turkish
  • Polish
  • Swedish
  • Dutch
  • Hungarian
  • Vietnamese
  • Czech
  • Slovak
  • Danish
  • Finnish
  • Romanian
Posted in Extracting words | Leave a comment

How to capitalize the first word in a string (or the first letter in a cell) using an Excel macro instead of an Excel function or formula

Although you can use a Microsoft Excel function or formula to capitalize the first word in a string (or the first letter in a cell), there are times when you may want to change the case of the text in the same cell the data was entered, programmatically. Just follow these steps:

1. Hit [ALT]+[F11] to open the Visual Basic Editor

2. Hit [ALT]+[I]+[M], or just click “Insert” > “Module”

3. In the pop-up window copy the ff. code”:

Sub CapsFirstLetter()
Dim Cell As Variant
For Each Cell In Selection
Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Right(Cell.Text, Len(Cell.Text) - 1))
Next
End Sub

4. Hit [ALT]+[Q], or just click “File” > “Close and return to Microsoft Excel”

5. Run the Macro in Excel by going to:

Microsoft 2007: View > Macro > Macro and then Run “CapsFirstLetter”

Microsoft 2010: Tools > Macros > View Macros and then Run “CapsFirstLetter”.

This will capitalize all the text in the range you selected on the worksheet.

 

Want to capitalize the first character in every word using an Excel macro? View this tutorial »

Posted in Macros | Leave a comment

How to uppercase text using an Excel macro instead of the UPPER function

Although you can use the =UPPER() function or formula to lowercase all the text in a cell, there are times when you may want to uppercase the text in the same cell the data was entered, programmatically. Just follow these steps:

1. Hit [ALT]+[F11] to open the Visual Basic Editor

2. Hit [ALT]+[I]+[M], or just click “Insert” > “Module”

3. In the pop-up window copy the ff. code and edit the range “A1:A5″:

Sub Uppercase()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("A1:A5")
      ' Change the text in the range to uppercase letters.
      x.Value = UCase(x.value)
   Next
End Sub

4. Hit [ALT]+[Q], or just click “File” > “Close and return to Microsoft Excel”

5. Run the Macro in Excel by going to:

Microsoft 2007: View > Macro > Macro and then Run “Uppercase”

Microsoft 2010: Tools > Macros > View Macros and then Run “Uppercase”.

This will uppercase all the text in the range you specified on the selected worksheet (cells A1:A5 in the example code above).

 

Want to lowercase text using an Excel macro? View this tutorial »

Want to capitalize just the first character in every word using an Excel macro? View this tutorial »

Posted in Macros | Leave a comment

How to lowercase text using an Excel macro instead of the LOWER function

Although you can use the =LOWER () function or formula to lowercase all the text in a cell, there are times when you may want to lowercase the text in the same cell the data was entered, programmatically. Just follow these steps:

1. Hit [ALT]+[F11] to open the Visual Basic Editor

2. Hit [ALT]+[I]+[M], or just click “Insert” > “Module”

3. In the pop-up window copy the ff. code and edit the range “A1:A5″:

Sub Lowercase()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("A1:A5")
      x.Value = LCase(x.Value)
   Next
End Sub

4. Hit [ALT]+[Q], or just click “File” > “Close and return to Microsoft Excel”

5. Run the Macro in Excel by going to:

Microsoft 2007: View > Macro > Macro and then Run “Lowercase”

Microsoft 2010: Tools > Macros > View Macros and then Run “Lowercase”.

This will lowercase all the text in the range you specified on the selected worksheet (cells A1:A5 in the example code above).

 

Want to uppercase text using an Excel macro? View this tutorial »

Want to capitalize just the first character in every word using an Excel macro? View this tutorial »

Posted in Macros | Leave a comment

How to capitalize all words using an Excel macro instead of the PROPER function

Although you can use the =PROPER () function or formula to capitalize all the words in a cell, there are times when you may want to capitalize the text in the same cell the data was entered, programmatically. Just follow these steps:

1. Hit [ALT]+[F11] to open the Visual Basic Editor

2. Hit [ALT]+[I]+[M], or just click “Insert” > “Module”

3. In the pop-up window copy the ff. code and edit the range “A1:A5″:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:A5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub

4. Hit [ALT]+[Q], or just click “File” > “Close and return to Microsoft Excel”

5. Run the Macro in Excel by going to:

Microsoft 2007: View > Macro > Macro and then Run “Proper_Case”

Microsoft 2010: Tools > Macros > View Macros and then Run “Proper_Case”.

This will capitalize all the text in the range you specified on the selected worksheet (cells A1:A5 in the example code above).

 

Want to lowercase text using an Excel macro? View this tutorial »

Want to uppercase text using an Excel macro? View this tutorial »

Posted in Macros | Leave a comment

Formula to find next Monday’s date

Here’s a formula that you can use to generate next Monday’s date:

=TODAY()+8-WEEKDAY(TODAY(),2)

Posted in Functions | Leave a comment

How to make Excel stop calculating automatically

Is your Excel file calculating and recalculating all the formulas automatically, causing your entire computer to freeze? That’s likely because your Excel application’s “Workbook Calculation” is set to “Automatic” instead of “Manual”.

  • Excel 2003: Tools > Options > Calculation > Calculation > Manual
  • Excel 2007: Office button > Excel options > Formulas > Workbook Calculation > Manual
  • Excel 2010: File > Options > Formulas > Workbook Calculation > Manual

If you want your file to recalculate before saving, make sure to check the “Recalculate workbook before saving” option.

Posted in General tips | Leave a comment

How to display two Excel sheets side by side on two monitors in Windows 7

If you use Excel a lot and spend most of your day toggling across multiple Excel files, then having a dual monitor setup will help you tremendously! In fact, a New York Times study has shown that having an additional monitor increases productivity by 20 to 30 percent. However, the challenge is that some applications such as MS Office Excel load multiple files from the same instance of the application. This means that Excel opens multiple files on top of each other on the same monitor. And if you try moving one Excel file to the second monitor, all other Excel files also move to the second monitor.

How to display one Excel file on one monitor and another file on a second monitor

To display the two files side by side instead of on top of each other, you have to load the two files as two separate instances. To do this, you must open a new instance of Excel, then open/create a workbook. I’ve summarized the steps below.

  1. Open the first Excel file you need to access and put that on Monitor #1.
  2. Open a second instance of Excel on Monitor #2 by going to the Start Menu > Programs > Micrrosoft Office > Excel.
  3. Move this second instance of Excel to Monitor #2.
  4. Open or create a workbook from the second instance of Excel that’s now on Monitor #2.

Voila!

Posted in General tips | 50 Responses

How to make VLOOKUP search from the bottom to the top of the list

Normally, VLOOKUP searches data from top to bottom. But when you have a long list of data and you want to get the latest data at the bottom of the list (instead of the older data at the top), you can’t use VLOOKUP because it always returns the first one it sees from the top, not the latest one.

Here’s a formula that performs a “reverse VLOOKUP”:

=LOOKUP(2,1/(A:A=G2),B:B)

Download this sample Excel sheet to see how this “reverse VLOOKUP” formula works »

Posted in Functions | Leave a comment

How to Extract Text Before Second or Third Instance of a Symbol

For example, I have the following list in cell A1:

“Coffee, cream, sugar, tea”

I am trying to copy all the text before the second or third instance of the comma into an adjacent cell.

To extract the text before the second instance of the comma, use the ff. formula:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1,",","^^",2))-1)

where the 2 represents the comma instance number

The result: Coffee, cream

Posted in Extracting words | Leave a comment