Some string functions

It was pointed out in a comment to this post at Daily Dose of Excel that VBA has a StrReverse function that will (logically enough) reverse a string.  That’s not awfully useful to me (except maybe for Project Euler), but it did set me thinking that I spend a fair amount of time writing lengthy formulas in Excel to extract bits of strings, which could be done with much less typing with a few simple User Defined Functions.

So here they are; download from String; full open source code is in the download file.

(Now updated as suggested by Dick K in the comments with a MidW() function, and an optional delimiter parameter)

Functions are:

leftw(); Return the first word(s) from a string

rightw(); Return the last word(s) from a string

Midw(); Return words(s) from the middle of a string

FINDrev(); Find from the right hand end

Leftval(); Extract a value from the left end of a string

Rightval(); Extract a value from the right end of a string

Reverse(); Reverse a string

Screenshot with more details:

Click for full size view

Click to view full size

This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

5 Responses to Some string functions

  1. Ross says:

    Good Stuff,
    YEah I do this too, a few UDF would help! I think I’ll nick those and bug them in my addin!


  2. Next version, do a MIDW(ref, wordnum) to get the nth word from ref.

    The worst part of string functions is nested FINDs. Maybe MIDW should be

    =MIDW(ref, wordnum, [delim])

    where I can tell it what the delimeter is – default to space.


  3. mrt says:

    Dick, Doug –

    Function MIDW(ref, wordnum, Optional delim) As String
    Dim Result As Variant
    If IsMissing(delim) Then delim = Chr(32)
    Result = Split(ref, delim)
    MIDW = Trim(Result(wordnum – 1))
    End Function




    • dougaj4 says:

      Dick – done

      Michael – thanks. I added the space as the default value for delim and deleted the If..Then, and added an optional numwords parameter and a loop. Here’s what I ended up with:

      Function MidW(MString As String, WordNum As Long, _
      Optional NumWords As Long = 1, Optional Delim As String = ” “) As String
      Dim Result As Variant, i As Long

      Result = Split(MString, Delim)
      WordNum = WordNum – 1
      MidW = Trim(Result(WordNum))
      If NumWords > 1 Then
      If WordNum + NumWords – 1 > UBound(Result) Then NumWords = UBound(Result) – WordNum + 1
      For i = 1 To NumWords – 1
      MidW = MidW & Delim & Trim(Result(WordNum + i))
      Next i
      End If
      End Function

      Ross – hope you find them useful.


  4. Michael says:

    Doug –

    Very cool. That’s the difference between when I do it and a pro does it.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s