Home

Customizing text in Hyperlink

%3CLINGO-SUB%20id%3D%22lingo-sub-475234%22%20slang%3D%22en-US%22%3ECustomizing%20text%20in%20Hyperlink%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475234%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20hyperlinks%20referencing%20different%20map%20locations%20on%20Google%20Maps.%26nbsp%3B%20Is%20there%20a%20way%20to%20change%20the%20entire%20column%20to%20a%20simple%20%22Map%20It%22%20instead%20of%20the%20long%20address%3F%26nbsp%3B%20I%20see%20how%20I%20can%20do%20one%20address%20at%20a%20time%20by%20right%20clicking%20but%20I%20have%20hundreds%20of%20addresses%20and%20am%20trying%20to%20keep%20all%20the%20information%20on%20one%20page.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-475234%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-476439%22%20slang%3D%22en-US%22%3ERe%3A%20Customizing%20text%20in%20Hyperlink%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-476439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323990%22%20target%3D%22_blank%22%3E%40Jimblackbook%3C%2FA%3E%2C%20you%20can%20do%20this%20with%20a%20macro.%3C%2FP%3E%3CP%3ERight-click%20on%20the%20sheet%20tab%20name%20where%20you%20have%20the%20hyperlinks.%3C%2FP%3E%3CP%3EChoose%20view%20code.%3C%2FP%3E%3CP%3EOn%20the%20window%20that%20opens%20paste%20the%20following%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20EditHyperLinks()%3CBR%20%2F%3EDim%20i%20As%20Long%3CBR%20%2F%3E'Replace%20numbers%202%20and%203%20to%20be%20the%20row%20numbers%20of%20the%20first%20cell%20and%20the%20last%20cell%20of%20the%20list%20of%20hyperlinks%20in%20your%20column%3CBR%20%2F%3EFor%20i%20%3D%202%20To%204%3CBR%20%2F%3E'Replace%20B%20with%20the%20letter%20for%20the%20column%20in%20which%20you%20have%20the%20hyperlinks%3CBR%20%2F%3ERange(%22B%22%20%26amp%3B%20i).Hyperlinks(1).TextToDisplay%20%3D%20%22Map%20it%22%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERead%20the%20green%20text%20rows%20and%20follow%20the%20instructions%20to%20change%20the%20code%20in%20the%20row%20right%20below%20according%20to%20your%20file.%3C%2FP%3E%3CP%3EClick%20F5%20to%20run%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20need%20further%20assistance.%3C%2FP%3E%3CP%3EGood%20luck!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Jimblackbook
Occasional Visitor

I have a column of hyperlinks referencing different map locations on Google Maps.  Is there a way to change the entire column to a simple "Map It" instead of the long address?  I see how I can do one address at a time by right clicking but I have hundreds of addresses and am trying to keep all the information on one page.

1 Reply
Highlighted

@Jimblackbook, you can do this with a macro.

Right-click on the sheet tab name where you have the hyperlinks.

Choose view code.

On the window that opens paste the following code:

 

Sub EditHyperLinks()
Dim i As Long
'Replace numbers 2 and 3 to be the row numbers of the first cell and the last cell of the list of hyperlinks in your column
For i = 2 To 4
'Replace B with the letter for the column in which you have the hyperlinks
Range("B" & i).Hyperlinks(1).TextToDisplay = "Map it"
Next i
End Sub

 

Read the green text rows and follow the instructions to change the code in the row right below according to your file.

Click F5 to run the macro.

 

Let me know if you need further assistance.

Good luck!