循环提取指定数据，且放入同一个单元格

长春鱼鱼

A1数据如下。更多例子请见附件：

1）如何把单元格里面的图片地址(.jpg)提取出来，且放到单元格B1，并用逗号隔开？重复的值只提取一次。
例子中，B1的值最后应该是：
f835b022-2c84-489b-91c4-4c951b4fbe2d.jpg,38ecb32f-efe0-47af-8d9c-2679f6a60f46.jpg,77f7aca8-87e7-48cd-8ed2-d94b5b12f125.jpg,04a6f879-58ad-4247-85ae-438ac62db3e5.jpg

2）如何把第3个冒号:后，第一个双引号里面的内容（红色标出）依次提取出来，且放到单元格C1，并用逗号隔开？重复的值只提取一次。
例子中，C1的值最后应该是：
173,175,10#Rhodo,366

3）如何把第3个冒号:后，" "" "前的内容提取出来（红色标出），且放到相应图片的后面，用::隔开。重复的值只提取一次。所有值放到一个单元格D1.
例子中，D1的值最后应该是：
f835b022-2c84-489b-91c4-4c951b4fbe2d.jpg::173,38ecb32f-efe0-47af-8d9c-2679f6a60f46.jpg::175,77f7aca8-87e7-48cd-8ed2-d94b5b12f125.jpg::10#Rhodo,04a6f879-58ad-4247-85ae-438ac62db3e5.jpg::366
4) 如何把第4个冒号:后，}前的数据提取出来（绿色标出），且放到单元格E1里，用逗号隔开。重复的值只提取一次。
例子中，E1的值最后应该是：361385,361386,100014064,100014065

单元格A1数据：
"{""skuArray"": [{""价格"":""3.99"",""属性"":{""14"":""173"",""5"":""361385""},""大图14"":""f835b022-2c84-489b-91c4-4c951b4fbe2d.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""173"",""5"":""361386""},""大图14"":""f835b022-2c84-489b-91c4-4c951b4fbe2d.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.39"",""属性"":{""14"":""173"",""5"":""100014064""},""大图14"":""f835b022-2c84-489b-91c4-4c951b4fbe2d.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""173"",""5"":""100014065""},""大图14"":""f835b022-2c84-489b-91c4-4c951b4fbe2d.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""175"",""5"":""361385""},""大图14"":""38ecb32f-efe0-47af-8d9c-2679f6a60f46.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""175"",""5"":""361386""},""大图14"":""38ecb32f-efe0-47af-8d9c-2679f6a60f46.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""175"",""5"":""100014064""},""大图14"":""38ecb32f-efe0-47af-8d9c-2679f6a60f46.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""175"",""5"":""100014065""},""大图14"":""38ecb32f-efe0-47af-8d9c-2679f6a60f46.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""10#Rhodo"",""5"":""361385""},""大图14"":""77f7aca8-87e7-48cd-8ed2-d94b5b12f125.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""10#Rhodo"",""5"":""361386""},""大图14"":""77f7aca8-87e7-48cd-8ed2-d94b5b12f125.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""10#Rhodo"",""5"":""100014064""},""大图14"":""77f7aca8-87e7-48cd-8ed2-d94b5b12f125.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""10#Rhodo"",""5"":""100014065""},""大图14"":""77f7aca8-87e7-48cd-8ed2-d94b5b12f125.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""366"",""5"":""361385""},""大图14"":""04a6f879-58ad-4247-85ae-438ac62db3e5.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""366"",""5"":""361386""},""大图14"":""04a6f879-58ad-4247-85ae-438ac62db3e5.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""366"",""5"":""100014064""},""大图14"":""04a6f879-58ad-4247-85ae-438ac62db3e5.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""},
{""价格"":""3.99"",""属性"":{""14"":""366"",""5"":""100014065""},""大图14"":""04a6f879-58ad-4247-85ae-438ac62db3e5.jpg"",""库存"":""999"",""商家编码"":""Fancy1081""}]}"

长春鱼鱼

忘了附上更多例子。

hhjjpp

test-h.rar (17.98 KB, 下载次数: 13)

2015-7-8 15:32 上传

点击文件名下载附件

cmxxccxx

又要提取又要去重复，这样的东西还是用自定义函数吧，见附件 SAMPLE.zip (14.69 KB, 下载次数: 16)

2015-7-8 15:12 上传

点击文件名下载附件

长春鱼鱼

hhjjpp 发表于 2015-7-8 14:53

好复杂的公式阿，分很多步呢。

不过，最关键的是，最后的数据，重复的没有删掉。

长春鱼鱼

cmxxccxx 发表于 2015-7-8 15:12
又要提取又要去重复，这样的东西还是用自定义函数吧，见附件

能否贴下代码？打开了文档，代码是空的哦

多谢！

cmxxccxx

长春鱼鱼 发表于 2015-7-8 16:06
能否贴下代码？打开了文档，代码是空的哦

多谢！

自定义函数

1. Function aa(r As Range)
   
2. s = ""
   
3. ar = Split(r, ":")
   
4. For i = 0 To UBound(ar) Step 1
   
5.    br = Split(ar(i), ",")
   
6.    For j = 0 To UBound(br)
   
7.       If InStr(br(j), ".jpg") > 0 And InStr(s, br(j)) = 0 Then s = s & "," & br(j)
   
8.    Next
   
9. Next
   
10. aa = Mid(s, 2)
    
11. End Function
    

复制代码

Mr宋

cmxxccxx 发表于 2015-7-8 16:08
自定义函数

看着好厉害啊啊，什么时候我也可以这样啊

长春鱼鱼

cmxxccxx 发表于 2015-7-8 16:08
自定义函数

B1 值正确，多谢！

能否帮忙写下 C1 和 D1 的函数？我还看不懂代码，自己在你提供代码基础上改改，一直都不对。

多谢哈

长春鱼鱼

有没有人帮忙看下单元格C1, D1, E1 数据怎么弄啊？

多谢呢！