PDA

View Full Version : Extract substring



copyt
06-11-2012, 10:59 PM
Hi all, I need to extract a substring "xl2122_q2.pkl" from

E:\Lab\XL FILES\RNAP_HS_PKL\RNAP_HS_PKL\xl2122_q2.pkl

Could anybody show how to do that?

Anyhelp would be appreciated.

Thanks,:bow:

Trebor76
06-11-2012, 11:29 PM
Hi copyt,

Change the cell reference (i.e. from cell A3 to where the string is actually housed) in the following:

=RIGHT(A3,LEN(A3)-FIND("~",SUBSTITUTE(A3,"\","~",LEN(A3)-LEN(SUBSTITUTE(A3,"\","")))))

HTH

Robert

mikerickson
06-11-2012, 11:41 PM
=TRIM(RIGHT(SUBSTITUTE(A1, "\", REPT(" ",255)),255)

copyt
06-12-2012, 02:38 AM
:bow: Hello Trebor76 (http://www.vbaexpress.com/forum/member.php?u=21682) and mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706), Thank you very much

Trebor76
06-12-2012, 02:48 AM
Hi copyt,

Thanks for the feedback and on behalf of mikerickson and myself, you're welcome ;)

snb
06-12-2012, 03:11 AM
using a UDF:


Function last_element(c01, c02)
last_element = Split(c01, c02)(UBound(Split(c01, c02)))
End Function


If cell A1 contains the string "E:\Lab\XL FILES\RNAP_HS_PKL\RNAP_HS_PKL\xl2122_q2.pkl" then put in A2 the formula



=last_element(A1,"\")