Sub string in excel

Question

I would like to substring tow strings from tow different columns

How the first string in column A1 Looks like: A76 Transfer Conditions for Tools and Equipment

How the string in the column B1 Looks like: Documents/Z_Documentation/PDF/Circular-A76.pdf

What I would like to have is

From the second string the last bit after the last /

=LEFT(B1,FIND("@",SUBSTITUTE(B1,"/","@",LEN(B1)-LEN(SUBSTITUTE(B1,"/","")))))

And from the first string I would like to have the Hole thing

So the string at the and should look like: Circular-A76.pdf | A76 Transfer Conditions for Tools and Equipment

Thanks for help

PaichengWu · Accepted Answer · 2016-06-29 10:16:15Z

1

Write in C1 cell with =RIGHT($B1,SMALL(IF(MID($B1,LARGE(ROW(INDIRECT("1:"&LEN($B1))),ROW(INDIRECT("1:"&LEN($B1)))),1)="/",ROW(INDIRECT("1:"&LEN($B1))),""),1)-1) & " | " & $A1

then press Ctrl + Shift + Enter

You will get

edited Jun 29, 2016 at 10:16

answered Jun 29, 2016 at 10:09

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Sub string in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related