Turning Inches into Feet and Inches -- even fractions of an inch...

Turning inches into feet is simple math... but why not have a function that does it and the output looks better than if I did it by hand.   Given an integer that represents inches this little Excel VBA function will output feet and inches.

=====================code below===============================
Public Function IntoFeet(ininches As Double) As String
Dim feet As Integer
Dim inches As Double
    feet = Int(ininches / 12)
    inches = ininches - (feet * 12)
    IntoFeet = feet & "' " & Trim(Excel.WorksheetFunction.Text(inches, "#-?/?")) & """" 'formats thte output including fractions
End Function
=================================================================


I'm sure there are a lot of things I can do to improve this function, but if you have some ideas, drop me a comment!

Thanks-

Super Tools for Learning Regular Expression

Today I'm not going to give you any function code, rather I'm going to give you a list of resources.  Places I've collected that are a good reference for learning and using regular expression.


Sites on Regular Expressions
regular-experssions.info 
RegExp


User Guides
Wikipedia Overview - Good Starting place to learn anything
Microsoft on Regular Expression
Grymoire Navigation
Oracle Java on Regular Expression
zytrax Regular Expression User Guide

Guide to Regular Expression
Excel and Regular Expression
Mozilla and Regular Expressions Mostly Java but the principals are the same


Learning
code project 30-minute tutorial
W3C Schools  - They teach it in Java, but the most of the principles apply to Excel and VBA



Tools for Generating RE
The best Regular Expression Generating tool online - I love this tool,  put in the text expression - and it will help you break it down into it's parts, while generating the code!   Very cool!


Testing your Regular Expression:
https://regex101.com/
Desinger 215 RegExp Tester
RegEx Evaluator

Find Using Regular Expression in Excel - New Fuction

I've found that regular expression is one of the most useful tools in data manipulation. Being able to pass a regular expression rule and return the first instance of that expression is a powerful function.    I'm not going to get into depth on how regular expression works, there are tons of great resources on line that can help you learn how to write the expressions.    I might to into some depth later on other tools I use online, but here I'm just going to give you the VBA function that will expose the power of regular expression into your excel spreadsheet.  

Here is the code:
'-----------------------------------------------FRE---------------------------------------
Public Function FRE(StringIn As String, strPattern As String) As String
  Dim r As RegExp
    Set r = New RegExp
      With r
            .Global = True
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern
        End With
   Set m = r.Execute(StringIn)
   FRE = Trim(m(0))
End Function
'------------------------------------------------------------------------------------------------

I named this function FRE - simply because I use it a ton, and didn't want to type some totally expressive name, - FRE is Find using Regular Expression.

There are two parts - the Stringin - which is the string your searching in, and strPattern which is the regular Expression Pattern.   If this function finds anything, it's only going to return the first instance.   I'm sure we can write better functions for returning arrays, making ranges, etc.   This is just dirt simple. 

Here is an example of how you could use this function -  you have a cell that has a lot of text mixed with phone numbers, but you just want to return the phone numbers from that cell, this function will return the first phone number.

Lets Say A1 = "Joe Smith (456) 456-8456 josmith@google.com"
to return the phone number, the function would look like this FRE(A1,"(\(\d{3}\).*\d{3}.*\d{4})")

And the return would be - (456) 456-8456


Super Simple.    I hope this will help somebody out there!

Join Function for mutlple cells

I've found that joining a range of cells together into a single delimited field is really useful.  Excel in the Data tab has the ability to take delimited text in a single column and move it into multiple columns, but this little function goes the other way.

Here is the code:
===========================================================
Public Function Jointext(r As Range, del As String) As String

Dim b As String
For a = 1 To r.Count
    If IsEmpty(r.Value2(1, a)) = False Then
    b = b & r.Value2(1, a) & del
    End If
Next
Jointext = Left(b, Len(b) - Len(del))
End Function
==============================================================

It's very simple,  I'm iterating a range array and putting the values into a single string delimited by the del string.  Then after that, I'm removing the last delimiter.

Simple

Solving the Plural Singular Issue with Code

In the process of creating a script generation application, I realized that phrase keywords can be either singular or plural, and in excel, there is no easy way to tell the difference between words.   That gave me an idea... a function that would figure out if something is singular or plural.  This way I would know that the article to use after a keyword phrase would be "is" if singular or "are" if plural.

English is fairly consistent with plural and singular, with "s" being the key... but not always.  Think of words like bus or lens that both end with "s", these are singular not plural.   And how about when a keyword phrase includes "with" like a "The bus with a red door"?   The correct way to phrase this is "The bus with the red door is big", so it's singular making the noun to identify being the word before the word "with".   My code is below:

---

Public Function isitplural(t As String) As Boolean

Dim w() As String
singwords = Array("bus", "lens", "news", "billiards", "Brussels", "United States", "mumps", "measles", "acoustics", "aerobics", "aerodynamics", "aeronautics", "athletics", "classics", "economics", "electronics", "genetics", "linguistics", "logistics", "mathematics", "mechanics", "obstetrics", "physics", "politics", "statistics", "thermodynamics", "GAMES", "billiards", "bowls", "cards", "darts", "ILLNESSES", "diabetes", "measles", "mumps", "rabies", "draughts", "skittles", "rickets", "shingles")
pluralwords = Array("alumnae", "alumni", "antennae", "bacteria", "bureaux", "cacti", "cherubim", "children", "criteria", "curricula", "data", "days-off", "deer", "dice", "die", "editors-in-chief", "feet", "fish", "foci", "fora", "formulae", "fruit", "fungi", "geese", "hippopotami", "kine", "lice", "media", "men", "mice", "mothers-in-law", "nautili", "nuclei", "octopi", "octopus", "oxen", "people", "phenomena", "salmon", "sheep", "stadia", "stimuli", "stimulus", "supernovae", "syllabi", "teeth ", "tuna", "women", "woodlice")
w() = Split(t, " ")
d = 0
a = w(UBound(w)) 'last word
For Each c In w
If c = "with" Then
   a = w(d - 1)
   Exit For
End If
d = d + 1
Next


'check if ends with s
If LCase(Right(a, 1)) = "s" Then 
'it might be plural, but need to check plural words
isitplural = True
For Each b In singwords
    If LCase(a) = LCase(b) Then 
' it's probably not pural but a proper noun that ends with s
        isitplural = False   
'its one of those singular words like lens or bus
        Exit Function
    End If
Next
Else
isitplural = False 
'it still might be plural... check the singwords
For Each b In pluralwords
If LCase(a) = LCase(b) Then
    itisplural = True 'it is one of those weird plural words
    Exit Function
    End If
Next
End If

End Function

---

I'm still looking for words to add to my array of singular rule breakers or singular words that end with an 's', and I'm still looking for those plural rule breakers... the ones that end with an 's' and aren't plural.