Counting text in a range is easy. For example, if you would like count how many times “text” is in a specified range (named data), you can write something like this: =COUNTIF(data,”*text*”) This formula will give you the count of all the cells where Excel found “text” at any place. But let’s say you want […]
Continue readingIf you do not wish to enter complex formula in Excel and you already have calculated average, count, and variance for your samples(Tip:use a PivotTable), then you can use these user-defined functions to calculate the t-test stat value and degrees of freedom required to do hypothesis testing. Both the functions provide an optional argument for […]
Continue readingIf you want to convert the text to uppercase, use the following code; however, I recommend downloading ASAP Utilities, it has many functionalities, including text conversion. It doesn’t offer source code though. Here’s my code for uppercase conversion:(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code) ‘Will […]
Continue readingVery simple, uses the VBA function StrReverse to reverse the input string. ‘A function to reverse a string provided as input‘For example, the string “abcd” will become “dcba”‘Uses the VBA function StrReversePublic Function ReverseString(sInputString As String) As StringOn Error GoTo ReverseString_Error ReverseString = StrReverse(sInputString) Exit Function ReverseString_Error: ReverseString = “#ERROR#”End Function
Continue readingOh, man, I can’t tell how useful that concatenate function is. One repetitive use I found is to create OR/AND conditions for Access queries. I copy and paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concat2 function to create a string to […]
Continue reading