Removing Letters From Strings

This is a quick and dirty way I came up with to remove letters from a particular Excel cell via Macros. Just paste this into a module and call it like any other Excel formula by using RemoveText(cell).

I am a little rusty but I know it probably would have been more efficient to user Chars instead of a string array. : )

Public Function RemoveText(ByVal strInput As String) As String

    'Holds the alphabet for it to know what we don't want
    Dim arrAlphabet(26) As String

    'Temporary holding location while we scan through the input string
    Dim strTemp As String

    'Place to aggregate our output
    Dim strOutput As String

    arrAlphabet(0) = "A"
    arrAlphabet(1) = "B"
    arrAlphabet(2) = "C"
    arrAlphabet(3) = "D"
    arrAlphabet(4) = "E"
    arrAlphabet(5) = "F"
    arrAlphabet(6) = "G"
    arrAlphabet(7) = "H"
    arrAlphabet(8) = "I"
    arrAlphabet(9) = "J"
    arrAlphabet(10) = "K"
    arrAlphabet(11) = "L"
    arrAlphabet(12) = "M"
    arrAlphabet(13) = "N"
    arrAlphabet(14) = "O"
    arrAlphabet(15) = "P"
    arrAlphabet(16) = "Q"
    arrAlphabet(17) = "R"
    arrAlphabet(18) = "S"
    arrAlphabet(19) = "T"
    arrAlphabet(20) = "U"
    arrAlphabet(21) = "V"
    arrAlphabet(22) = "W"
    arrAlphabet(23) = "X"
    arrAlphabet(24) = "Y"
    arrAlphabet(25) = "Z"

    'Loop through each letter in the input string
    For x = 1 To Len(strInput)

        'Check that letter against the alphabet
        strTemp = Right(Left(strInput, x), 1)

        For y = 0 To 25
            'If it's a letter...
            If (strTemp = arrAlphabet(y) Or strTemp = LCase(arrAlphabet(y))) Then
             'Clear out our temporary hold variable
                strTemp = ""
            End If
        Next y

        'If it's not a number then concantonate it for the end result
        strOutput = strOutput & strTemp
    Next x

    RemoveText = strOutput

End Function

ETag Removal

The main purpose of ETags is for servers to validate whether there is a new version of the file they are requesting. The problem is that sites served on multiple servers are likely to have ETags that do not match. This problem exists for both IIS and Apache served sites. This mismatch is not important in a small site served by a single server. However it is suggested that ETags be removed on larger websites with multiple servers, this decreases header data and thus allows for a faster load. Leaving the mismatching ETag however is said to bog down busier sites. Below is a simple piece of code that shows you how to remove ETags, just add it to your .htaccess file.

FileETag None

How to get the MD5 Checksum of a file

MD5 Checksum is used to verify the integrity of a file

In other words to make sure a file posted by the author has not been changed by a third party.

To use this the author posts the file and the hash value he got from running a file checksum. The person downloading the file then runs a file checksum on the file when they have it on their computer to see if it matches the authors checksum. If it does, it has not been altered, if it doesnt it has. Read more on this on MD5 Wikipedia.org.

Here is how I got it working on my Windows 7 computer

  1. Download and extract the File Checksum Integrity Verifier utility package
  2. Move the fciv folder to where you please. Rename the file fciv.exe to checksum.exe to make things simpler later.
  3. Add it to the system path for calling it easily later from the command line. Type:
    set path=%path%;c:location_of_fciv_folder
    

And you are done! For additional information read the ReadMe.txt file included in Microsofts download.

Now lets try it by calling the .exe file as such:

checksum.exe [filename]

checksumwin

Update:

I’ve noticed that setting the system path doesn’t seem to be permanent but a per-command-line-window thing.

File Funnel

File Funnel is a small application I made that copies files of a specific extension from one directory and its sub-directories to another. It gives you the option to either copy just the files or the directory structure as well.

*It is built on VB.NET and requires the Microsoft .NET Framework so an additional download might be necessary. The application will direct you to the Microsoft download site for that.

Source @ GIT: https://github.com/cquinterox/File-Funnel

How the SumProduct Formula Works

The sum product formula is officially used for summing the product of
two or more columns in an array. Here is a simple example.

sumprod1

Our formula:

=sumproduct(A1:A2, B1:B2)

Really translates to:

=(A1+B1) * (A2+B2)

*notice row 1 is adding to row 1, and row 2 to 2

Because:

(5*6)+(10*3) = 60

It is pretty straight forward.

Mutiple Conditional

You can also use the sumproduct formula as a super useful multi-conditional countif or sumif formula. Check out the table and how the formula is used. Please note that I am using named arrays in order to make this as simple as possible.

sumprod2

Super Countif

=sumproduct(--(name_array= John),--(location_array= CA),--(has_a_dog_array="yes"))

The above use of the sumproduct formula will return the count of all the down owners in California that are named John. Cool isnt it? Very useful.

But what if you want to know the sum of the incomes of all the guys named John that live in California? Easy, add the range that has the income to the end of this. Like so.

Super Sumif

=sumproduct(--(name_array= John),--(location_array= CA),--(has_a_dog_array="yes"), --(income_array))

In this case the formula will match all the Johns that live in California and have a dog and tally up their incomes. So our result will be 123,000. You can also use conditionals if working with numbers.

Now I know the formula looks funny with all those negative signs but they are required as the formula will not work right if you omit them. In a couple of tests I have conducted I noticed that if the double negatives are removed the formula tries to multiply boolean (true/false) types instead of matching up what you are looking for. So though my observation of this is probably not ideal, remember the keep the double negatives in there for it to work right.

A very important thing to note is that when working with sumproduct formulas you must make sure your arrays are the same length. You cant have the name array be 100 rows and the location array be 95 rows high because it will cause an error.