Excel remove text before last "|" character in string

2
votes

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

4
excelexcel-formula
Possible duplicate of How can I perform a reverse string search in Excel without using VBA? - Absinthe
I believe @user4039065's answer is the correct answer (and the shortest formula). - User1973

4 Answers

3
votes

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.

3
votes

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
votes

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

0
votes

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