2

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

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.

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".

Uvu
  • 77
  • 1
  • 1
  • 8
  • Possible duplicate of [How can I perform a reverse string search in Excel without using VBA?](http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba) – Absinthe May 07 '17 at 11:39
  • I believe @user4039065's answer is the correct answer (and the shortest formula). – User1974 Jan 15 '20 at 02:47

4 Answers4

3

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

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

If someone think that something is missing, please comment.

Uvu
  • 77
  • 1
  • 1
  • 8
3

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)))

enter image description here

0

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.

Note:I inserted your picture string into A1

Column 1: Image #

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

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

Duke
  • 163
  • 8
  • Perhaps this is a better design, but you are not addressing the question which was asked. – AQuirky May 07 '17 at 18:28
  • It would if you use the offset. So if you use counta on either column 2 or 3, it will tell you how many pictures are in the string. You can then use offset to only grab the last picture or string while ignoring everything else – Duke May 07 '17 at 18:50
0

Col A - data to be processed

Col b - =TRIM(IF(ISERROR(FIND("-",A2)),"",MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))),255)))

Col c - =SUBSTITUTE(A5,B5,"")

Col c will be your output column

Keshav Kumar
  • 61
  • 3