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
Excel IP in Cell convert to URL ?
Re: Excel IP in Cell convert to URL ?
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
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
Please use [code][/code] when posting code!
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Re: Excel IP in Cell convert to URL ?
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.
I was hoping there was something AHK could do.. Scan the open xlsx document and change the values.
Re: Excel IP in Cell convert to URL ?
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
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
Please use [code][/code] when posting code!
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Re: Excel IP in Cell convert to URL ?
Thanks for this.
Find using *.*.*.* works, but replace using http://*.*.*.* fills the cells with http://*.*.*.* and not http://1.1.1.1 etc.
Find using *.*.*.* works, but replace using http://*.*.*.* fills the cells with http://*.*.*.* and not http://1.1.1.1 etc.
Re: Excel IP in Cell convert to URL ?
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
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
Please use [code][/code] when posting code!
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Re: Excel IP in Cell convert to URL ?
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
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
Re: Excel IP in Cell convert to URL ?
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
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
Please use [code][/code] when posting code!
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Excel IP in Cell convert to URL ?
Below is an AHK COM solution:
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
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)
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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: Excel IP in Cell convert to URL ?
Not bad!
Please use [code][/code] when posting code!
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Keyboard: Logitech G PRO - Mouse: Logitech G502 LS - OS: Windows 10 Pro 64 Bit - AHK version: 1.1.33.09
Who is online
Users browsing this forum: rc76 and 221 guests