I have a list of Craigslist hypertext links to the items listed by city location. I am wanting to identify the city location in column (B) (see attached)
To do this, I need to convert the hypertext to text. I did this in A2 with a single ', but was wanting a macro to do the entire list
Convert Hypertext to Text
-
-
-
Re: Convert Hypertext to Text
Here's something I whipped up. One of the experts probably has a quicker more fluent way to do it. But this is functional.
Code
Display MoreSub removehyperlink() Dim totalrows As Long Dim cell As Range Dim acol Dim bcol totalrows = ActiveSheet.UsedRange.Rows.Count acol = "$A$2:$A$" & totalrows bcol = "$B$2:$B$" & totalrows For Each cell In Range(acol) cell.Formula = "'" & cell.Formula Range("B2").Select Selection.AutoFill Destination:=Range(bcol), Type:=xlFillDefault Next cell End Sub
-
Re: Convert Hypertext to Text
JohnDrew,
Your workbook VBA is password protected.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Code
Display MoreOption Explicit Sub GetLocation() ' Stanley D. Grom, 01/13/2011 ' [URL]http://www.ozgrid.com/forum/showthread.php?t=149718[/URL] Dim c As Range, s As String, Sp, Sp2 Application.ScreenUpdating = False Worksheets("Search_Results").Activate For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)) s = c.Formula Sp = Split(s, "//") Sp2 = Split(Sp(1), ".") c.Offset(, 1) = Sp2(0) Next c Application.ScreenUpdating = True End Sub
Then run the GetLocation macro.
I would really like to do this with RegExp.
Have a great day,
Stan
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!