Excel remove text before last "|" character in string

Question

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

Possible duplicate of How can I perform a reverse string search in Excel without using VBA? — Absinthe
– Absinthe, Commented May 7, 2017 at 11:39
I believe @user4039065's answer is the correct answer (and the shortest formula). — User1974
– User1974, Commented Jan 15, 2020 at 2:47

Uvu · Accepted Answer · 2017-05-07 11:35:23Z

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.

answered May 7, 2017 at 11:35

Uvu

771 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2017-05-07 13:15:44Z

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

answered May 7, 2017 at 13:15

user4039065

1 Comment

User1974 Over a year ago

I believe this is the correct answer (and the shortest formula).

Duke · Accepted Answer · 2017-05-07 12:40:28Z

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

answered May 7, 2017 at 12:40

Duke

1638 bronze badges

2 Comments

AQuirky Over a year ago

Perhaps this is a better design, but you are not addressing the question which was asked.

Duke Over a year ago

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

Keshav Kumar · Accepted Answer · 2021-07-08 12:09:01Z

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

answered Jul 8, 2021 at 12:09

Keshav Kumar

613 bronze badges

Collectives™ on Stack Overflow

Excel remove text before last "|" character in string

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related