Excel VBA: How to extract the substring if we know the positions of first and last letter in string

Question

how can we extract a substring from a string if we give the positions of first and last letter in string?

Dim my_string As String
Dim my_substring As String
my_string="Excel/Sheet1.xls"

instrrev(my_string,"/") gives 6 (start position)

len(my_string) gives 16 (end position)

So to get "Sheet1.xls" as my substring what should we do?

Or we can get it selecting characters till "/" is met first/

use mid function. reference: https://msdn.microsoft.com/en-us/library/05e63829(v=vs.90).aspx — Raghavendra, Jun 30 '15 at 10:05
I don't think you'll do any better than the answers provided [here](http://stackoverflow.com/a/1755577/2071807) — LondonRob, Jun 30 '15 at 10:13
LondonRob. Yes . Similar. But this deals with start and end positions of string. — Abdul Shiyas, Jun 30 '15 at 10:19

score 4 · Answer 1 · answered Jun 30 '15 at 10:03

4

How about this one?

my_substring = Split("Excel/Sheet1.xls","/")(1)

answered Jun 30 '15 at 10:03

Tom

747
5
16

1

This won't do what you expect it to do if there is more than one `/` in the string. Why not change it a bit to store the `Split` in a `Variant`, then get the *last* element with `UBound`? – LondonRob Jun 30 '15 at 10:11
May I suggest this slight improvement: `Split(strFileName, "/")(UBound(Split(strFileName, "/")))` – Ralph Jun 30 '15 at 10:17
Ralph, in that case I would consider putting the array in a variant variable so that the split function does not have to execute twice. But it is true that my suggestion would not work with more than one /. It is even advisable to check if there are any.... – Tom Jun 30 '15 at 10:29

score 2 · Accepted Answer · edited Jun 30 '15 at 10:15

2

Mid(my_string,instrrev(my_string,"/")+1, len(my_string))

would give you Sheet1.xls.

edited Jun 30 '15 at 10:15

Abdul Shiyas

401
3
9
30

answered Jun 30 '15 at 10:05

waka

3,362
9
35
54

1

Neat solution. I didn't know about `InStrRev`. I just learned something new. Thanks. – Ralph Jun 30 '15 at 13:35

score 1 · Answer 3 · answered Jun 30 '15 at 10:08

How about this solution:

Dim strFileName As String
strFileName = ActiveWorkbook.Path & "/" & ActiveWorkbook.Name
Debug.Print "Start Position: " & Len(strFileName) - InStr(1, StrReverse(strFileName), "/")
Debug.Print "Extacted file name: " & Mid(strFileName, Len(strFileName) - InStr(1, StrReverse(strFileName), "/") + 2)

Excel VBA: How to extract the substring if we know the positions of first and last letter in string

3 Answers3