String manipulation Excel function

Keep the combination of Excel functions as a memorandum.

Get the class name from FQCN

=RIGHT(A1,LEN(A1)-FIND("★",SUBSTITUTE(A1,".","★",LEN(A1)-LEN((SUBSTITUTE(A1,".",""))))))

result


A1 cell sample.group1.StrClass
Result StrClass

Get the filename from the full path

=RIGHT(A1,LEN(A1)-FIND("★",SUBSTITUTE(A1,"\","★",LEN(A1)-LEN((SUBSTITUTE(A1,"\",""))))))

result


A1 cell C:\vbaSample\sample\test.txt
Result test.txt

Get directory from full path (no trailing )

=LEFT(A1,FIND("★",SUBSTITUTE(A1,"\","★",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),1)-1)

result


A1 cell C:\vbaSample\sample\test.txt
Result C:\vbaSample\sample

Get the directory from the full path (with \ at the end)

=LEFT(A1,FIND("★",SUBSTITUTE(A1,"\","★",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),1))

result


A1 cell C:\vbaSample\sample\test.txt
Result C:\vbaSample\sample\

Recommended Posts

String manipulation Excel function
Reflection on Java string manipulation
String