Hi team,
How to manipulate IPV6 addresses last and second last octet with any Excel formula or function .
Second last octet with = 444
last two digit of last octet with = 05
Eg.
1505:800:1:300:9:1:105:1106 ==> 2405:800:1:300:9:1:444:1105
1505:80:12:900:9:1:562:2ba ==> 2405:80:12:900:9:1:444:205
1505:800:142:600:9:1:895:17 ==> 2405:800:142:600:9:1:444:05
Last edited by ganesh_6663; 03-30-2019 at 12:25 PM.
Please Login or Register to view this content.
1505:800:1:300:9:1:105:1106 1505:800:1:300:9:1:444:1105 B2: =MaskIPV6(A2, "::::::0444:xx05") 1505:80:12:900:9:1:562:2ba 1505:80:12:900:9:1:444:205 1505:800:142:600:9:1:895:17 1505:800:142:600:9:1:444:5
Last edited by shg; 03-30-2019 at 01:46 PM.
Entia non sunt multiplicanda sine necessitate
IP in A1
Please try at B1
=REPLACE( REPLACE(A1,FIND(" ",SUBSTITUTE(A1,":"," ",7))-3,3,444),LEN(A1)-1,2,"05")
Thank you very much, for some ip giving wrong output .
1405:200:142:300:7:2:903:3 ==> 1405:200:142:300:7:2:88803
Originally Posted by shg
Please Login or Register to view this content.
1505:800:1:300:9:1:105:1106 1505:800:1:300:9:1:444:1105 B2: =MaskIPV6(A2, "::::::0444:xx05") 1505:80:12:900:9:1:562:2ba 1505:80:12:900:9:1:444:205 1505:800:142:600:9:1:895:17 1505:800:142:600:9:1:444:5
Thanks , But not getting output for this code . How to use it.
It's a user-defined function.
1. Copy the code from the post
2. In Excel, press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel
Then insert the formula as shown.
Last edited by shg; 03-30-2019 at 05:13 PM.
Please try
=REPLACE( REPLACE(A1,FIND(" ",SUBSTITUTE(A1,":"," ",7))-3,3,444),LEN(A1)-1+(LEN(A1)-FIND(" ",SUBSTITUTE(A1,":"," ",7))<2),2,"05")
Really thanks for your help.
Done as u said , ut still not work.Here excel link , if possible please check .
--------------------------------------------------------
https()ufile.io(forward slash)slpbg
Thank you very much , it work fine
Attached ...
Last edited by shg; 03-31-2019 at 03:48 PM.
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
This formula work fine
=REPLACE( REPLACE(B9,FIND(" ",SUBSTITUTE(B9,":"," ",7))-3,3,888),LEN(B9)-1+(LEN(B9)-FIND(" ",SUBSTITUTE(B9,":"," ",7))<2),2,"03")
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks