excel - Extracting text from string between two identical characters using VBA -

let's have following string within cell:

e. stark, t. lannister, a. martell, p baelish, b. dondarrion, , j. mormont. increased levels of nudity across westeros contributes sporadic seasonal climate. nat. proc. aca. sci. (2011) 3: 142-149.

and want extract title this. approach considering write script says "pull text string, if more 50 characters long." way returns title, , not stuff " stark, t" , " martell, p". code have far is:

sub titletest()     dim txt string     dim output string     dim integer     dim rng range     dim j integer     dim k integer      j = 5     set rng = range("a" & j) 'text in cell a5     txt = rng.value 'txt string     = 1      while j <= 10 'there 5 references between a5 , a10     k = instr(i, txt, ".") - instr(i, txt, ". ") + 1 'k supposed length of string returned, can't differenciate 1 "." other.      output = mid(txt, instr(i, txt, "."), k)             if len(output) < 100                 = + 1             elseif len(output) > 10                 output = mid(txt, instr(i, txt, "."), instr(i, txt, ". "))                 range("b5") = output                 j = j + 1             end if     wend end sub 

of course, work if wasn't 2 "." trying full information from. there way write instr function in such way won't find same character twice? going in wrong way?

thanks in advance,

edit: approach might work (if possible), if have 1 character " any lower case letter." , ".". possible? can't find example of how achieved...

here go, works wish. judging code sure can adapt needs quite quickly:

option explicit  sub extracttextsub()      debug.print extracttext("e. stark, t. lannister, a. martell, p baelish, b. dondarrion, , j. mormont. increased levels of nudity across westeros contributes sporadic seasonal climate. nat. proc. aca. sci. (2011) 3: 142-149.")  end sub  public function extracttext(str_text string) string      dim arr         variant     dim l_counter   long     arr = split(str_text, ".")      l_counter = lbound(arr) ubound(arr)          if len(arr(l_counter)) > 50             extracttext = arr(l_counter)         end if      next l_counter  end function 

edit: 5 votes in no time made me improve code bit :) return longest string, without thinking of 50 chars. furthermore, on error handlaer , constant point. plus adding point end of extract.

option explicit  public const str_point = "."  sub extracttextsub()      debug.print extracttext("e. stark, t. lannister, a. martell, p baelish, b. dondarrion, , j. mormont. increased levels of nudity across westeros contributes sporadic seasonal climate. nat. proc. aca. sci. (2011) 3: 142-149.")  end sub  public function extracttext(str_text string) string      on error goto extracttext_error      dim arr             variant     dim l_counter       long     dim str_longest     string      arr = split(str_text, str_point)      l_counter = lbound(arr) ubound(arr)          if len(arr(l_counter)) > len(extracttext)             extracttext = arr(l_counter)         end if      next l_counter  extracttext = extracttext & str_point  on error goto 0 exit function  extracttext_error:  msgbox "error " & err.number & err.description   end function