Posts tagged string

Count Text in a Range

Apr24
2009 Leave a Comment Written by admin

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 to count wildcard characters in a range, then you can write something like this:
=SUM(IF(NOT(ISERR(FIND("*",data))),1,0))

This formula should be entered as an array formula with Ctrl + Shift + Enter. Usually, wildcard can be “escaped” using a tilde (~), but the FIND function doesn’t understand wildcard characters like the SEARCH function does.

Posted in String Operations - Tagged , ,

String Operations

Mar09
2005 Leave a Comment Written by admin

I found this on microsoft’s website. I didn’t know that I can use MID function on the LHS and converting string to ASCII will speed up the process. Cool!!

“The following points provide suggestions for ways to enhance the performance of string operations:
Minimize concatenation operations when you can. You can use the Mid function on the left side of the equal sign to replace characters within the string, rather than concatenating them together. The drawback to using the Mid function is that the replacement string must be the same length as the substring you are replacing.

Dim strText As String

strText = ‘this is a test’
Mid(strText, 11, 4) = ‘tent’
Debug.Print strText

Microsoft Visual Basic for Applications (VBA) provides a number of intrinsic string constants that you can use to replace function calls. For example, you can use the vbCrLf constant to represent a carriage return/linefeed combination within a string, rather than using Chr(13) & Chr(10).
String-comparison operations are slow. Sometimes, you can avoid them by converting a character in the string to an ANSI value. For example, the following code checks whether the first character in a string is a space:

If Asc(strText) = 32 Then

The previous code is faster than the following:

If Left(strText, 1) = ‘ ‘ Then”
Posted in String Operations - Tagged , ,

Excel Function to check if text is palindrome or not

Feb10
2005 Leave a Comment Written by admin

Definition of palindrome as given in Answers.com

pal?in?drome (p?l’?n-dr?m’)

n.

A word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!

Function IsPalindrome(sInput As String) As Boolean
If sInput = StrReverse(sInput) Then
IsPalindrome = True
Else
IsPalindrome = False
End If
End Function

Posted in String Operations - Tagged ,