ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

3 Q & A posts tagged with hyperlink

Jan 18

What is the simplest way to extract text pattern in Excel spreadsheet?

I have a sheet of a few columns of URLs. I need to extract a text pattern (I.E. extracting an URL parameter) and then replace it with something else and place it into another link structure. What's the simplest way to do that in Microsoft Excel spreadsheet? Does Excel provide regular express?


Yes, sure
I do this all the time
Now, the link you have may look like this

If this link in cell A1, then paste this in B1
=LEFT(A1, SEARCH("?",A1)-1)

This will bring you the first part until the "?"

Then in another cell, say C1 type 5

Then in D1 paste this
=HYPERLINK( B1&"id="&C1, B1)

Now this link will have this result

So doing 6 in C2, 7 in C3, etc
Copy and paste D1 down to generate multiple hyperlinks to different locations based on your criteria

I did this as example, because you didn't give any details

try applying your criteria and see

VBAXLMan is here to feed your Excel needs in case you want more help

Sunday 1/18/2009 4:34:54 AM
Jan 15

Why won't Excel open when opening a Word merge document?

I have Microsoft Word file linked with Microsoft Excel to merge. Used to be prior versions would automatically open up both files in it's respective windows (Word & Excel), but using Microsoft 2003, it just opens the Word document and when I try to manually open the Excel file (double click the icon) it says I'm already in it. Is there a way to automatically open both files by opening the Word file?


Excel have the feature of opening more than one workbook by double click on one Icon (It is called Workspace)
Not long ago, Office used to have Microsoft Binder, that enables you to put bunch of Office files (like Excel, Access, PowerPoint, etc) in one file called Binder.
But not anymore

The way you are trying to do, I don't think they care much about it now

I would suggest that you do one of the solutions below:
1- Create a hyperlink in one doc to open another (like hyperlink in Word to open Excel file)
2- Or, Do bunch of shortcuts in one folder to open all you files, select them all, and press enter, to open all those files
3- Or, may do some old fashion bat file.

I can not see any other way


Thursday 1/15/2009 2:02:12 PM
Jan 18

How do you conditional format a hyperlink in excel?

trying to hide links where data doesnt exist yet with white font


You will need to use HYPERLINK function, (Not Insert Hyperlink)
along with IF function.
It is somthing like that
=IF( A1=1, "", HYPERLINK( "destination"))
So when cell A1 equals 1, then this cell will show nothing, but when A1 equals anything but 1, this cell will show the hyperlink you specify

Enjoy my profile, I am the VBAXLMan

Thursday 1/18/2007 7:59:31 AM