Re: Select specific text in cell

On Fri, 15 Feb 2008 23:00:40 -0500, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote:

The above as a "one-liner" in deference to Rick:

LOL

============================================
Function fn(str As String) As String
fn = Trim(Split(Split(str, "\")(UBound(Split _
(str, "\"))), "-")(LBound(Split(Split _
(str, "\")(UBound(Split(str, "\"))), "-"))))
End Function
==========================================

The LBound for a Split is always 0 no matter what the Option Base is set to.
Using this fact, your one-liner can be simplified considerably...

Function fn(str As String) As String
fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0))
End Function

Rick

Actually, neither your one liner nor my longer variants will work if filename
includes a "-". For that, we need something like:

==============================
Option Explicit
Function fn(str As String) As String
Dim s1() As String
s1 = Split(str, "\")
s1 = Split(s1(UBound(s1)), "-")
ReDim Preserve s1(UBound(s1) - 1)
fn = Trim(Join(s1, "-"))
End Function
===========================
--ron
.

Relevant Pages

  • Re: Split Function Help
    ... >> I realize this thread is dealing with arrays created with the Split ... I'm not sure its a big deal; but I was concerned that some Option Base 1 ... > ReDim inArrAs String ... > IMO this is messy code though which is why I defaulted to a low bound ...
    (microsoft.public.vb.general.discussion)
  • Re: Arrays
    ... I always use Option Base 1 because looping from 1 to ... My functions (mostly string and string arrays) return empty strings or empty ... Or you could: ReDim CloseChanges ...
    (microsoft.public.excel.programming)
  • Re: Option Base 1
    ... Private Function VDate(strMD As String) As Integer ... Dim ArAs String ... OPTION BASE only sets the default lower bound when ... I always use the standard 0 based arrays OR I ...
    (microsoft.public.access.formscoding)
  • Re: =?iso-8859-1?Q?Excel2003:_mit_VBA_alle_Bl=E4tter_markieren?=
    ... Dim ArrAlleBlaetterAs String ... ReDim ArrAlleBlaetter ... MsgBox strBlaetter ... Option Base 1 stellt es so ein, dass der erste Index des Array 1 ist, ...
    (microsoft.public.de.excel)
  • Re: VBA Split-function
    ... Dim sFormula As String ... > I want to use the Split function in a custom made function and want the> output to be used in a main procedure. ... > I have tried placing "OPTION BASE 1" at the top of my procedure, but it> seems that the "Split"-function can only return an array with BASE 0. ...
    (microsoft.public.excel.programming)
Loading