如何使用EXCEL公式规范统一IPV6地址？

camelly

请问各位大佬，IPV6地址一般由32个字符组成，其中分成8组，每组由分割符”:"分割，每一组4个字符，如2408:8140:C0FF:FF40:2000:0019:0000:0002。目前存在的问题是，IPV6地址格式存在3种情况，想用EXCEL公式进行统一成标准格式，请各位大佬帮助
1、IP地址按照分割符”:"分割，不足8组（中间缺少分组），缺失的组按照0000补全；
2、每组4个字符，不足4个字符的前面补0；
3、每组中均应大写英文字母，不是大写字母的转成大写字母。

edwin11891

不足8组时，一定不是在首尾补齐，即0000总是在中间的连续两个冒号之间？

camelly

edwin11891 发表于 2024-3-12 18:25
不足8组时，一定不是在首尾补齐，即0000总是在中间的连续两个冒号之间？

是的，不足8组时，在中间两个冒号位置补齐

edwin11891

公式：A1为源数据单元格，实际不同时只需修改A1即可，下拉。

1. =LET(t,TEXTSPLIT(A1,,":"),z,ROWS(t),P,SUBSTITUTE(A1,"::",REPT(":",10-z)),Ts,TEXTSPLIT(P,,":"),Ty,TEXTJOIN(":",0,TEXT(IF(LEN(Ts)=0,0,Ts),"0000")),UPPER(Ty))

复制代码

edwin11891

edwin11891 发表于 2024-3-12 18:36
公式：A1为源数据单元格，实际不同时只需修改A1即可，下拉。

补充一下，需要WPS最新版（至少2023冬季更新版），或MS 365。

tmplinshi

1. =LET(
   
2.     expandIPv6, LAMBDA(str,
   
3.         LET(
   
4.             c, 9 - (LEN(str) - LEN(SUBSTITUTE(str, ":", ""))),
   
5.             str, SUBSTITUTE(str, "::", REPT(":", c)),
   
6.             arr, RIGHT("0000" & TEXTSPLIT(str, ":"), 4),
   
7.             UPPER(TEXTJOIN(":", , arr))
   
8.         )
   
9.     ),
   
10.     expandIPv6(A2)
    
11. )

复制代码

1. =UPPER(TEXTJOIN(":", , RIGHT("0000" & TEXTSPLIT(SUBSTITUTE(A2, "::", REPT(":", 9 - (LEN(A2) - LEN(SUBSTITUTE(A2, ":", ""))))), ":"), 4)))

复制代码

世界客都

最新版WPS即可

1. =TEXTJOIN(":",,UPPER(TEXT(TEXTSPLIT(SUBSTITUTE(A2,"::",REPT(":0000",8-LEN(A2)+LEN(SUBSTITUTE(A2,":",)))&":"),":"),"0000")))

复制代码

世界客都

edwin11891 发表于 2024-3-12 18:40
补充一下，需要WPS最新版（至少2023冬季更新版），或MS 365。

LET函数用的不错

edwin11891

世界客都 发表于 2024-3-12 21:09
LET函数用的不错

贻笑大方了！这样子思路原始、简单。

世界客都

edwin11891 发表于 2024-3-12 21:27
贻笑大方了！这样子思路原始、简单。

我才开始学习，要多向老师们请教