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 Functions.zip; 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 to view full size
Newton Excel Bach, not (just) an Excel Blog 
Good Stuff,
YEah I do this too, a few UDF would help! I think I’ll nick those and bug them in my addin!
Thanks
Ross
LikeLike
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.
LikeLike
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
Maybe?
…mrt
LikeLike
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.
LikeLike
Doug –
Very cool. That’s the difference between when I do it and a pro does it.
…mrt
LikeLike