How to extract a URL from a hyperlinked image, graphic, or icon on Excel

When you copy and paste data from the Web onto an Excel spreadsheet, you sometimes end up copying and pasting images, graphics, or icons that were originally hyperlinks. To make the data usable, you may want to extract just the hyperlink and get rid of the image, graphic, or icon.

You have two options. First is to do it manually:

  1. Right-click a hyperlinked image, graphic, or icon.
  2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
  3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
  4. Press Esc to close the Edit Hyperlink dialog box.
  5. Paste the URL into any cell desired.

But what if you have more than just a few hyperlinked images, graphics, or icons? If you had to do this for each and every single hyperlinked image, graphic, or icon, this can get tedious very very quickly. So the second option is to get the URLs using a macro.

The following example can be useful when extracting hyperlinks from images, graphics, or icons that have been copied into Excel.

Extracting a URL from a hyperlinked image, graphic, or icon on Excel is easy!

  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)

Sub ConvertHLShapes()
Dim shp As Shape
Dim sTemp As String

For Each shp In ActiveSheet.Shapes
sTemp = ""
On Error Resume Next 'go to next shape if no hyperlink
sTemp = shp.Hyperlink.Address
On Error GoTo 0
If sTemp <> "" Then
shp.TopLeftCell.Value = sTemp
shp.Delete
End If
Next
End Sub

Looking to extract a URL from a simple hyperlinked text? View steps to extract a URL from a hyperlink »

Posted in Macros | Leave a comment

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 | 2 Responses

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 | 70 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