Excel IP in Cell convert to URL ?

[TomT]

Hi

I spend a lot of time in Excel looking at clients details. In some cells are IP Addresses linking to their remote sites.
Each spreadsheet will contain hundreds of IP Addresses, some which will already have a URL associated with them.

Is there anyway AHK can detect the IP addresses in Excel and then convert that to a URL ?
ie: 192.168.1.150 becomes and active hyperlink to http://192.168.1.50/

Thanks

[Scr1pter]

Maybe you could use search and replace (Excel standard function).
Search for:
192.
and replace it by:
http://192.

However, I don't know how many different ip addresses you have (I'm speaking about the first three digits)

Regards

[TomT]

That would do it but the initial octet varies a lot..
I was hoping there was something AHK could do.. Scan the open xlsx document and change the values.

[Scr1pter]

What about this?
Search:
*.*.*.*
Replace by:
http://*.*.*.*

Then it wouldn't matter if the IP address is 1.2.3.4, or 192.168.1.1. or 41.222.1.555

P.S: Also done directly in Excel with Search and Replace.

Regards

[TomT]

Thanks for this.

Find using *.*.*.* works, but replace using http://*.*.*.* fills the cells with http://*.*.*.* and not http://1.1.1.1 etc.

[Scr1pter]

Hmm.
Well, do you have Notepad++ installed?
If yes, copy the whole column and use search and replace (RegEx - 'Reguläre Ausdrücke' on the screenshot):
Search:
(.*)\.(.*)\.(.*)\.(.*)

Replace by:
http://\1.\2.\3.\4

This is the result for:
1.2.3.4
192.168.1.0
72.48.7.6666


Regards

[TomT]

Hi
Thanks for your suggestion. I do use notepad++ and this does work.
I was hoping to find a way to get AHK to apply this to any xls as I open it.

Thanks

[Scr1pter]

Hi,
Probably you will have to use AHK RegExMatch (not sure if StringReplace will also work).
But I don't know how easy this will be, especially if we're talking about many lines.

However, this doesn't mean you don't have to use AHK at all - for this purpose.
You could create an AHK script which does the following steps::
- copies the whole Excel column and removes tab characters
- opens Notepad++
- creates a new document
- pastes the complete colums
- executes the search and replace
- marks and copies all lines
- closes document and closes Notepad++
- pastes everything into Excel.

I mean this sounds like big work, but it doesn't take long time to create that script.
If you agree with this solution (and if no one helps you with RegExMatch or StringReplace),
I can support you in case you have any problem.

General advice:
Only the result matters.
If you face a problem which can be solved by automation, don't try to focus on just 1 way.

Regards

[FanaticGuru]

Below is an AHK COM solution:

Code: Select all

xlApp := ComObjActive("Excel.Application")
for Cell in xlApp.ActiveSheet.UsedRange.Cells
	if RegExMatch(Cell.Value2, "\d*\.\d*\.\d*\.\d*", IP)
		xlApp.ActiveSheet.Hyperlinks.Add(Cell, IP,,, "http://" IP)

This will loop through all the used cells of the active worksheet and look for an IP pattern. It will then create a hyperlink from that information.

The IP address is really all that matters. You can make it display anything, ie. it can be "http://123.456.124.124" or it could display "Click here to open 123.456.124.124".

The pattern recognition is pretty crude and could be refined if you have problems with false matches.

FG

[Scr1pter]

Not bad!