Excel删除字符串中最后一个“|”字符前的文本

I have some multiple images in Excel file which are separated with | For example:

我在Excel文件中有一些多个图像,用|分隔例如:

http://s3.supplyhouse.com/images/products/small/gt2700-15-3.jpg|http://s3.supplyhouse.com/images/products/small/gt2700-15-4.jpg|http://s3.supplyhouse.com/images/products/small/gt2700-15-1.jpg

I want to extract the last image from the cell.

我想从单元格中提取最后一张图像。

I need to configure =RIGHT formula that will remove all text before last | character.

我需要配置= RIGHT公式,它将删除最后一个|之前的所有文本字符。

In some cells there are | characters 3 times, somewhere, 2, somewhere 4. So I need to find the last one and delete all characters before it, I don't need something like "Find 3rd | character and remove everything before it".

在某些细胞中有|字符3次,某处,2,某处4.所以我需要找到最后一个并删除之前的所有字符,我不需要像“查找第3个字符并删除它之前的所有内容”之类的内容。

3 个解决方案

#1

I have combined some excel formulas and made this one which works:

我结合了一些excel公式,并使这个有效:

=RIGHT($D1,LEN($D1)-SEARCH("^^",SUBSTITUTE(D1,"|","^^",LEN(D1)-LEN(SUBSTITUTE(D1,"|","")))))

If someone think that something is missing, please comment.

如果有人认为缺少某些内容,请发表评论。

#2

To get just the image filename,

要获得图像文件名,

=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),LEN(A2)))

To get the full link,

要获得完整链接,

=TRIM(RIGHT(SUBSTITUTE(A2,"|",REPT(" ",LEN(A2))),LEN(A2)))

#3

Cleaner way to do this would be to have 1 formula to identify image breaks and then another formula to give the picture string. Then just use an offset function to capture the last image for your answer.

更清洁的方法是使用1个公式来识别图像中断,然后使用另一个公式来给出图片字符串。然后只需使用偏移功能捕获最后一个图像作为答案。

Note:I inserted your picture string into A1

注意:我将您的图片字符串插入A1

Column 1: Image #

第1列:图像#

Column 2: String for each picture =IFERROR(SEARCH(".jpg",$A$1),"")

第2列:每张图片的字符串= IFERROR(SEARCH(“。jpg”,$ A $ 1),“”)

Column 3: Text for picture: =IFERROR(LEFT($A$1,B4+3),"")

第3栏:图片文字:= IFERROR(左($ A $ 1,B4 + 3),“”)

#1